Azure SQL MI：如何通过Log Analytics追踪实例登录用户

Great question! Let's break this down for you since Azure SQL Managed Instance (MI) handles auditing and login logging a bit differently than single Azure SQL Databases.

1. Enabling Login Log Collection to Log Analytics #

Unlike single Azure SQL Databases, SQL MI uses server-level auditing policies to capture login events, which can be routed directly to your Log Analytics workspace. Here's how to set it up:

• Navigate to your Azure SQL Managed Instance in the Azure Portal.
• Go to the Auditing blade under the Security section.
• Toggle Enable auditing to On if it's disabled.
• Under Audit destination, select Log Analytics workspace and choose your target workspace from the dropdown.
• Expand Audit actions and groups and ensure these two groups are checked:
  • SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP (captures all successful login attempts)
  • FAILED_DATABASE_AUTHENTICATION_GROUP (captures failed login attempts, useful for security monitoring)
• Save your configuration. It may take 5-10 minutes for events to start appearing in Log Analytics.

Querying Login Logs in Log Analytics #

Once the logs are flowing, you can use this Kusto query to retrieve login events:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "SQLSecurityAuditEvents"
| where action_name_s in ("SUCCESSFUL_DATABASE_AUTHENTICATION", "FAILED_DATABASE_AUTHENTICATION")
| project 
    TimeGenerated, 
    Action = action_name_s, 
    User = server_principal_name_s, 
    ClientIP = client_ip_s, 
    Database = database_name_s

2. Retrieving Login Information from SQL MI System Tables/Views #

You can access login-related data directly from SQL MI's system views and functions, though the scope depends on what you need:

Active Sessions Only #

To see currently connected users (no historical data), use sys.dm_exec_sessions:

SELECT 
    login_name AS [User],
    host_name AS [Client Host],
    program_name AS [Application],
    login_time AS [Login Time]
FROM sys.dm_exec_sessions
WHERE is_user_process = 1; -- Filter out system processes

Historical Audit Data (From Storage) #

If you've configured auditing to also send logs to an Azure Storage account, you can read those logs directly with sys.fn_get_audit_file:

SELECT 
    event_time AS [Event Time],
    action_id AS [Action ID],
    succeeded AS [Login Successful],
    session_server_principal_name AS [User],
    client_ip AS [Client IP]
FROM sys.fn_get_audit_file(
    'https://<your-storage-account>.blob.core.windows.net/sqldbauditlogs/<your-mi-name>/',
    DEFAULT, DEFAULT
);

Replace the placeholder URL with your actual storage account and MI name path.

Real-Time Tracking with Extended Events #

For custom, real-time login tracking, you can create an Extended Events session. This is useful if you need more granular control than auditing provides:

CREATE EVENT SESSION [TrackLoginEvents] ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id)),
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id))
ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvents\TrackLogins.xel') -- Adjust path as needed
WITH (STARTUP_STATE=ON); -- Starts the session automatically when MI restarts
GO

ALTER EVENT SESSION [TrackLoginEvents] ON SERVER STATE=START;

Note that you'll need to manage the storage for the event files, and this won't auto-forward to Log Analytics unless you set up additional integration.

Final Notes #

The most scalable and maintainable approach is using the built-in auditing to send login events to Log Analytics—it handles persistence, retention, and integrates seamlessly with other Azure monitoring tools. For ad-hoc checks or real-time needs, the system views or Extended Events are great alternatives.

内容的提问来源于stack exchange，提问作者mac