Enable Vulnerability Assessment on SQL Servers with Azure Policy

10 Min. Read

Azure Policy is a service in Azure that you use to create, assign, and manage policies. These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. Azure Policy meets this need by continuously evaluating your resources for non-compliance with assigned policies.

In this article, we will look at how to enable vulnerability assessment on SQL Servers with Azure Policy which can help you maintain regulatory compliance, and gain insight to discover, track, and help you remediate potential database vulnerabilities.

Introduction

You have a policy in your organization that dictates to enable vulnerability assessment on all new and existing Azure SQL Servers within your subscriptions. As you know very well, you should continuously assess your SQL Servers to identify database vulnerabilities.

SQL vulnerability assessment is a service that provides visibility into your security state. Vulnerability assessment includes actionable steps to resolve security issues and enhance your database security. It can help you to monitor a dynamic database environment where changes are difficult to track and improve your SQL security posture.

Vulnerability assessment (VA) is part of the Azure Defender (Security Center) for SQL offering, which is a unified package for advanced SQL security capabilities.

If we take a quick look at Azure Security Center as shown in the figure below, we do have already a built-in Policy definition that is based on the default Azure Security Benchmark (V2) that requires having vulnerability assessment enabled on SQL Servers to pass this recommendation, as well as we have an identical security recommendation for Azure SQL Managed Instances.

Vulnerability assessment should be enabled on your SQL servers
Vulnerability assessment should be enabled on your SQL servers

Based on this built-in policy, when you deploy a new SQL Server, Azure Security Center will evaluate the configuration and recommends enabling vulnerability assessment to detect anomalous activities that could indicate a threat to your database.

However, this policy does not auto-remediate the unhealthy SQL resources, therefore, you should manually remediate them as described in the ‘Remediation steps‘, you can also use the ‘Quick fix logic‘ option to remediate all unhealthy resources at once, or you can trigger a logic app if you want. You can also exempt this recommendation if needed, but I don’t recommend doing so because it’s categorized as ‘High‘ Severity.

Please note that vulnerability assessment is available for Azure SQL Database, Azure SQL Managed Instances, and Azure Synapse Analytics. For the purpose of this article, I will share two policies, one for Azure SQL Servers, and the second one for Azure SQL Managed Instances (MI), you can choose based on the service that you are using.

Once you have invested time and effort in remediating these resources to improve your security posture, you want to make sure that it will not decrease by a serious percentage, again. The problem is that the remediation is always done for existing resources, but NOT for new ones. You can think of remediating resources as a reactive versus a proactive approach.

As cybersecurity hygiene (practice), you need to have a solid Azure Governance to ensure that the new resources you deploy will have certain security standards, patterns, and configurations.

To ensure proper governance, we need to use Azure Policy. This will allow us to auto-enable and enforce vulnerability assessment on newly deployed SQL Servers, as well as remediating existing resources at scale, so you can make sure your organization’s policy and security requirements are met.

Prerequisites

To follow this article, you need to have the following:

1) An Azure subscription. If you don’t have an Azure subscription, you can create a free one here.

2) At least one Azure SQL Server is deployed in your subscription. You can follow the quick start guide here to create an Azure SQL Database.

3) Azure Security Center with Azure Defender enabled. You could enable Azure Defender for SQL at the subscription level under the Pricing & Settings page as shown in the figure below, or you could enable Azure Defender for Azure SQL at the resource level only to reduce costs.

4) Please note that for Azure Defender for SQL, if the status is set to “Off” it doesn’t mean that it is disabled everywhere, just that it is not enabled for all databases in the scope (e.g. subscription) and that it won’t apply to newly created databases only. However, it might still be that this functionality is enabled for some individual SQL resources. At the time of this writing, you can look at the “Azure Defender for SQL should be enabled for unprotected Azure SQL servers” recommendation to get this level of detail. You could try Azure Defender for SQL at no additional cost during the 30 days trial period. After that, the price is $15 for each server per month.

Azure Defender for SQL
Azure Defender for SQL

