Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Monitor SQL Encryption

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

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

Ensure that the SQL encryption monitoring feature is enabled within the Microsoft Defender for Cloud settings so that Microsoft Azure can verify if your SQL database servers have encryption enabled.

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

Security

Microsoft strongly recommends that you enable Transparent Data Encryption (TDE) on your Microsoft 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, Microsoft Defender for Cloud can determine if the encryption at rest is enabled for your SQL database servers. In case Transparent Data Encryption is not already enabled, the Microsoft Defender for Cloud service will recommend you to do so.


Audit

To determine if the monitoring of unencrypted SQL database servers is enabled within the Microsoft Defender for Cloud security policy, perform the following actions:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to Microsoft Defender for Cloud blade at https://portal.azure.com/#blade/Microsoft_Azure_Security/SecurityMenuBlade/0.

03 In the main navigation panel, under Management, choose Environment settings.

04 Click on the name (link) of the Azure subscription that you want to examine.

05 In the navigation panel, under Policy settings, choose Security policy.

06 In the Default initiative section, click on the name of the default initiative enabled for the selected subscription (i.e. ASC Default (subscription: <subscription-id>)).

07 Choose the Parameters tab, uncheck Only show parameters that need input or review, and search for the following parameter: Transparent Data Encryption on SQL databases should be enabled. If the Transparent Data Encryption on SQL databases should be enabled parameter is set to Disabled, the monitoring of unencrypted Microsoft SQL database servers is not enabled in the selected subscription.

08 Repeat steps no. 4 – 7 for each Microsoft Azure subscription created within your Azure account.

Using Azure CLI and PowerShell

01 Run account get-access-token command (Windows/macOS/Linux) using custom query filters to determine if the SQL database encryption monitoring is enabled within the current Azure subscription by checking the sqlDbEncryptionMonitoringEffect configuration parameter value:

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.sqlDbEncryptionMonitoringEffect.value'

02 The command output should return the requested parameter value:

"Disabled"

If the account get-access-token command output returns "Disabled", as shown in the output example above, the monitoring of unencrypted Microsoft SQL database servers is not enabled in the selected subscription.

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

Remediation / Resolution

To enable Transparent Data Encryption monitoring and recommendations for your Microsoft SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to Microsoft Defender for Cloud blade at https://portal.azure.com/#blade/Microsoft_Azure_Security/SecurityMenuBlade/0.

03 In the main navigation panel, under Management, choose Environment settings.

04 Click on the name (link) of the Azure subscription that you want to access.

05 In the navigation panel, under Policy settings, choose Security policy.

06 In the Default initiative section, click on the name of the default initiative enabled for the selected subscription (i.e. ASC Default (subscription: <subscription-id>)).

07 Choose the Parameters tab and uncheck the Only show parameters that need input or review checkbox to list all the initiative parameters.

08 Select AuditIfNotExists from the Transparent Data Encryption on SQL databases should be enabled parameter dropdown list to enable Transparent Data Encryption monitoring and recommendations for Microsoft SQL database servers.

09 Select Review + save to review the configuration changes, then choose Save to apply the new changes. If the operation is successful, the following confirmation message should be displayed: "Updating policy assignment succeeded".

10 Repeat steps no. 4 – 9 for each Microsoft Azure subscription available within your Azure account.

Using Azure CLI and PowerShell

01 Define the configuration parameters for the account get-access-token command, where the sqlDbEncryptionMonitoringEffect parameter is enabled to turn on the monitoring feature. Save the configuration document to a JSON file named enable-tde-monitoring.json and replace the highlighted details, i.e. <azure-subscription-id> and <policy-definition-id>, with your own Azure account subscription details:

{
  "properties":{
     "displayName":"ASC Default (subscription: <azure-subscription-id>)",
     "policyDefinitionId":"/providers/Microsoft.Authorization/policySetDefinitions/<policy-definition-id>",
     "scope":"/subscriptions/<azure-subscription-id>",
     "parameters":{
        "sqlDbEncryptionMonitoringEffect":{
           "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 document defined at the previous step (i.e. enable-tde-monitoring.json file), to enable Transparent Data Encryption monitoring and recommendations for Microsoft SQL database servers:

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-tde-monitoring.json"'

03 The command output should return information about the modified configuration parameter:

{
  "sku": {
    "name": "A0",
    "tier": "Free"
  },
  "properties": {
    "displayName": "ASC Default (subscription: abcdabcd-1234-1234-1234-abcdabcdabcd)",
    "policyDefinitionId": "/providers/Microsoft.Authorization/policySetDefinitions/1234abcd-1234-1234-1234-abcd1234abcd",
    "scope": "/subscriptions/abcdabcd-1234-1234-1234-abcdabcdabcd",
    "parameters": {
      "sqlDbEncryptionMonitoringEffect": {
        "value": "AuditIfNotExists"
      }
    },
    "metadata": {
      "createdBy": "abcdabcd-1234-1234-1234-abcdabcdabcd",
      "createdOn": "2019-05-17T15:38:40.3473931Z",
      "updatedBy": "1234abcd-1234-1234-1234-abcd1234abcd",
      "updatedOn": "2022-02-01T21:22:40.7422203Z"
    }
  },
  "id": "/subscriptions/abcdabcd-1234-1234-1234-abcdabcdabcd/providers/Microsoft.Authorization/policyAssignments/SecurityCenterBuiltIn",
  "type": "Microsoft.Authorization/policyAssignments",
  "name": "SecurityCenterBuiltIn",
  "location": "eastus"
}

04 Repeat steps no. 1 – 3 for each Microsoft Azure subscription available in your Azure cloud account.

References

Publication date May 31, 2019