Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Monitor SQL Auditing

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-014

Ensure that monitoring for unaudited Microsoft SQL servers is enabled within your Azure account so that Microsoft Defender for Cloud can determine if your SQL database servers have security auditing and threat detection enabled.

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

Security

Microsoft recommends enabling auditing and threat detection for all the databases created on your SQL servers. Security auditing and threat detection can help you maintain regulatory compliance, understand database activity, and find any anomalies that could indicate potential vulnerabilities or suspected security violations. When the monitoring feature is enabled, Microsoft Defender for Cloud can determine if the security auditing is enabled for the SQL database servers provisioned in your Azure cloud account. If the SQL auditing is not enabled, the Microsoft Defender for Cloud recommends turning it on for compliance, advanced threat detection, and investigation purposes.

Audit

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

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: Auditing should be enabled on advanced data security settings on SQL Server. If the Auditing should be enabled on advanced data security settings on SQL Server parameter is set to Disabled, the monitoring of unaudited Microsoft Azure SQL servers is not enabled for the selected subscription.

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

Using Azure CLI

01 Run account get-access-token command (Windows/macOS/Linux) using custom query filters to determine if the monitoring of unaudited SQL servers is enabled for the current Azure subscription by checking the sqlServerAuditingMonitoringEffect 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.sqlServerAuditingMonitoringEffect.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 unaudited Microsoft Azure SQL servers is not enabled for the current subscription.

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

Remediation / Resolution

To enable monitoring for unaudited Microsoft Azure SQL servers, perform the following operations:

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 Auditing should be enabled on advanced data security settings on SQL Server parameter dropdown list to enable the monitoring of unaudited Microsoft SQL servers within the selected Azure subscription.

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

01 Define the configuration parameters for the account get-access-token command, where the sqlServerAuditingMonitoringEffect parameter is enabled to turn on the monitoring feature. Save the configuration document to a JSON file named enable-unaudited-sql-server-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":{
        "sqlServerAuditingMonitoringEffect":{
           "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-unaudited-sql-server-monitoring.json file), to enable the monitoring of unaudited Microsoft SQL servers within the selected Azure 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-unaudited-sql-server-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": {
      "sqlServerAuditingMonitoringEffect": {
        "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

Related SecurityCenter rules