Skip to content

Configuring MSSQL Server for Database/Synapse Audits#

To configure MSSQL server for database/Synapse audits, use the following steps:

  1. Login to Azure portal.

  2. Search for SQL Servers in which you want to configure MSSQL for Azure AD users.

  3. Select Azure AD user, and then click Auditing.

  4. Click the toggle button to ON to Enable Azure SQL Auditing.

  5. Select the Storage checkbox to set audit log destination as storage account, and then select your existing storage account from the Storage Details.

  6. Click the Save button.

  7. To open the Azure cloud shell, click the shell icon, , on the top menu bar, and then click PowerShell

  8. Click Show advanced settings.

  9. In the Cloud Shell region text box, enter your region.

  10. In the Storage account, select Use existing to use your existing storage account.

  11. In the File share, select Create new, and then enter name.

  12. Click the Create storage button.

    Cloud powershell window will appear, you can run your commands in the powershell.

  13. 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
    
  14. Run the following command, if you have Azure Synapse Database:

    Set-AzSqlServerAudit -ResourceGroupName "${RESOURCE_GROUP}" -ServerName "${MSSQL_SERVER_NAME}" -AuditActionGroup
    BATCH_COMPLETED_GROUP
    

    The above queries will take around one or two minutes to be completed.

  15. 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.

  16. Go to Properties, and then copy the container URL.

    Now, your Audit storage url will be ${CONTAINER_URL}/${MSSQL_SERVER_NAME}


Last update: August 24, 2021