5) Next, you need to have the appropriate permissions to create and manage Azure Policy definitions. The Azure RBAC built-in roles that you can use are Resource Policy Contributor or Security Admin.

6) Last but not least, you need to have the appropriate permissions to assign the Contributor role for the Managed Identity (Application ID) created during the assignment of the policy either on a management group or a subscription, so the policy with “DeployIfNotExists” can configure your SQL Servers. Azure Policy creates a managed identity for each assignment but must have details about what roles to grant the managed identity. If the managed identity is missing roles, an error is displayed during the assignment of the policy or an initiative. Please note that when creating an assignment through the Azure Portal, the role assignments are auto-granted. However, when using PowerShell, you must manually configure the managed identity. More information can be found here.

Assuming you have all the prerequisites in place, take now the following steps:

Create Custom Policy definition

A quick overview of Azure Policy effects. Each policy definition that you create in the Azure Policy has a single effect. That effect determines what happens when the policy rule is evaluated to match. The effects behave differently if they are for a new resource, an updated resource, or an existing resource.

In this example, we need to enable vulnerability assessment for all Azure SQL Servers and on existing resource(s) in our environment. For this to work, we need to use the “DeployIfNotExists” policy.

To understand how the Azure Policy effect works with the “DeployIfNotExists” policy definition, please check the official documentation from Microsoft here.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Definitions” → “+ Policy definition”.

For the “Definition location“, select the location by clicking the ellipsis [] and select either a management group or a subscription.

In the “Name“ field, give a descriptive name for the policy definition such as – “Enable Vulnerability assessment on SQL servers” and add a Description.

In the “Category” section, select the appropriate category for this policy. In this example, we will choose “SQL” as shown in the figure below.

New Policy definition
New Policy definition

For the “POLICY RULE“, paste the following policy definition in JSON format based on the service you are using (Azure SQL Servers or Azure SQL Managed Instances), and then click “Save“.

For the “Role definitions“, make sure that the Contributor is selected. The contributor role is required to enable vulnerability assessment on the SQL resources.

