|   Trend Micro Cloud One™
Open menu

Enable SQL Encryption Monitoring

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
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 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Enable SQL Encryption Monitoring

Risk level: Medium