Monitor SQL Encryption

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.

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"

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

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.

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.