Enable SQL Encryption Monitoring

01 Sign in to Azure Management Console.

02 Navigate to Azure Security Center blade at https://portal.azure.com/#blade/Microsoft_Azure_Security/SecurityMenuBlade/.

03 In the navigation panel, choose Security policy to access Policy Management portal.

04 On the Policy Management page, click on the name of the subscription that you want to examine to access the selected subscription configuration settings.

05 On the Security Policy page, in the Data category, check the Monitor unencrypted SQL databases in Azure Security Center configuration setting status. If the setting is set to Disabled, the SQL encryption monitoring for Microsoft Azure SQL servers is not enabled in the current Azure subscription.

06 Repeat step no. 4 and 5 for each Microsoft Azure subscription available in your account.

01 Run account get-access-token command (Windows/macOS/Linux) using custom query filters to describe the "Monitor SQL Encryption" feature status for the current Azure account subscription:

az account get-access-token
	--query "{subscription:subscription,accessToken:accessToken}"
	--out tsv | xargs -L1 bash -c 'curl -X GET -H "Authorization: Bearer $1" -H "Content-Type: application/json" https://management.azure.com/subscriptions/$0/providers/Microsoft.Authorization/policyAssignments/SecurityCenterBuiltIn?api-version=2018-05-01' | jq 'select(.name=="SecurityCenterBuiltIn")'|jq '.properties.parameters.sqlEncryptionMonitoringEffect.value'

02 The command output should return the SQL encryption monitoring configuration status:

"Disabled"

03 Repeat step no. 1 and 2 for each Microsoft Azure subscription available in your account.

01 Sign in to Azure Management Console.

02 Navigate to Azure Security Center blade at https://portal.azure.com/#blade/Microsoft_Azure_Security/SecurityMenuBlade/.

03 In the navigation panel, choose Security policy to access Policy Management portal.

04 On the Policy Management page, click on the name of the subscription that you want to examine to access the selected subscription configuration settings.

05 On the Security Policy page, click on the ASC Default (subscription: <azure-subscription-id>) policy assignment to edit the policy configuration.

06 On the selected policy assignment page, in the PARAMETERS section, select AuditIfNotExists from Monitor SQL encryption dropdown list to enable the monitoring of unencrypted Azure SQL database servers available in the current subscription.

07 Click Save to apply the changes. If successful, the following message should be displayed: "Updating policy assignment succeeded". Once the configuration changes are saved, the "Monitor SQL Encryption" feature becomes active for the selected Azure subscription.

08 If required, repeat steps no. 4 – 7 for other Microsoft Azure cloud subscription available.

01 Define the necessary parameters for the account get-access-token command, where the sqlEncryptionMonitoringEffect configuration parameter is enabled. Save the following content to a JSON file named enable-sql-encryption-monitoring.json and replace the highlighted details, i.e. <azure-subscription-id> and <policy-definition-id>, with your own Azure account details:

{
   "properties":{
      "displayName":"ASC Default (subscription: <azure-subscription-id>)",
      "policyDefinitionId":"/providers/Microsoft.Authorization/policySetDefinitions/<policy-definition-id>",
      "scope":"/subscriptions/<azure-subscription-id>",
      "parameters":{
         "sqlEncryptionMonitoringEffect":{
            "value":"AuditIfNotExists"
         }
      }
   },
   "id":"/subscriptions/<azure-subscription-id>/providers/Microsoft.Authorization/policyAssignments/SecurityCenterBuiltIn",
   "type":"Microsoft.Authorization/policyAssignments",
   "name":"SecurityCenterBuiltIn",
   "location":"eastus"
}

02 Run account get-access-token command (Windows/macOS/Linux) using the configuration parameters defined within the JSON document at the previous step, to enable SQL encryption monitoring for the SQL database servers available in the selected Microsoft Azure cloud subscription:

az account get-access-token
	--query "{subscription:subscription,accessToken:accessToken}"
	--out tsv | xargs -L1 bash -c 'curl -X PUT -H "Authorization: Bearer $1" -H "Content-Type: application/json" https://management.azure.com/subscriptions/$0/providers/Microsoft.Authorization/policyAssignments/SecurityCenterBuiltIn?api-version=2018-05-01 -d@"enable-sql-encryption-monitoring.json"'

03 If successful, the command output should return the updated Azure Security Center policy, as shown in the following output example:

{
   "sku":{
  	"name":"A0",
  	"tier":"Free"
   },
   "properties":{
  	"displayName":"ASC Default (subscription: abcdabcd-1234-1234-1234-abcdabcdabcd)",
  	"parameters":{
     	"sqlEncryptionMonitoringEffect":{
        	"value":"AuditIfNotExists"
     	}
  	},

  	...

   },
   "id":"/subscriptions/abcdabcd-1234-1234-1234-abcdabcdabcd/providers/Microsoft.Authorization/policyAssignments/SecurityCenterBuiltIn",
   "type":"Microsoft.Authorization/policyAssignments",
   "name":"SecurityCenterBuiltIn",
   "location":"eastus"
}

04 If required, repeat steps no. 1 – 3 for other Microsoft Azure cloud subscription available.