Ensuring SQL Server Compliance with Bicep and Azure Policy

Managing SQL Server compliance in Azure can be tough. Always keep security features on, like data encryption, auditing, and access control. Doing this manually isn’t just tedious; it’s risky. That’s where Azure Policy comes in. It enforces security and compliance without requiring manual intervention. So, you won’t stress over misconfigurations.

But here’s the thing: policies are only effective if they’re consistently applied. If you set up SQL Servers by hand or use scripts that don’t follow compliance rules, you create gaps. This is where Bicep can make a huge difference. It allows you to set up your infrastructure as code. This way, every SQL Server deployment starts with security best practices in mind.

In this post, we’ll walk through how Azure Policy and Bicep work together to keep your SQL Servers compliant, without the headache.

Why SQL Server Compliance Matters

SQL Server stores sensitive business and customer data. So, security is very important. But keeping every deployment compliant can feel like a never-ending battle. Some of the most common requirements include:

  • Data encryption (at rest and in transit)
  • Auditing and logging to meet regulatory standards
  • Access control to prevent unauthorized access
  • Automatic remediation of misconfigured resources

You can’t afford to leave these to chance. Azure Policy lets you set up guardrails. This ensures every SQL Server meets your security standards automatically.

What is Azure Policy? (And Why Should You Care?)

Think of Azure Policy as your compliance watchdog. It keeps an eye on your environment all the time. It makes sure resources match your organization’s standards. When applied to SQL Server, Azure Policy can:

  • Audit existing SQL Server instances to find compliance gaps
  • Deny the creation of non-compliant resources
  • Fix misconfigured settings without manual intervention.

For example, if someone attempts to deploy a SQL Server without Transparent Data Encryption (TDE), a policy can either block the deployment or automatically correct the issue. This means you don’t have to manually check every deployment, it just happens.

Deploying SQL Server with Bicep and Enforcing Compliance

Step 1: Define SQL Server Deployment in Bicep

Instead of clicking around in the Azure portal, you can define your SQL Server as code using Bicep. This ensures that every deployment follows a standardized and secure configuration:

param sqlServerName string = 'my-secure-sqlserver'

param databaseName string = 'db1'

param location string = 'westus'

@secure()
param administratorLoginPassword string

resource sqlServer 'Microsoft.Sql/servers@2024-05-01-preview' = {
  name: 'my-secure-sqlserver'
  location: 'westus'
  properties: {
    administratorLogin: 'sqladminuser'  // Replace with your preferred admin username
    administratorLoginPassword: administratorLoginPassword
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Disabled'
  }
}

This Bicep template does two things:

  1. Create a SQL Server with TLS 1.2 for better security.
  2. It disables public network access to reduce the attack surface.

To deploy this Bicep template, save it in a file (i.e. sqlserver.bicep), and run the following command:

az deployment group create --resource-group your-resource-group --template-file sqlserver.bicep --parameters administratorLoginPassword=your-password

Step 2: Apply an Azure Policy for Compliance

Let’s add an Azure Policy to ensure all databases have Transparent Data Encryption (TDE) enabled now that our SQL Server deployment is secure.

Here’s an example Azure Policy definition to enforce TDE:

{
  "properties": {
    "displayName": "Enforce Transparent Data Encryption on SQL Databases",
    "description": "This policy enforces Transparent Data Encryption on SQL Databases",
    "mode": "All",
    "metadata": {
      "version": "1.0.0",
      "category": "SQL"
    },
    "policyRule": {
      "if": {
        "allOf": [
          {
            "field": "Microsoft.Sql/servers/databases/transparentDataEncryption",
            "exists": "true"
          },
          {
            "field": "Microsoft.Sql/servers/databases/transparentDataEncryption[*].status",
            "notEquals": "Enabled"
          }
        ]
      },
      "then": {
        "effect": "Deny"
      }
    }
  }
}

When you deploy with Bicep, every SQL Server automatically follows these security best practices.

Instead of using the JSON format, you can also define the policy in Bicep:

resource sqlTdePolicy 'Microsoft.Authorization/policyDefinitions@2025-01-01' = {
  name: 'deny-disabled-transparent-data-encryption-sql'
  properties: {
    displayName: 'Deny Disabled Transparent Data Encryption on SQL Databases'
    description: 'Do not allow Azure SQL databases to have Transparent Data Encryption disabled.'
    mode: 'All'
    policyRule: {
      if: {
        allOf: [
          {
            field: 'type'
            equals: 'Microsoft.Sql/servers/databases/transparentDataEncryption'
          }
          {
            field: 'Microsoft.Sql/transparentDataEncryption.status'
            notEquals: 'Enabled'
          }
          {
            field: 'Microsoft.Sql/servers/databases/transparentDataEncryption/state'
            notEquals: 'enabled'
          }
        ]
      }
      then: {
        effect: 'Deny'
      }
    }
  }
}

You can automatically assign this policy to your subscription using Bicep as well.

resource sqlTdePolicyAssignment 'Microsoft.Authorization/policyAssignments@2025-01-01' = {
  name: 'enforce-transparent-data-encryption-sql-assignment'
  properties: {
    displayName: 'Enforce Transparent Data Encryption on SQL Databases'
    policyDefinitionId: sqlTdePolicy.id
  }
}

Save the Bicep template in a file (i.e. sqltdepolicy.bicep), and run the following command to deploy it:

az deployment sub create --location your-location --template-file .\sqltdepolicy.bicep --parameters subscriptionId=your-subscription-id

