SQL Server Audit and Vulnerability Scans with Bicep

Update 3/31/22: Realized this assumed Defender was already turned on the server so adding the Bicep to do that.

Update 4/14/23: Active example haws been moved to: https://github.com/JFolberth/Azure_IaC_Flavors/tree/main/code/sqlServerAAD/bicep

Introduction

This one is tricky. If you are well verse in Azure and some of the security best practices then you are aware that Microsoft Defender for Cloud recommends that SQL Server have Vulnerability Assessments and Auditing turned on. So how do you enable SQL Server Audit and Vulnerability Scans with Bicep?

These settings can be defined via Infrastructure as Code (IaC); however, it’s not very straightforward. First the vulnerability assessments should go to an Azure Storage Account. Audit Logs can go to a variety of places; however, Log Analytic Workspaces tend to be the most preferred so we will do that. On top of these individual components, we need to create them then we have to wire it all to talk to each other.

I’ll end by giving you the .bicep module for SQL Server that will configure all this. You just need to pass in the appropriate parameters.

Assumptions

• There already is an existing Log Analytic Workspace (You usually don’t have a 1:1 relationship on these)

What are Vulnerability Assessments and Audit Logs?

TL:DR These are important security features which Microsoft recommends you turn on.

Per Microsoft Documentation:

Vulnerability assessment is a scanning service built into Azure SQL Database. The service employs a knowledge base of rules that flag security vulnerabilities. It highlights deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data.

azure-sql/database/sql-vulnerability-assessment

The simple version is Microsoft’s way of scanning SQL to ensure best practice is being adhered to and flagging anything that may seem out of the ordinary.

So what about Audit Logs? This is a pretty standard term as it’s your SQL Audit logs. Per Microsoft

Azure SQL Database auditing tracks database events and writes them to an audit log in your Azure storage account, or sends them to Event Hub or Log Analytics for downstream processing and analysis

azure-sql/database/audit-log-format

Auditing

This is the trickier of the two. Why is that? There is a fairly under documented notion that for SQL Server auditing to be turned on the destination is actually the master database. I discovered this by going over the schema for the microsoft.sql/servers/auditsettings:

When using REST API to configure auditing, Diagnostic Settings with ‘SQLSecurityAuditEvents’ diagnostic logs category on the database should be also created.

Note that for server level audit you should use the ‘master’ database as {databaseName}.

This seems simple enough. There’s just one problem that exists both in ARM and Bicep; however, I could argue it is more obfuscated in Bicep. The relative parent and naming segments. The provider endpoint is for server yet the above makes you realize this needs to be a database segment. so….what do to do.

The diagnosticSettings will still be at a server and will look like Microsoft.Sql/servers/databases/providers/diagnosticSettings. To get this setting at the database we need to update the name of the individual resource. This would be accomplished by something like '${sqlServer.name}/master/microsoft.insights/LogAnalytics' The LogAnalytics piece can be substituted for the name of the diagnosticSettings you’d prefer.

The second half of this the actual tuning of the auditSettings to be collected was taken from microsoft.sql/servers/auditsettings and I just used the recommend subset. In this case that is

      'BATCH_COMPLETED_GROUP'
      'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
      'FAILED_DATABASE_AUTHENTICATION_GROUP'

However feel free to check any aditional auditng you require.

Vulnerability Assessments

The Vulnerability Assessments aren’t as hard to configure. It’s just more resources which again could make the case of ARM vs Bicep here since we need to create a Storage Account, Blob, and Container just in Azure Resources. Bicep this is easier to create individual resources vs ARM where these resources would be nested. This leads to something that looks a little off like:

resource storageAccountVulnerability 'Microsoft.Storage/storageAccounts@2021-08-01' = {
  name: vulnerabilitySAName
  kind: 'StorageV2'
  location: location
  sku: {
    name: 'Standard_LRS'
  }
}

resource storageAccountBlobVulnerability 'Microsoft.Storage/storageAccounts/blobServices@2021-08-01' = {
  name: 'default'
  parent: storageAccountVulnerability
}

resource storageAccountContainerVulnerability 'Microsoft.Storage/storageAccounts/blobServices/containers@2021-08-01' = {
  name: 'vulnerabilityscans'
  parent: storageAccountBlobVulnerability
}

Next up the vulnerabilityAssessments needs to configured the storageContainerPath. This requires a bit of string interpolation. I have opened a discussion on the Bicep repo page as feels this could be referenced as part of the storageAccountContainerVulnerability properties.

So, the end result is something like:

storageContainerPath: '${storageAccountVulnerability.properties.primaryEndpoints.blob}${storageAccountContainerVulnerability.name}'

Again, this looks a little goofy but does accomplish what we are setting out for.

Now the last part is how to grant the SQL Server access to write to the Storage Account. From what I have seen all the documentation instructs the usage of Access Keys. If you follow me, you know my preference on anything involving this type of Access is to use Role Based Access Control (RBAC). For additional reading on the topic check out “The Perilous Dilemma of RBAC in an Automated World” or “Nested Loops In Azure Bicep with an RBAC Example“

To leverage RBAC Controls, we need to set the SQL Server Resource’s identity property:

  identity:{
    type: 'SystemAssigned'
  }

And then we will need a block for the Role Assignment:

