Manage Access Audits¶

The Microsoft SQL Server connector provides access auditing capabilities to help organizations track user activity, ensure compliance, and enhance security monitoring. This feature allows you to collect and process audit logs from Microsoft SQL Server audit files. Audit logs can be stored in Azure Blob Storage (for Azure SQL) or in local file systems (for on-premises SQL Server installations).

Audit Configuration¶

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

Storage URL: Specifies the storage location where SQL Server audit logs are stored. For Azure SQL, this should be an Azure Blob Storage URL. For on-premises SQL Server, this should be the local file system path where audit logs are written. For Azure Blob Storage, 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

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

SQL Server On-Premises Setup¶

Perform the following steps to enable auditing locally.

Step 1: Docker Compose

Create a docker-compose.yml file with the following configuration:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

version: "3.8"

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sqlserver2019
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=welcome1
      - MSSQL_PID=Developer
    ports:
      - "1433:1433"
    volumes:
      - sqlserver-data:/var/opt/mssql
      - ./audit-logs:/var/opt/mssql/audit
    restart: unless-stopped

volumes:
  sqlserver-data:

Step 2: Create Audit Logs Directory

Create the audit logs directory on the host and set appropriate permissions:

1
2

mkdir -p audit-logs
chmod 777 audit-logs

Step 3: Start SQL Server Container

Start the SQL Server container using Docker Compose:

1

docker compose up -d

Step 4: Configure Server Audit

Connect to SQL Server using a database management tool (such as DBeaver) and run the following SQL queries to create and enable the server audit:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE SERVER AUDIT SQL_Server_Audit
TO FILE (
    FILEPATH = '/var/opt/mssql/audit',
    RESERVE_DISK_SPACE = ON
);

ALTER SERVER AUDIT SQL_Server_Audit WITH (STATE = ON);

CREATE SERVER AUDIT SPECIFICATION Audit_Spec
FOR SERVER AUDIT SQL_Server_Audit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP);

ALTER SERVER AUDIT SPECIFICATION Audit_Spec WITH (STATE = ON);

Step 5: Verify Server Audit Configuration

Verify that the server audit is configured and enabled:

1

SELECT name, state_desc FROM sys.server_audits;

Query audit logs to verify they are being written:

1

SELECT * FROM sys.fn_get_audit_file('/var/opt/mssql/audit/*.sqlaudit', DEFAULT, DEFAULT);

To check recent audit events:

1
2
3
4
5
6

SELECT TOP 5 
    event_time,
    action_id,
    server_principal_name
FROM sys.fn_get_audit_file('/var/opt/mssql/audit/*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Step 6: Create Database Audit Specification

To capture table-level operations, create a database audit specification for your target database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Switch to the target database
USE connector_automation_db;

-- Create Database Audit Specification
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec_Privacera]
FOR SERVER AUDIT [SQL_Server_Audit]
ADD (SELECT ON SCHEMA::[customer_schema] BY [public]),
ADD (INSERT ON SCHEMA::[customer_schema] BY [public]),
ADD (UPDATE ON SCHEMA::[customer_schema] BY [public]),
ADD (DELETE ON SCHEMA::[customer_schema] BY [public])
WITH (STATE = ON);

Step 7: Verify Database Audit Specification

Verify that the database audit specification is enabled:

1
2
3
4
5
6
7

SELECT 
    name,
    is_state_enabled,
    create_date,
    modify_date
FROM sys.database_audit_specifications
WHERE name = 'DatabaseAuditSpec_Privacera';

Step 8: Test Audit Logging

Perform test operations to verify audit logging is working:

1
2

-- Test SELECT operation
SELECT * FROM customer_schema.customer_data WHERE id = 1;

Query audit logs filtered for your table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT 
    event_time,
    action_id,
    server_principal_name,
    database_principal_name,
    database_name,
    schema_name,
    object_name,
    statement,
    succeeded
FROM sys.fn_get_audit_file('/var/opt/mssql/audit/*.sqlaudit', DEFAULT, DEFAULT)
WHERE database_name = 'connector_automation_db'
  AND schema_name = 'customer_schema'
  AND object_name = 'customer_data'
  AND event_time >= DATEADD(MINUTE, -10, GETDATE())  -- Last 10 minutes
ORDER BY event_time DESC;

Check recent audit events:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT TOP 10
    event_time,
    action_id,
    server_principal_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('/var/opt/mssql/audit/*.sqlaudit', DEFAULT, DEFAULT)
WHERE database_name = 'connector_automation_db'
ORDER BY event_time DESC;

Step 9: Configure Service User Permissions

The service user (e.g., privacera_connector) requires specific permissions to query audit logs and configuration.

Grant Server-Level Permissions¶

The sys.fn_get_audit_file() function requires VIEW SERVER STATE permission:

1

GRANT VIEW SERVER STATE TO [privacera_connector];

For querying audit configuration:

1

GRANT VIEW ANY DEFINITION TO [privacera_connector];

Grant Database Permissions¶

For read-only audit access:

1

ALTER ROLE db_datareader ADD MEMBER [privacera_connector];

For full database operations (if connector manages resources):

1

ALTER ROLE db_owner ADD MEMBER [privacera_connector];

File System Permissions¶

Setup¶