You also do this manually through the Azure portal:

  1. Go to Azure Policy blade in the Azure portal
  2. Go to “Definitions”
  3. Find the definition. You can use the search bar
  4. Open the definition
  5. Click on “Assign policy”
  6. Assign the policy to your subscription
  7. Click on “Review + create”
  8. Click on “Create”

Step 3: Create the database

Now let’s create a database that doesn’t have TDE enabled:

param sqlServerName string = 'my-secure-sqlserver'
param databaseName string = 'db1'

param location string = 'westus'

resource sqlDatabase 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
  name: '${sqlServerName}/${databaseName}'
  location: location
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    maxSizeBytes: 1073741824 // 1 GB
    sampleName: 'AdventureWorksLT' // Optional: Use a sample database, or remove this line for an empty database
  }
  sku: {
    name: 'S0' // Standard tier
    tier: 'Standard'
    capacity: 10
  }
}

resource tdeSetting 'Microsoft.Sql/servers/databases/transparentDataEncryption@2024-05-01-preview' = {
  parent: sqlDatabase
  name: 'current'
  properties: {
    state: 'Disabled'
  }
}

Save the Bicep template in a file (i.e. sqldatabase.bicep), and run the following command to deploy it:

az deployment group create --resource-group your-resource-group --template-file sqldatabase.bicep

When you deploy this Bicep template you will get an error because the policy is blocking the deployment. You will see something like this:

{'code': 'InvalidTemplateDeployment', 'message': 'The template deployment failed because of policy violation. Please see details for more information.'}

This is exactly what we want to achieve! The policy is blocking the deployment because the TDE is not enabled.

What would happen if there is already a database that doesn’t have TDE enabled?

To demonstrate this I:

  • Removed the current assignment of the policy
  • Created a sql database without TDE
  • Assigned the policy again with the correct effect

As we would expect, if we create the policy after the databases are already created, the policy will not be enforced. Instead we will see that the database is not compliant.

Azure Policy

Click on the policy and we will go to the policy details.

Azure Policy

Click on the “…” button on the resource below in the screen and click “View resource”.

Azure Policy

This will bring you to the resource that is not compliant.

Azure Policy

Congratulations! You’ve just set up a policy that enforces TDE on all SQL databases.

Why This Approach Works So Well

Using Bicep for deployment and Azure Policy for compliance gives you a strong mix that:

  • Ensures every SQL Server deployment is secure from day one.
  • Prevents misconfigurations before they happen
  • Automatically audits existing resources.
  • Scales across multiple teams and environments

This isn’t only about easier compliance. It’s about making it automatic. That way, you won’t have to chase down security issues all the time.

There are also options to automatically remediate non-compliant resources. This way, you can ensure that your resources are always compliant. This was not part of this blog post but I will write about this in the future.

Other Ways to Use Azure Policy with Bicep and SQL Server

There are many ways to use Azure Policy with Bicep and SQL Server. Here are some examples of what you can do. I will be writing about these examples in the future that may be interesting to you:

  • Enforce Private Access & Block Public Exposure

    • Why? Prevents SQL Servers from being exposed to the public internet by forcing private endpoints.
    • How?
      • Block SQL Servers with public access enabled.
      • Require a Private Link connection.

  • Require TLS 1.2 or Higher for Secure Connections

    • Why? Keeps data encrypted during transmission, reducing security risks.
    • How?
      • Block SQL Servers using old TLS versions (below 1.2).
      • Require all servers to use TLS 1.2 or newer.

  • Enable Auditing & Log Analytics for Monitoring

    • Why? Ensures all queries, access logs, and security events are captured for auditability and compliance.
    • How?
      • Block SQL Servers that don’t have auditing enabled.
      • Require logs to be sent to Azure Monitor, Event Hub, or a Storage Account.

  • Turn On Advanced Threat Protection (ATP)

    • Why? Detects unusual database activity, like brute-force attacks and SQL injection.
    • How?
      • Require ATP to be enabled on all SQL Servers.
      • Ensure email alerts are set up for security notifications.

  • Enforce Geo-Replication for High Availability

    • Why? Ensures business continuity by keeping a backup copy of your database in another region.
    • How?
      • Block databases without geo-replication enabled.
      • Automatically configure a failover group for disaster recovery.

  • Protect Sensitive Data with Row-Level Security (RLS)

    • Why? Limits who can see what in your database by restricting data visibility based on user roles.
    • How?
      • Ensure Row-Level Security (RLS) is enabled for sensitive tables.

  • Block Deprecated SQL Server Versions

    • Why? Prevents security risks from unsupported and outdated SQL versions.
    • How?
      • Block deployments using old SQL Server versions.
      • Require SQL Server 2022 or newer.

  • Require Customer-Managed Keys (CMK) for Encryption

    • Why? Gives you full control over encryption keys instead of relying on Microsoft-managed keys.
    • How?
      • Require TDE (Transparent Data Encryption) to use your own Key Vault key.

Stay tuned for more examples on how to use Azure Policy with Bicep and SQL Server!

Final Thoughts: Compliance Without the Hassle

Staying compliant doesn’t have to be a manual, time-consuming process. Start adding security to your infrastructure by using Bicep with Azure Policy.

So what’s next?

  • Try it out! Deploy a SQL Server using Bicep and assign an Azure Policy to enforce compliance.
  • Use Azure Policy to check if your current SQL Servers meet security rules.
  • Set up remediation to automatically fix misconfigured resources.

Bicep and Azure Policy make SQL Server compliance a key part of your deployment. Now, it’s not an afterthought.