Skip to main content

Privacera Documentation

Table of Contents

Configure Microsoft SQL server for database synapse audits

To configure MS SQL server for database or 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.

    searchsql.jpg
  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.

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

    powershell.jpg
  8. Click Show advanced settings.

    setting.jpg
  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.

    storage.jpg

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

    pcommand.jpg
  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.

    containers.jpg

    Note

    Make sure that your MSSQL Server name directory is visible inside your audit log container. It might take some time to appear inside the container.

    servername.jpg

    Now, you need to form an Audit Storage URL for your MSSQL Server.

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

    url.jpg

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