Configuring MSSQL Server for Database/Synapse Audits#
To configure MSSQL server for database/Synapse audits, use the following steps:
-
Login to Azure portal.
-
Search for SQL Servers in which you want to configure MSSQL for Azure AD users.
-
Select Azure AD user, and then click Auditing.
-
Click the toggle button to ON to Enable Azure SQL Auditing.
-
Select the Storage checkbox to set audit log destination as storage account, and then select your existing storage account from the Storage Details.
-
Click the Save button.
-
To open the Azure cloud shell, click the shell icon,
, on the top menu bar, and then click PowerShell
-
Click Show advanced settings.
-
In the Cloud Shell region text box, enter your region.
-
In the Storage account, select Use existing to use your existing storage account.
-
In the File share, select Create new, and then enter name.
-
Click the Create storage button.
Cloud powershell window will appear, you can run your commands in the powershell.
-
Run the following command, if you have Azure MSSQL Database:
Set-AzSqlServerAudit -ResourceGroupName "${RESOURCE_GROUP}" -ServerName "${MSSQL_SERVER_NAME}" -AuditActionGroup SCHEMA_OBJECT_ACCESS_GROUP,DATABASE_OBJECT_CHANGE_GROUP,SCHEMA_OBJECT_CHANGE_GROUP -
Run the following command, if you have Azure Synapse Database:
Set-AzSqlServerAudit -ResourceGroupName "${RESOURCE_GROUP}" -ServerName "${MSSQL_SERVER_NAME}" -AuditActionGroup BATCH_COMPLETED_GROUPThe above queries will take around one or two minutes to be completed.
-
Go to your Storage account in which you have configured MSSQL Server for auditing purpose, and then click Containers.
Note
Make sure that your MSSQL Server name directory is vaialble inside your audit log container. It might take some time to appear inside the container.
Now, you need to form an Audit Storage URL for your MSSQL Server.
-
Go to Properties, and then copy the container URL.
Now, your Audit storage url will be
${CONTAINER_URL}/${MSSQL_SERVER_NAME}