Sending SQLSecurityAudit logs to multiple Log Analytics workspaces

Question

We're trying to get logging (SQL Security Audit specifically) to both an "Operations" LAW as well as Sentinel workspace.

Scenario;

• Create SQL Server, set SQL Auditing to send to Sentinel workspace

Create sample DB

• Create diagnostics setting - allLogs to "general" ops logging LAW (no metrics)
• SQL Server logs show SQLSecurityAuditEvents in Sentinel workspace, but not the Operations LAW (even with the "SQL Security Audit Event" ticket in diagnostics, and sending to the Operations LAW)
• Test - Login with invalid creds to Query Editor (AzureDiagnostics table); SQL SRV logs show failed auth
• KQL query and database "logs" level show nothing.
• Try to set Auditing at DB level to go to Operations LAW - not available as diagnostics are already set (and should be sending audit data?)

Am I missing something or is this just not possible? Should I set DB level auditing to the Operations LAW before setting any Diagnostic settings?

If I want additional logging going to Sentinel <audit> for example, can I just set that in the diagnostic settings even though the SQL Auditing is already sending to that workspace?

Answer 1

Hello @Knock, William

Thanks for outlining your testing steps, they're very helpful.

From what you've described, this is expected behavior rather than a configuration issue.

SQL Auditing and Diagnostic Settings use different ingestion pipelines. When SQL Auditing is configured to send SQLSecurityAuditEvents directly to a Log Analytics workspace (for example, your Sentinel workspace), those audit events aren't duplicated to another Log Analytics workspace through Diagnostic Settings.

In other words:

• Diagnostic Settings can send platform logs and metrics to a single Log Analytics workspace per diagnostic setting.
• SQLSecurityAuditEvents generated by SQL Auditing are delivered to the auditing target you've configured and aren't fan-out to multiple Log Analytics workspaces automatically.

If you need the same audit events in both an Operations workspace and a Sentinel workspace, you may want to consider one of these approaches:

• Make the Operations workspace the audit destination and use Azure Monitor Data Collection Rules (where applicable) or Microsoft Sentinel content to centralize analytics.
• Send audit logs to an intermediate destination such as an Azure Storage account or Event Hub, then ingest them into multiple Log Analytics workspaces using Azure Monitor or custom ingestion pipelines.
• If Microsoft Sentinel is connected to a different Log Analytics workspace, evaluate whether consolidating monitoring into a single workspace better aligns with your operational and security requirements.

If a Microsoft moderator is monitoring this thread, it would be useful to confirm whether Azure SQL Auditing currently supports sending SQLSecurityAuditEvents to multiple Log Analytics workspaces simultaneously, or if this remains a product limitation by design.

Please "Accept the Answer" if this information helped you. This will help us and others in the community as well.