Here is the Policy definition in JSON format for Azure SQL Servers:

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers"
        },
        {
          "field": "kind",
          "notContains": "analytics"
        }
      ]
    },
    "then": {
      "effect": "[parameters('effect')]",
      "details": {
        "type": "Microsoft.Sql/servers/vulnerabilityAssessments",
        "name": "default",
        "existenceCondition": {
          "field": "Microsoft.Sql/servers/vulnerabilityAssessments/recurringScans.isEnabled",
          "equals": "True"
        },
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "parameters": {
                "serverName": {
                  "type": "string"
                },
                "location": {
                  "type": "string"
                }
              },
              "variables": {
                "getDeploymentName": "[concat('PolicyDeployment-Get-', parameters('serverName'))]",
                "updateDeploymentName": "[concat('PolicyDeployment-Update-', parameters('serverName'))]"
              },
              "resources": [
                {
                  "apiVersion": "2020-06-01",
                  "type": "Microsoft.Resources/deployments",
                  "name": "[variables('getDeploymentName')]",
                  "properties": {
                    "mode": "Incremental",
                    "template": {
                      "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                      "contentVersion": "1.0.0.0",
                      "resources": [],
                      "outputs": {}
                    }
                  }
                },
                {
                  "apiVersion": "2020-06-01",
                  "type": "Microsoft.Resources/deployments",
                  "name": "[variables('updateDeploymentName')]",
                  "properties": {
                    "mode": "Incremental",
                    "expressionEvaluationOptions": {
                      "scope": "inner"
                    },
                    "template": {
                      "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                      "contentVersion": "1.0.0.0",
                      "parameters": {
                        "location": {
                          "type": "string"
                        },
                        "serverName": {
                          "type": "string"
                        }
                      },
                      "variables": {
                        "serverResourceGroupName": "[resourceGroup().name]",
                        "subscriptionId": "[subscription().subscriptionId]",
                        "uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('serverResourceGroupName'), parameters('location'))]",
                        "storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]"
                      },
                      "resources": [
                        {
                          "type": "Microsoft.Storage/storageAccounts",
                          "apiVersion": "2019-04-01",
                          "name": "[variables('storageName')]",
                          "location": "[parameters('location')]",
                          "sku": {
                            "name": "Standard_LRS"
                          },
                          "kind": "StorageV2",
                          "properties": {
                            "minimumTlsVersion": "TLS1_2",
                            "supportsHttpsTrafficOnly": "true",
                            "allowBlobPublicAccess": "false"
                          }
                        },
                        {
                          "type": "Microsoft.Sql/servers/securityAlertPolicies",
                          "apiVersion": "2017-03-01-preview",
                          "name": "[concat(parameters('serverName'), '/Default')]",
                          "properties": {
                            "state": "Enabled",
                            "emailAccountAdmins": true
                          }
                        },
                        {
                          "type": "Microsoft.Sql/servers/vulnerabilityAssessments",
                          "apiVersion": "2018-06-01-preview",
                          "name": "[concat(parameters('serverName'), '/Default')]",
                          "dependsOn": [
                            "[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
                            "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/securityAlertPolicies/Default')]"
                          ],
                          "properties": {
                            "storageContainerPath": "[concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment')]",
                            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), '2018-02-01').keys[0].value]",
                            "recurringScans": {
                              "isEnabled": true,
                              "emailSubscriptionAdmins": true,
                              "emails": []
                            }
                          }
                        }
                      ],
                      "outputs": {}
                    },
                    "parameters": {
                      "serverName": {
                        "value": "[parameters('serverName')]"
                      },
                      "location": {
                        "value": "[parameters('location')]"
                      }
                    }
                  }
                }
              ]
            },
            "parameters": {
              "serverName": {
                "value": "[field('name')]"
              },
              "location": {
                "value": "[field('location')]"
              }
            }
          }
        }
      }
    }
  },
  "parameters": {
    "effect": {
      "type": "String",
      "metadata": {
        "displayName": "Effect",
        "description": "Enable or disable the execution of the policy"
      },
      "allowedValues": [
        "DeployIfNotExists",
        "Disabled"
      ],
      "defaultValue": "DeployIfNotExists"
    }
  }
}

Here is another Policy definition for Azure SQL Managed Instances:

