|   Trend Micro Cloud One™
Open menu

Enable SQL Auditing 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-014

Ensure that "Monitor SQL Auditing" feature is enabled within your Microsoft Azure account configuration settings so that Azure Security Center can determine if your SQL database servers have security auditing and threat detection enabled.

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

Security

Microsoft Azure advise you to enable auditing and threat detection for all databases on your Azure 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 "Monitor SQL Auditing" feature is enabled, the service can determine if security auditing is enabled for the SQL database servers available in your Azure cloud account. If SQL auditing is not enabled, the Azure Security Center recommends turning it on for compliance, advanced threat detection, and investigation purposes.

Audit

To determine if SQL auditing 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 unaudited SQL servers in Azure Security Center configuration setting status. If the setting is set to Disabled, the SQL auditing 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 Auditing" 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.sqlAuditingMonitoringEffect.value'

02 The command output should return the SQL auditing monitoring configuration status:

"Disabled"

If the command output returns "Disabled", as shown in the example above, the SQL auditing and threat detection monitoring for Microsoft Azure SQL 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 auditing and threat detection monitoring for Azure SQL 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 auditing dropdown list to enable SQL auditing monitoring for all Microsoft Azure SQL servers available in the current Azure 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 Auditing" 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 sqlAuditingMonitoringEffect configuration parameter is enabled. Save the following content to a JSON file named enable-sql-auditing-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":{
         "sqlAuditingMonitoringEffect":{
            "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 in the enable-sql-auditing-monitoring.json document at the previous step, to enable SQL auditing and threat detection monitoring for the SQL 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-auditing-monitoring.json"'

03 If successful, the command output should return the updated Azure Security Center policy:

{
   "sku":{
  	"name":"A0",
  	"tier":"Free"
   },
   "properties":{
  	"displayName":"ASC Default (subscription: abcdabcd-1234-1234-1234-abcdabcdabcd)",
  	"parameters":{
     	"sqlAuditingMonitoringEffect":{
        	"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 Auditing Monitoring

Risk level: Medium