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
  • Set the Parent of the Auditing Resource
  • Specify what you want to audit
  • Retention length and storage account connection
• 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

Related Posts

• How to Reset the Azure CLI Virtual Environment
• Deploying a DACPAC to Azure SQL Server
• How to View Pipelines Associated with a Synapse Data Source

This is the fifth in my series of posts about working with Bicep, the declarative scripting language created by Microsoft as the modern facade for Azure Resource Manager templates. Today’s post is focusing on a small bit of information that may be important for some Azure environments.

For those of us who work in Azure and have Azure SQL Servers and Databases, we often want those resources to be available to other resources, such as applications, within the Azure environment. To grant that access through the portal, there is a simple checkbox on the Networking page of the Azure SQL Server that will allow that. But if you’re trying to declaratively create your Azure infrastructure with Bicep like I am, how are you meant to accomplish checking that same box? That is what this post will answer.

What’s in this post

• Background
• How to check that box in a Bicep template
  • Understanding what the checkbox does
  • Bicep setup to check the box
  • Create the sqlServer resource in the template
  • Create the firewallRules resource in the template
• Summary
• Resources

Background

For those of you who may not be as familiar with what I was referencing above, what I am referring to is this checkbox that you find on the Networking page of an Azure SQL Server instance in the Azure Portal.

The purpose of this checkbox, as you can learn by hovering over the information icon at the end of the line of text for the checkbox, is to configure the firewall settings to allow inbound access from Azure resources. By default, this box is left unchecked.

However, if you are in a situation where you have a separate application that is going to be accessing the server and database (which is fairly common), you will also likely need to ensure this networking rule is checked when creating new Azure SQL Servers.

How to check that box in a Bicep template

Understanding what the checkbox does

The heart of what that checkbox is doing in the background when it is checked is creating a firewall rule which indicates to Azure services if they are allowed to connect to the server and database or not. You can see what it’s doing by querying the system table which contains the list of server firewall rules, which I wrote about in a previous post.

When the box is checked, you can see a firewall rule for the server in the table sys.firewall_rules (run on the master database), called “AllowAllWindowsAzureIps” with start and end IP addresses both set to 0.0.0.0.

Conversely, if that box is not checked on the portal, you will not see a firewall rule in the table with that name or IP address range. Once you understand that the checkbox is setting a specific firewall rule for you in the background, it becomes simple to figure out how you can make that box get checked through a Bicep template.

Bicep setup to check the box

Since the checkbox in the portal is creating a simple firewall rule with a specific name and IP address, we can recreate that easily within a Bicep template by creating a firewallRule resource within the template and tying that to your Azure SQL Server with the parent attribute.

Create the sqlServer resource in the template

First, create the SQL Server resource so that we can tie the firewall rule resource to it. If you would like to see how to define such a resource to then create a firewall rule for, see last week’s post.

Create the firewallRules resource in the template

After you’ve create the Azure SQL Server resource in your script, the next step is to specify a second resource of the type firewallRules which we will use to check that box on the server’s networking page.

resource firewallRule 'Microsoft.Sql/servers/firewallRules@2023-08-01-preview' = {
  name: 'AllowAllWindowsAzureIps'
  parent: sqlServer
  properties: {
    endIpAddress: '0.0.0.0'
    startIpAddress: '0.0.0.0'
  }
}

The important parts to note from that resource definition are the following:

• name: 'AllowAllWindowsAzureIps': This specifies the name of the firewall rule that is going to be created. In this instance, the rule needs to have this specific name in order to check the settings box in the portal and allow the access
• parent: sqlServer: This line is what tells the template that this firewall rule needs to be applied to the server we created in the same template previously, with the symbolic name of sqlServer.
• endIpAddress: '0.0.0.0': This, along with the startIpAddress, must be set to all 0s, which is what Azure looks for when trying to see if another Azure resource has access to the server. This “range” is special, reserved for this situation.
• startIpAddress: '0.0.0.0': This, along with the endIpAddress, must be set to all 0s, which is what Azure looks for when trying to see if another Azure resource has access to the server. This “range” is special, reserved for this situation.

Then you can also specify any and all Azure SQL Databases you want to be on your server in the template if you would like and then deploy the template. After the template has been deployed, navigate to your new server in the Azure Portal and review the networking settings to see that the “Allow Azure services and resources to access this server” box is successfully checked.

Summary

If you’ve been wondering how you can get the one box on the Azure SQL Server Networking page in the portal checked by using a Bicep template, it’s as simple as adding a firewall rule for the server with a start and end IP address of all 0s. This special firewall rule is used to indicate to Azure that a given resource is available to access through other Azure resources.

Let me know in the comments below if this tip helped you or if you have any questions regarding setting up firewall rules for an Azure SQL Server using Bicep.

Resources

• https://stackoverflow.com/questions/72433407/is-there-a-way-to-set-allow-access-to-azure-services-in-microsoft-dbforpostgre
• https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql#connections-from-inside-azure
• https://github.com/Azure/bicep-registry-modules/blob/main/avm/res/sql/server/main.bicep

Related Posts

• Using Custom Script Extensions with Bicep Templates
• Create an Azure SQL Server and Database with a Bicep Template
• Add RBAC Role Definitions to Resources with Bicep Template