{
  "mode": "All",
  "policyRule": {
    "if": {
      "field": "type",
      "equals": "Microsoft.Sql/managedInstances"
    },
    "then": {
      "effect": "[parameters('effect')]",
      "details": {
        "type": "Microsoft.Sql/managedInstances/vulnerabilityAssessments",
        "name": "default",
        "existenceCondition": {
          "field": "Microsoft.Sql/managedInstances/vulnerabilityAssessments/recurringScans.isEnabled",
          "equals": "True"
        },
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "parameters": {
                "serverName": {
                  "type": "string"
                },
                "location": {
                  "type": "string"
                }
              },
              "variables": {
                "getDeploymentName": "[concat('PolicyDeployment-Get-', parameters('serverName'))]",
                "updateDeploymentName": "[concat('PolicyDeployment-Update-', parameters('serverName'))]"
              },
              "resources": [
                {
                  "apiVersion": "2020-06-01",
                  "type": "Microsoft.Resources/deployments",
                  "name": "[variables('getDeploymentName')]",
                  "properties": {
                    "mode": "Incremental",
                    "template": {
                      "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                      "contentVersion": "1.0.0.0",
                      "resources": [],
                      "outputs": {}
                    }
                  }
                },
                {
                  "apiVersion": "2020-06-01",
                  "type": "Microsoft.Resources/deployments",
                  "name": "[variables('updateDeploymentName')]",
                  "properties": {
                    "mode": "Incremental",
                    "expressionEvaluationOptions": {
                      "scope": "inner"
                    },
                    "template": {
                      "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                      "contentVersion": "1.0.0.0",
                      "parameters": {
                        "location": {
                          "type": "string"
                        },
                        "serverName": {
                          "type": "string"
                        }
                      },
                      "variables": {
                        "serverResourceGroupName": "[resourceGroup().name]",
                        "subscriptionId": "[subscription().subscriptionId]",
                        "uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('serverResourceGroupName'), parameters('location'))]",
                        "storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]"
                      },
                      "resources": [
                        {
                          "type": "Microsoft.Storage/storageAccounts",
                          "apiVersion": "2019-04-01",
                          "name": "[variables('storageName')]",
                          "location": "[parameters('location')]",
                          "sku": {
                            "name": "Standard_LRS"
                          },
                          "kind": "StorageV2",
                          "properties": {
                            "minimumTlsVersion": "TLS1_2",
                            "supportsHttpsTrafficOnly": "true",
                            "allowBlobPublicAccess": "false"
                          }
                        },
                        {
                          "type": "Microsoft.Sql/managedInstances/securityAlertPolicies",
                          "apiVersion": "2017-03-01-preview",
                          "name": "[concat(parameters('serverName'), '/Default')]",
                          "properties": {
                            "state": "Enabled",
                            "emailAccountAdmins": true
                          }
                        },
                        {
                          "type": "Microsoft.Sql/managedInstances/vulnerabilityAssessments",
                          "apiVersion": "2018-06-01-preview",
                          "name": "[concat(parameters('serverName'), '/Default')]",
                          "dependsOn": [
                            "[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
                            "[concat('Microsoft.Sql/managedInstances/', parameters('serverName'), '/securityAlertPolicies/Default')]"
                          ],
                          "properties": {
                            "storageContainerPath": "[concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment')]",
                            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), '2018-02-01').keys[0].value]",
                            "recurringScans": {
                              "isEnabled": true,
                              "emailSubscriptionAdmins": true,
                              "emails": []
                            }
                          }
                        }
                      ],
                      "outputs": {}
                    },
                    "parameters": {
                      "serverName": {
                        "value": "[parameters('serverName')]"
                      },
                      "location": {
                        "value": "[parameters('location')]"
                      }
                    }
                  }
                }
              ]
            },
            "parameters": {
              "serverName": {
                "value": "[field('name')]"
              },
              "location": {
                "value": "[field('location')]"
              }
            }
          }
        }
      }
    }
  },
  "parameters": {
    "effect": {
      "type": "String",
      "metadata": {
        "displayName": "Effect",
        "description": "Enable or disable the execution of the policy"
      },
      "allowedValues": [
        "DeployIfNotExists",
        "Disabled"
      ],
      "defaultValue": "DeployIfNotExists"
    }
  }
}

In this custom policy, I am looking for all Microsoft SQL Servers and all SQL Managed Instances, and then I am checking if vulnerability assessments scan is enabled.

The ExistenceCondition option in the template above is super useful for Azure SQL Servers and SQL Managed Instances that have already vulnerability assessments enabled. If this condition is true, the policy definition will skip the deployment defined in the “DeployIfNotExists” section.

As part of this template, a new storage account is automatically created and configured to store your Vulnerability Assessment scan results. If you’ve already enabled Azure Defender for another server in the same resource group and region, then the existing storage account will be used.

To learn more about the ExistenceCondition option for the “DeployIfNotExists” properties, please check the official documentation here.

Assign Custom Policy definition

To assign the custom policy definition, take the following steps:

If you want to automate the deployment and assignment of Azure Policies via Azure DevOps instead of using the Azure Portal, then please check the following step-by-step guide.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Assignments”. An assignment is a policy that has been assigned to take place within a specific scope.

Select “Assign policy” from the top of the “Policy | Assignments” page.

On the “Assign Policy” page, select the Scope by clicking the ellipsis [] and select either a management group or subscription. You can optionally select a resource group if you want. A scope determines what resources or grouping of resources the policy assignment gets enforced on. Then click Select at the bottom of the Scope page.

