SQL MI audit specification not showing in Log Analytics Workspace

Question

Hi,

I'm trying to send SQL MI audit logs to log analytics. I've followed the steps here https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql

and here

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-specification-transact-sql?view=sql-server-ver16

and here

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16#audit-level-audit-action-groups

SQL MI is sending diagnostic logs including audit logs to log analytics.

What am I missing that I don't see the action groups?

Audit server state is set to "On"

Answer 1

Hello ShaktiSingh-MSFT ,

I am creating a new SQL MI instance and I will send a response.

Answer 2

@SSingh-MSFT I can the commands but one failed and I am unable to restart SQL MI. I am able to see databases created and dropped in the logs but the data going into the logs is inconsistent.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/common-criteria-compliance-enabled-server-configuration-option?view=sql-server-ver16

Error message - Msg 5869, Level 16, State 1, Procedure sp_configure, Line 166
Changes to server configuration option common criteria compliance enabled are not supported in SQL Database Managed Instances.

Here is the SQL I ran
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Answer 3

@SSingh-MSFT , finally got the Audit logs flowing. Even though I had the server and database specification setup I needed to stop and restart the Audit Server. Another issue was I needed to add database specifications.

Thank you for your support.