As part of my ongoing series about working with Bicep and SQL Servers and SQL Databases, I am going to cover how you can turn on auditing for a SQL Server instance using Bicep. The auditing I am referring to is what you will find if you go to an Azure SQL Server (Platform as a Service, fully cloud version), then navigate to “Settings” and then “Auditing”. By default, the auditing option will track a few different things that happen on your server, and then you’re able to customize it further if you would like. This post will specifically cover how to enable auditing for a SQL Server using a Storage Account for the destination of the audit logs and the “Storage Access Keys” option for the Storage Authentication Type.
Other Posts in this Series
- An Introduction to Bicep
- Create an IaaS Windows VM with a Bicep Template
- Using Custom Script Extensions with Bicep Templates
- Create an Azure SQL Server and Database with a Bicep Template
- Check the box to allow Azure Resources to Access SQL Server in Bicep Template
- Add RBAC Role Definitions to Resources with Bicep Template
What’s in this post
- What is SQL Server Auditing?
- Full Bicep Template
- Create a Storage Account Resource
- Create the Auditing Resource
- Summary
- Resources
What is SQL Server Auditing?
According to the Azure portal, auditing “tracks database events and writes them to an audit log in your Azure Storage account, Log Analytics workspace or Event Hub.” This allows you to keep track of what is happening on your server and its databases in case you ever need to look back and see what changed or if you need to troubleshoot an error in more detail. Auditing gives you a similar level of server and database traceability as the standard SQL Server Logs on a normal instance, just in a slightly different form since it’s 100% in the cloud.
In my organization, we have a requirement for auditing to be turned on for all our SQL Servers as a safety measure, which is why I wanted to learn how to enable it in the portal and then later how to enable it through a Bicep template now that we’re automating our resource creation more.
Note: This Bicep template applies auditing at the SQL Server level, not the SQL Database level. Both are options that you can choose from. Understand that I am not showing how to enable auditing for a specific database, as the template for that is likely slightly different.
In the portal, this is what the Auditing settings page looks like.
Continue reading and I’ll show you how to convert that portal page into a Bicep template that you can deploy without much effort at all.
Full Bicep Template
This post is discussing a subset of a Bicep template that I’ve also covered in part in other posts. If you would like to see the Bicep template in its entirety, you can find it on my GitHub.
Create a Storage Account Resource
Since I have opted to send my SQL Server audit logs to a Storage Account, we first need to create a storage account to send the logs to. If you already have an existing storage account that you would like to send the files to instead, you can skip this step and do an “existing” type reference in the script.
The definition below creates a very standard storage account. The only thing to mention is that it must be a “V2” type of storage in order to work with auditing.
resource storageAcct 'Microsoft.Storage/storageAccounts@2023-05-01'= {
name: stgAcctName
location: location
sku: {
name: 'Standard_RAGRS'
}
kind: 'StorageV2'
properties: {
dnsEndpointType:'Standard'
defaultToOAuthAuthentication:false
publicNetworkAccess:'Enabled'
allowCrossTenantReplication:false
minimumTlsVersion:'TLS1_2'
allowBlobPublicAccess:false
allowSharedKeyAccess:true
largeFileSharesState:'Enabled'
networkAcls: {
defaultAction: 'Allow'
bypass:'AzureServices'
}
supportsHttpsTrafficOnly:true
encryption: {
requireInfrastructureEncryption:false
services: {
file: {
keyType:'Account'
enabled:true
}
blob: {
keyType:'Account'
enabled:true
}
}
keySource:'Microsoft.Storage'
}
accessTier:'Hot'
}
}Create the Auditing Resource
Once you have your storage account created and ready to use, you can then enable auditing on your SQL Server by creating a auditingSettings type resource like what I have below.
resource sqlServerAuditing 'Microsoft.Sql/servers/auditingSettings@2024-05-01-preview'= {
name: 'default'
parent: sqlServer
properties: {
state: 'Enabled'
auditActionsAndGroups: [
'BATCH_COMPLETED_GROUP'
'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
'FAILED_DATABASE_AUTHENTICATION_GROUP'
]
retentionDays:90
storageAccountSubscriptionId:'2b896190-565d-4f66-9397-92a34afbec85'
storageEndpoint:'https://${stgAcctName}.blob.core.windows.net'
storageAccountAccessKey:storageAcct.listKeys().keys[0].value
}
}Set the Parent of the Auditing Resource
The first notable thing is that I have specified the parent of the resource as the SQL Server that I created earlier in the template (not shown in this post, but is on GitHub), which is important to tell the auditing resource what you want it to be applied/connected to. If you created the SQL Server in a separate template, you would have to reference the server differently to get the template to understand what you’re trying to apply the auditing to.
Specify what you want to audit
The next notable thing about this resource definition is the list of auditActionsAndGroups, which I chose as the default values. If you go to the Auditing settings in the portal and hover above the (i) icon next to where you enable the auditing feature, you will get information about what auditing rules the Azure SQL Server will get by default unless you specify otherwise.
The default policies are:
- BATCH_COMPLETED_GROUP
- SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
- FAILED_DATABASE_AUTHENTICATION_GROUP
In my experience so far, those options have been sufficient for my troubleshoot needs, but you can add others to the list if you would like. To see all the options, review this Microsoft document.
The best part of creating the auditing resource through Bicep is that you are able to specify those additional “action groups”. Otherwise, you would have to use PowerShell to update the list of things you want to be audited, since you aren’t able to make that change in the portal at this time.
Retention length and storage account connection
One of the final things you will need to set in your auditing resource is the number of days you would like to retain the logs, with a maximum value of 100. I chose 90 days, according to my organization’s standards.
Then you need to specify the storage account and the storage account keys to grant access with. I have set the storageEndpoint value using a variable which contains the name of my storage account, which was created earlier in the full template. I then need to provide the “primary” key of the storage account which gives the auditing resource access to save logs to the storage account. To do this, since I had created my storage account in the same template above, I extract the keys using built-in Bicep functionality. storageAcct.listKeys().keys[0].value
The breakdown of that key extraction is <stg acct symbolic name>.<function that will list keys>.<first key in list (primary)>.<value in keys array at location 0>.
Summary
With just two resource definitions in a Bicep template, you are able to specify custom auditing for your Azure SQL Server instance, and do extra detailed auditing beyond the defaults which isn’t available through the portal. Let me know in the comments below if you found this helpful. Thanks for reading!
Resources
- https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup?view=azuresql
- https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver17
