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

Enable Advanced Data Security for SQL Servers

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: Sql-006

Ensure that Advanced Data Security feature is enabled within your Microsoft Azure SQL server configuration settings to provide a set of advanced SQL security capabilities for your SQL database servers, such Data Discovery and Classification – used for discovering and classifying sensitive data, Vulnerability Assessment – useful for finding and mitigating potential database vulnerabilities, and Advanced Threat Protection – a service used for detecting abnormal activities that could indicate a threat to your SQL databases.

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

Security

The Advanced Data Security (ADS) suite of security services represents an advanced layer of database security, which enables you to recognize and respond to potential threats as they occur by providing security alerts on abnormal activity within your SQL servers. Once ADS is enabled and configured, you can receive notification alerts upon suspicious database activities, potential vulnerabilities, SQL injection attacks, and anomalous database access patterns. These threat detection alerts provide details of suspicious activity and generate recommendations on how to investigate and mitigate the threats found. Additionally, Azure SQL Advanced Data Security includes functionality for discovering and classifying sensitive and protected data.

Note: Advanced Data Security is a paid service. Cloud Conformity recommends you to enable this feature at least on business-critical Azure SQL servers.

Audit

To determine if Advanced Data Security (ADS) is enabled for your Microsoft Azure SQL servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers provisioned in your Azure account.

04 Click on the name of the SQL server that you want to examine.

05 In the navigation panel, under Security, select Advanced Data Security to access the ADS configuration settings for the selected database server.

06 On the ADS configuration page, check the ADVANCED DATA SECURITY setting. If the ADS configuration setting is set to OFF, the Advanced Data Security feature is not enabled for the selected Microsoft Azure SQL database server.

07 Repeat steps no. 4 – 6 for each SQL database server provisioned in the selected subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run Get-AzSqlServer PowerShell cmdlet using custom query filters to list the names of all SQL database servers and the names of their associated resource groups, available in the current Azure subscription: 

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

02 The command output should return the requested SQL database server information: 

ServerName             ResourceGroupName
----------             -----------------
cc-production-server   cloud-shell-storage-westeurope
cc-project5-server     cloud-shell-storage-westeurope

03 Run Get-AzSqlServerAdvancedDataSecurityPolicy PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to get the Advanced Data Security (ADS) configuration status for the selected database server: 

Get-AzSqlServerAdvancedDataSecurityPolicy -ServerName "cc-production-server" -ResourceGroupName "cloud-shell-storage-westeurope" | Select-Object IsEnabled

04 The command output should return the requested feature status: 

IsEnabled
---------
False

If Get-AzSqlServerAdvancedDataSecurityPolicy cmdlet output returns False for the IsEnabled configuration attribute, as shown in the example above, the Advanced Data Security feature is not enabled for the selected Microsoft Azure SQL database server.

05 Repeat step no. 3 and 4 for each SQL database server provisioned in the selected subscription.

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

Remediation / Resolution

To enable Advanced Data Security (ADS) feature for your Microsoft Azure SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to reconfigure.

05 In the navigation panel, under Security, select Advanced Data Security to access the ADS configuration settings for the selected database server.

06 On the ADS configuration page, select ON for the ADVANCED DATA SECURITY setting to enable Advanced Data Security (ADS) for the selected SQL server.

07 The ADS security package includes Vulnerability Assessment and Advanced Threat Protection services. To configure these security services, perform the following:

  1. Under VULNERABILITY ASSESSMENT SETTINGS, select the right Azure subscription and click Storage account to configure an Azure Storage account where the scan results for all the databases created on the server will be stored. On the Choose storage account panel, click Create new to set up a new storage account or just select one of the storage accounts already available in the selected subscription to return to the ADS configuration page. (Optional) You can also enable Periodic recurring scans to scan your database(s) for vulnerabilities every 7 days, and use the Send scan reports to box to define a separated list of emails to which your scan reports will be sent.
  2. Under ADVANCED THREAT PROTECTION SETTINGS, in the Send alerts to box, specify a semicolon-separated list of emails to which alerts will be sent upon detection of abnormal activities, then click Advanced Threat Protection types and select All to send email alerts for all types of vulnerabilities detected by the Advanced Threat Protection service.

08 Click Save to apply the Advanced Data Security configuration changes.

09 Repeat steps no. 4 – 8 for each SQL database server available within the selected subscription.

10 Repeat steps no. 3 – 9 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run Enable-AzSqlServerAdvancedDataSecurity PowerShell cmdlet using the name of the SQL server that you want to reconfigure and the name of the associated resource group as identifier parameters (see Audit section part I to identify the right Azure cloud resource) to enable Advanced Data Security (ADS) feature for the selected Microsoft Azure SQL database server. Once the Enable-AzSqlServerAdvancedDataSecurity command request is sent, a new Azure Storage account will automatically be created for saving vulnerability assessments. If a storage account was previously created for this purpose, it will be used instead of creating a new one: 

Enable-AzSqlServerAdvancedDataSecurity -ServerName "cc-production-server" -ResourceGroupName "cloud-shell-storage-westeurope"

02 The command output should return the metadata for the reconfigured SQL database server: 

ServerName           ResourceGroupName              IsEnabled
----------           -----------------              ---------
cc-production-server cloud-shell-storage-westeurope True

03 (Optional) To configure the Advanced Threat Protection service once the Advanced Data Security (ADS) feature is enabled, run Update-AzSqlDatabaseAdvancedThreatProtectionSettings PowerShell cmdlet for each database created on the selected Azure SQL server using the -NotificationRecipientsEmails parameter to provide the email address(es) to which the service sends notification alerts (the command does not produce an output): 

Update-AzSqlDatabaseAdvancedThreatProtectionSettings -ServerName "cc-production-server" -ResourceGroupName "cloud-shell-storage-westeurope" -DatabaseName "cc-sql-database" -StorageAccountName "abcdabcdabcdabcdabcd" -NotificationRecipientsEmails "secops@cloudconformity.com"

04 Repeat steps no. 1 – 3 for each SQL database server available in the selected subscription.

05 Repeat steps no. 1 – 4 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Jun 12, 2019

Related Sql rules