Enable Azure SQL Server Auditing with Python SDK

Question

I'm trying to enable Azure SQL auditing to a Log Analytics workspace as per the screen shot here using the Python SDK, but try as I might, I can't find the right combination of commands (nor can I see any hints in the official documentation) to make this stick programmatically.

I've tried both sql_client.extended_server_blob_auditing_policies and sql_client.server_blob_auditing_policies methods and neither show me information on the Log Analytics workspace when I enable this via the GUI.

Anyone been able to do this? Using azure-mgmt-sql 3.0.1.

Answer 1

To Enable Azure SQL server auditing by using python SDK azure-mgmt-sql 3.0.1 you can follow below procedure: To authenticate with azure you can use service principal authentication using below code:

TENANT_ID = '<tenanatId>'
CLIENT_ID = '<clientId>'
CLIENT_SECRET = '<clientSecret>'

credentials = ClientSecretCredential(
    client_id=CLIENT_ID,
    client_secret=CLIENT_SECRET,
    tenant_id=TENANT_ID
)

Connect azure sql server using below code:

SUBSCRIPTION_ID = '<SubId>'
RESOURCE_GROUP = '<RGName>'
SERVER_NAME = '<serverName>'

sql_client = SqlManagementClient(
    credential=credentials,
    subscription_id=SUBSCRIPTION_ID
)

resource_client = ResourceManagementClient(
    credential=credentials,
    subscription_id=SUBSCRIPTION_ID
)

server = sql_client.servers.get(
    resource_group_name=RESOURCE_GROUP,
    server_name=SERVER_NAME
)

Enable auditing to a Log Analytics workspace using the create_or_update method of the ServerAuditPolicy class:

code:

LOG_ANALYTICS_WORKSPACE_ID = '<workspaceId>'



destination = LogAnalyticsDestination(workspace_id=LOG_ANALYTICS_WORKSPACE_ID)

policy = ServerBlobAuditingPolicy(
    retention_days=90,
    state='Enabled',
    use_server_default=False,
    storage_account_subscription_id=None,
    destination_type='LogAnalytics',
    destination_details=destination
)

result = sql_client.server_audit_policies.create_or_update(
    resource_group_name=RESOURCE_GROUP,
    server_name=SERVER_NAME,
    parameters=policy
)

Complete code:

from azure.identity import ClientSecretCredential
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.sql.models import ServerBlobAuditingPolicy
from azure.mgmt.monitor.models import LogAnalyticsDestination
TENANT_ID = '<tenanatId>'
CLIENT_ID = '<clientId>'
CLIENT_SECRET = '<clientSecret>'

credentials = ClientSecretCredential(
    client_id=CLIENT_ID,
    client_secret=CLIENT_SECRET,
    tenant_id=TENANT_ID
)

SUBSCRIPTION_ID = '<SubId>'
RESOURCE_GROUP = '<RGName>'
SERVER_NAME = '<serverName>'

sql_client = SqlManagementClient(
    credential=credentials,
    subscription_id=SUBSCRIPTION_ID
)

resource_client = ResourceManagementClient(
    credential=credentials,
    subscription_id=SUBSCRIPTION_ID
)

server = sql_client.servers.get(
    resource_group_name=RESOURCE_GROUP,
    server_name=SERVER_NAME
)


LOG_ANALYTICS_WORKSPACE_ID = '<workspaceId>'



destination = LogAnalyticsDestination(workspace_id=LOG_ANALYTICS_WORKSPACE_ID)

policy = ServerBlobAuditingPolicy(
    retention_days=90,
    state='Enabled',
    use_server_default=False,
    storage_account_subscription_id=None,
    destination_type='LogAnalytics',
    destination_details=destination
)

result = sql_client.server_audit_policies.create_or_update(
    resource_group_name=RESOURCE_GROUP,
    server_name=SERVER_NAME,
    parameters=policy
)

Auditing will enable on sql server level