Azure SQL Database Auditing with Active Directory Administrators

Question

Pulumi · Accepted Answer

To set up Azure SQL Database Auditing along with Azure Active Directory (Azure AD) administrators, you need to create and configure two main resources: an Azure SQL Server instance and an Azure SQL Database. Then, for the auditing, you need to enable and configure Auditing Policies for that Azure SQL Database. For the Active Directory part, you'll set up an Azure AD Administrator for the SQL Server.

Follow these steps in your Pulumi program to accomplish this:

1. **Create an Azure SQL Server**: You'll need an Azure SQL Server before you can create databases or configure auditing policies.
2. **Set an Azure AD Administrator**: Assign an Active Directory admin to the SQL Server for managing database access and operations.
3. **Create an Azure SQL Database**: Deploy a SQL database within the server you've just created.
4. **Enable and Configure Database Blob Auditing Policy**: Define and set up an auditing policy for your Azure SQL Database to log database events.

Let's translate these steps into a Pulumi Python program:

```python
import pulumi
import pulumi_azure_native.sql as azure_sql
import pulumi_azure_native.resources as resources

# Create a resource group for your SQL Server and related resources
resource_group = resources.ResourceGroup('sql-resource-group')

# Create an Azure SQL Server
sql_server = azure_sql.Server('sql-server',
    resource_group_name=resource_group.name,
    location=resource_group.location,
    administrator_login='sqladmin',
    administrator_login_password='strong-password!123',  # Replace with a strong password
    version='12.0')

# Set an Azure Active Directory admin for the SQL Server
aad_admin = azure_sql.ServerAzureADAdministrator('aad-admin',
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    login='your-aad-admin-login',  # Replace with your admin login
    sid='00000000-0000-0000-0000-000000000000',  # Replace with the security ID of the Azure AD admin
    tenant_id='00000000-0000-0000-0000-000000000000')  # Your Azure AD tenant ID

# Create an Azure SQL Database
sql_database = azure_sql.Database('sql-database',
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    sku_name='S0')

# Enable and configure a Blob Auditing Policy for the Azure SQL Database
blob_auditing_policy = azure_sql.DatabaseBlobAuditingPolicy('blob-auditing-policy',
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    database_name=sql_database.name,
    state='Enabled',
    storage_endpoint='https://myaccount.blob.core.windows.net',  # Replace with the endpoint of your storage account
    storage_account_access_key='storage-account-access-key',  # Replace with your storage account access key
    retention_days=90,  # Number of days to retain the audit logs
    audit_actions_and_groups=['SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP', 'FAILED_DATABASE_AUTHENTICATION_GROUP'])

# Export the SQL Server and Database names
pulumi.export('sql_server_name', sql_server.name)
pulumi.export('sql_database_name', sql_database.name)
```

This program does the following:

- Sets up a new resource group to organize all the components of your SQL infrastructure.
- Deploys an Azure SQL Server and an Azure SQL Database in the resource group.
- Configures an Azure AD administrator for your SQL Server.
- Enables Azure SQL Database Auditing by setting up a Blob Auditing Policy, which specifies that audit logs be written to a specified blob storage endpoint, retained for a specified period (in this case, 90 days), and includes both successful and failed database authentication actions.

Before running this program with Pulumi, replace placeholders with actual values for the administrator login, SID, tenant ID, storage endpoint URL, and storage account access key. Keep in mind that handling secrets like the administrator login password and storage account access key should be done securely, for example, using Pulumi's secret handling capabilities or Azure Key Vault.

To execute the Pulumi program, save the contents to a file with a `.py` extension and run it using the Pulumi CLI, which will provision the resources defined in the code in your Azure subscription. Make sure your environment has been authenticated with Azure through the Azure CLI and that the Pulumi CLI is installed and configured.