resource sqlStorageAccountRBAC 'Microsoft.Authorization/roleAssignments@2020-10-01-preview' = {
  name: guid(sqlServer.name, blobContributorRoleId,storageAccountVulnerability.name )
  scope: storageAccountVulnerability
  properties:{
    roleDefinitionId: blobContributorRoleId
    principalId: sqlServer.identity.principalId
    principalType: 'ServicePrincipal'
  }
  
}

Also set a variable to store the Blob Contributor Role ID:

var blobContributorRoleId = 'ba92f5b4-2d11-453d-a403-e96b0029c9fe'

This should then give the SQL Server contributor access over the Blob, thus making it inherently safer than passing keys, even if they are being securely passed.

Enable Defender

Azure Defender for Cloud may not be automatically enabled so we need to turn that one with the following Bicep code:

resource sqlServerAdvancedSecurityAssessment 'Microsoft.Sql/servers/securityAlertPolicies@2021-08-01-preview' = {
  name: '${sqlServer.name}/advancedSecurityAssessment'
  properties:{
    state: 'Enabled'
  }
}

Conclusion

Automating SQL Server Audit and Vulnerability Scans with Bicep was not as easy as I had hoped. It took some time and manipulation to get it working and something where I could argue which was easier Bicep or ARM. Don’t get me wrong Bicep is still the way to go…..just don’t start with this one. This is one of those that has always been a pain to automate and it’s a set it and forget, for the most part, configuration.

This automation though is required to be consistent across environments and even further enabled by the Microsoft Defender for Cloud recommendations.

Also as promised here is the full SQL Server Module which you should be able to plug and play into your existing SQL Server Bicep Deployments. Which to my early point with Bicep should be 100% for you to consume. Feel free to provide any feedback!

sql-server.module.bicep

param serverName string
param tags object
param sqlAdminGroupObjectId string
param sqlAdminGroupName string
param location string = resourceGroup().location
param vulnerabilityScanEmails string = 'SQLAdmin@TEST.COM'
param vulnerabilitySAName string
param logAnalyticsWorkspaceID string

var blobContributorRoleId = 'ba92f5b4-2d11-453d-a403-e96b0029c9fe'
resource sqlServerAdvancedSecurityAssessment 'Microsoft.Sql/servers/securityAlertPolicies@2021-08-01-preview' = {
  name: '${sqlServer.name}/advancedSecurityAssessment'
  properties:{
    state: 'Enabled'
  }
}
resource sqlServer 'Microsoft.Sql/servers@2021-05-01-preview' = {
  name: serverName
  location: location
  tags: tags
  properties: {
    version: '12.0'
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    restrictOutboundNetworkAccess: 'Disabled'
    administrators: {
      administratorType: 'ActiveDirectory'
      tenantId: subscription().tenantId
      principalType: 'Group'
      azureADOnlyAuthentication: true
      login: sqlAdminGroupName
      sid: sqlAdminGroupObjectId
    }
  }
  identity:{
    type: 'SystemAssigned'
  }
}
resource storageAccountVulnerability 'Microsoft.Storage/storageAccounts@2021-08-01' = {
  name: vulnerabilitySAName
  kind: 'StorageV2'
  location: location
  sku: {
    name: 'Standard_LRS'
  }
}

resource storageAccountBlobVulnerability 'Microsoft.Storage/storageAccounts/blobServices@2021-08-01' = {
  name: 'default'
  parent: storageAccountVulnerability
}

resource storageAccountContainerVulnerability 'Microsoft.Storage/storageAccounts/blobServices/containers@2021-08-01' = {
  name: 'vulnerabilityscans'
  parent: storageAccountBlobVulnerability
}

resource sqlVulnerability 'Microsoft.Sql/servers/vulnerabilityAssessments@2021-08-01-preview' = {
  name: 'default'
  parent: sqlServer
  properties: {
    recurringScans:{
      emails: [
        vulnerabilityScanEmails
      ]
      isEnabled: true
    }
    storageContainerPath: '${storageAccountVulnerability.properties.primaryEndpoints.blob}${storageAccountContainerVulnerability.name}'
  }
  
   dependsOn: [
    sqlStorageAccountRBAC
    sqlServerAdvancedSecurityAssessment
  ] 
}

resource sqlStorageAccountRBAC 'Microsoft.Authorization/roleAssignments@2020-10-01-preview' = {
  name: guid(sqlServer.name, blobContributorRoleId,storageAccountVulnerability.name )
  scope: storageAccountVulnerability
  properties:{
    roleDefinitionId: blobContributorRoleId
    principalId: sqlServer.identity.principalId
    principalType: 'ServicePrincipal'
  }
  
}

 resource SqlDbDiagnosticSettings 'Microsoft.Sql/servers/databases/providers/diagnosticSettings@2021-05-01-preview' ={
  name: '${sqlServer.name}/master/microsoft.insights/LogAnalytics'

  properties: {
    workspaceId: logAnalyticsWorkspaceID
    logs:[
      {
        category: 'SQLSecurityAuditEvents'
        enabled: true
      }
    ]
  }
  }
 resource sqlAudit 'Microsoft.Sql/servers/auditingSettings@2021-08-01-preview'={
   name: 'default'
   parent: sqlServer
   properties:{
     auditActionsAndGroups:[
      'BATCH_COMPLETED_GROUP'
      'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
      'FAILED_DATABASE_AUTHENTICATION_GROUP'
     ]
     isAzureMonitorTargetEnabled: true
     state:'Enabled'
   }
 }