Select the Policy definition ellipsis [] to open the list of available definitions. Choose the custom policy that we created in the previous step. The Policy enforcement is Enabled by default. Click Next to continue.

On the Parameters page, specify the parameters for this policy assignment (DeployIfNotExists or Disabled). The default value is set to “DeployIfNotExists“, you can disable the effect of this policy later on by setting the effect to “Disabled“. Click Next to continue.

Assign Policy
Assign Policy

In the Remediation page, by default “Create a Managed Identity” is selected for you because existing resources can be updated via a remediation task after the policy is assigned only. Choose the default location for the “Managed Identity“, this is required because policies with the “deployIfNotExists” and “Modify” effect types need the ability to modify resources and edit the configuration on existing resources respectively. To do this, a managed identity will be created automatically for each policy assignment. This identity will also be given the “Contributor” permissions on the scope that you have selected. Click Next to continue.

Create a Managed Identity
Create a Managed Identity

On the Non-compliance messages page, set the desired message. Then click the “Review + create” button.

Finally, click “Create” to create the assignment.

Verify vulnerability assessment on SQL Servers

To verify that the custom policy is deployed successfully, you need to wait for at least 30 minutes after a resource has been created or updated. The policy won’t be triggered immediately, this is by design. To trigger the policy compliance scan manually (immediately), open the cloud shell and run the following command:

$job = Start-AzPolicyComplianceScan -AsJob
$job | ft -AutoSize

The scan job will run in the background.

Behind the scene, Azure Policy will create a Remediation task as shown in the figure below. To remediate existing resources, select the desired policy and then click on the “Remediate” button.

Policy Remediation | Enable Vulnerability Assessment on SQL
Policy Remediation | Enable Vulnerability Assessment on SQL

Please note that during an evaluation cycle, the policy definition with a “DeployIfNotExists” effect that matches resources is marked as non-compliant only, but no action is taken on that resource. For this reason, the remediation task was created when you assign the policy definition as described in the previous section.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Remediation”. In the Policy | Remediation page, select the Remediation tasks tab, and check the status of the auto-remediation task. It’s completed successfully without any error as shown in the figure below.

Remediation State - Complete
Remediation State – Complete

To verify that vulnerability assessment is enabled, from the Azure Portal, open the specific resource in Azure SQL Database, SQL Managed Instance Database, or Azure Synapse.

Under the Security heading, select Security Center.

Next, select (Configure) on the link to open the Azure Defender for SQL settings pane for either the entire server or managed instance as shown in the figure below:

Azure Defender for SQL - Enabled at the server-level
Azure Defender for SQL – Enabled at the server-level

In the Server settings page, you can see that Azure Defender for SQL settings is ‘ON‘, as well as the storage account, is configured as shown in the figure below:

Azure SQL - Server Settings
Azure SQL – Server Settings

Please note that vulnerability scans will be triggered automatically once a week. In most cases, it will be on the day the Vulnerability Assessment has been enabled and saved. A scan result summary will be sent to the email addresses you provide.

The scan is lightweight and safe. It takes a few seconds to run and is entirely read-only. It doesn’t make any changes to your database.

That’s it there you have it!

Summary

In this article, we showed you how to enable and enforce vulnerability assessment on SQL Servers with Azure Policy, so you can make sure your organization’s policy and security requirements are met.

Azure Policies are becoming increasingly popular, as they provide a wide range of management capabilities over Azure resources. Not only do they allow us to enforce policy rules to control Azure resources, but they can also enable us to automatically evaluate compliance and remediate non-compliant resources.

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-

Related Posts

Previous

Enable Azure SQL Auditing with Azure Policy

Running Azure Stack HCI on #DataON Integrated System All-NVMe Flash

Next

Let me know what you think, or ask a question...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Stay in Touch

Never miss out on your favorite posts and our latest announcements!

The content of this website is copyrighted from being plagiarized!

You can copy from the 'Code Blocks' in 'Black' by selecting the Code.

Please send your feedback to the author using this form for any 'Code' you like.

Thank you for visiting!