Enable SQL Encryption Monitoring

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk level: Medium (should be achieved)
Rule ID: SecurityCenter-015

Ensure that "Monitor SQL Encryption" feature is enabled within your Microsoft Azure cloud account settings so that Azure Security Center can verify if your SQL database servers have encryption enabled.

This rule resolution is part of the Cloud Conformity Security & Compliance tool for Azure

Security

Azure Security Center strongly recommends that you enable Transparent Data Encryption (TDE) on your Azure SQL servers in order to safeguard your data in the event of a data breach. TDE protects your data and helps you meet regulatory compliance by encrypting your SQL databases, their associated backups, and transaction log files at rest, without having to change your application. With SQL encryption monitoring turned on, Azure Security Center can determine if encryption at rest is enabled for your Azure SQL databases. In case Transparent Data Encryption is not already enabled, the Security Center service will recommend you to do so.


Audit

To determine if SQL encryption monitoring is enabled within Azure Security Center, perform the following actions:

Using Azure Console

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.

Using Azure CLI and PowerShell

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"

If the command output returns "Disabled", as shown in the example above, the SQL encryption monitoring for Microsoft Azure SQL database servers is not enabled in selected Azure subscription.

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

Remediation / Resolution

To enable SQL encryption monitoring and recommendations for Azure SQL database servers, perform the following actions:

Using Azure Console

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.

Using Azure CLI and PowerShell

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.

References

Publication date May 31, 2019

Unlock the Remediation Steps


Gain free unlimited access
to our full Knowledge Base


Over 750 rules & best practices
for AWS and Azure

You are auditing:

Enable SQL Encryption Monitoring

Risk level: Medium