Skip to content

Manage Access Audits

The Microsoft SQL Server (MSSQL) connector supports access auditing to help organizations track user activity, meet compliance requirements, and enhance security monitoring. Access audit logs are collected from Microsoft SQL Server audit files stored in Azure Blob Storage and processed by the connector.

Scope

Access audit collection is supported only for Azure SQL Server and Azure Managed SQL Server, where audit logs are stored in Azure Blob Storage.

Audit Configuration

Audit Enable: Enables access audit collection for the Microsoft SQL Server connector. Set this property to true to enable audit logs.

Storage URL: Specifies the Azure Blob Storage URL where SQL Server audit logs are stored. The connector reads audit logs from this location. The URL format should be: https://<storage_account>.blob.core.windows.net/<container>/<mssql_server_name>.

  • Example: https://sqlaudit.blob.core.windows.net/sqldbauditlogs/my-sql-server

Important

The <mssql_server_name> must be the actual SQL Server name. This value appears as a directory inside the audit log container in Azure Blob Storage.
See Verify Audit Configuration for details.

Audit Load Key: Specifies the audit loading mechanism used by the connector. This property determines how audit logs are processed:

  • load: Use this value for standard Microsoft SQL Server databases.
  • load_synapse: Use this value for Azure Synapse Analytics databases.

Audit Initial Pull Minutes: Specifies the number of minutes in the past from which to start pulling audit logs when the connector first runs. This helps ensure that historical audit data is captured. Default: 30 minutes.

Audit Excluded Users: Comma-separated list of users to exclude from audit processing. Audit logs for these users will not be processed by the connector. This is useful for excluding service accounts or administrative users from audit collection.

  • Example: admin_user,service_account@domain.com

Audit Sync Interval: Specifies the interval (in seconds) at which the connector synchronizes and processes audit logs. Default: 30 seconds.

Prerequisites

Before configuring access audits, you need to set up SQL Server auditing in Azure. Follow these steps:

Enable Azure SQL Auditing

  1. Log in to the Azure portal.
  2. Search for the SQL Servers in which you want to configure MSSQL auditing.
  3. Select the SQL Server (or SQL Database), and then click Auditing.
  4. Click the Enable Azure SQL Auditing toggle button to turn on Azure SQL Auditing.
  5. Select the Storage checkbox to set the audit log destination to a storage account, and then select your existing storage account from the Storage Details.
  6. Click Save.

Configure Azure Cloud Shell

  1. Click the Cloud shell icon in the top menu bar, and then select PowerShell.
  2. Click Show advanced settings.
  3. In the Cloud Shell region, enter your region.
  4. In the Storage account, select Use existing.
  5. In the File share, select Create new, and then enter a name.
  6. Click Create storage.
  7. The Cloud PowerShell window will appear. You can now run your commands in PowerShell.
  8. If you have an Azure SQL Database, run:
    PowerShell
    Set-AzSqlServerAudit -ResourceGroupName "${RESOURCE_GROUP}" -ServerName "${MSSQL_SERVER_NAME}" -AuditActionGroup SCHEMA_OBJECT_ACCESS_GROUP,DATABASE_OBJECT_CHANGE_GROUP,SCHEMA_OBJECT_CHANGE_GROUP
  9. If you have an Azure Synapse Database, run:
    PowerShell
    Set-AzSqlServerAudit -ResourceGroupName "${RESOURCE_GROUP}" -ServerName "${MSSQL_SERVER_NAME}" -AuditActionGroup BATCH_COMPLETED_GROUP

Verify Audit Configuration and Get Storage URL

  1. Go to the storage account configured for MSSQL auditing and click Containers.

    Note

    Make sure that your MSSQL Server name directory is visible inside your audit log container. It may take a few minutes to appear.

  2. Navigate into your audit log container (e.g., sqldbauditlogs). You should see a directory with your MSSQL server name (e.g., my-sql-server).

  3. Go to Properties, and copy the container URL (e.g., https://sqlaudit.blob.core.windows.net/sqldbauditlogs).

  4. Your Audit storage URL will be: ${CONTAINER_URL}/${MSSQL_SERVER_NAME}

    For example: https://sqlaudit.blob.core.windows.net/sqldbauditlogs/my-sql-server

    Do not use only the container URL

    The storage URL must include the SQL Server name directory, otherwise the connector will not find audit logs and will show: auditLogsCount: 0

Setup

Warning

  • Ensure that SQL Server audit is properly configured and writing logs to the specified Azure Blob Storage location.
  • The Storage URL must be accessible by the JDBC user credentials configured in the connector.
  • Replace the example values with your actual values.

  1. SSH to the instance where Privacera Manager is installed.

  2. Run the following command to open the .yml file to be edited.

    If you have multiple connectors, then replace instance1 with the appropriate connector instance name.

    Bash
    1
    vi ~/privacera/privacera-manager/config/custom-vars/connectors/mssql/instance1/vars.connector.mssql.yml

  3. Update or add the following properties:

    YAML
    1
2
3
4
5
6
    CONNECTOR_MSSQL_AUDIT_ENABLE: "true"
CONNECTOR_MSSQL_AUDIT_STORAGE_URL: "https://sqlaudit.blob.core.windows.net/sqldbauditlogs/my-sql-server"
CONNECTOR_MSSQL_AUDIT_LOAD_KEY: "load"
CONNECTOR_MSSQL_AUDIT_INITIAL_PULL_MINUTES: "30"
CONNECTOR_MSSQL_AUDIT_EXCLUDED_USERS: "admin_user,service_account@domain.com"
CONNECTOR_MSSQL_AUDIT_SYNC_INTERVAL: "30"

  4. Once the properties are configured, run the following commands to update your Privacera Manager platform instance:

    Step 1 - Setup which generates the helm charts. This step usually takes few minutes.

    Bash
    1
2
    cd ~/privacera/privacera-manager
./privacera-manager.sh setup
    Step 2 - Apply the Privacera Manager helm charts.
    Bash
    1
2
    cd ~/privacera/privacera-manager
./pm_with_helm.sh upgrade
    Step 3 - (Optional) Post-installation step which generates Plugin tar ball, updates Route 53 DNS and so on. This step is not required if you are updating only connector properties.

    Bash
    1
2
    cd ~/privacera/privacera-manager
./privacera-manager.sh post-install

  1. In PrivaceraCloud portal, navigate to Settings → Applications.

  2. On the Connected Applications screen, select MSSQL.

  3. Click the pen icon or the Account Name to modify the settings.

  4. On the Edit Application screen, go to Access Management.

  5. Under the BASIC tab, enable Enable access audits using the toggle button.

  6. Enter a value in the Audits storage URL field. Example: https://sqlaudit.blob.core.windows.net/sqldbauditlogs/my-sql-server

  7. Under the ADVANCED tab, enter the following value:

    • Users to exclude when fetching access audits: admin_user,service_account@domain.com

  8. Add the following custom properties to control audit loading and synchronization behavior under Add New Custom Properties:

    Bash
    1
2
3
    ranger.policysync.connector.0.audit.initial.pull.min=30
ranger.policysync.connector.0.audit.interval.sec=30
ranger.policysync.connector.0.load.audits=load

  9. Click SAVE to apply the changes.