Azure SQL provides DBA’s with an easy and efficient means of standing up relational database services for their cloud and enterprise applications. As with any database platform, security remains a top concern and has not been overlooked by Microsoft with the variety of security features available in Azure SQL, including those offered through the Advanced Data Security package.
The Advanced Data Security package for Azure SQL provides administrators with a single go-to location for discovering and classifying data, assessing and addressing potential database vulnerabilities, and visibility into anomalous and potentially malicious activity that is taking place. Once ADS is enabled for your SQL Database server or managed instance, you will be provided with three distinct sets of functionality:
- Data Discovery & Classification – Discovery, classify, label, and protect the data within your Azure SQL databases
- Vulnerability Assessment – Discover and track potential vulnerabilities and security misconfigurations, with actionable steps that can be taken to fortify your database security posture
- Advanced Threat Protection – Continuously monitor your database for suspicious activity, with real-time alerts on potential vulnerabilities
This is the first in a three-part blog series. In this blog post, I will walk you through configuring ADS along with a deeper dive into the Data Discovery & Classification functionality. Future blog posts will cover the Vulnerability Assessment and Advanced Threat Protection pieces of the Advanced Data Security offering.
Getting Started with Advanced Data Security
To enable ADS at the database level, navigate to Settings > Advanced Data Security for your SQL database and click ‘Enable.
Alternatively, this can also be configured and managed at the server/managed instance level, by navigating to Settings > Advanced Data Security for your server and switching the ‘Advanced Data Security’ setting from ‘Off’ to ‘On’.
The settings configured on this page will apply to all contained databases but can be overridden for any particular database in the case that there are specific requirements on the database that differ from the settings configured at the server or instance level.
Data Discovery and Classification
The Data Discovery and Classification features provide the ability to discover, classify, label, and protect the sensitive data contained within your Azure SQL databases.
- Discovery & Classification – The classification engine performs scans against your databases and identifies columns that contain sensitive data, providing a streamlined approach to review and apply the appropriate recommendations as provided by the Azure portal
- Labeling – Labels can be applied to columns using the classification suggested through the SQL engine and can be used for advanced sensitivity-based auditing and protection
After you enable ADS on your Azure SQL databases, you can begin your classification journey by clicking on the Data Discovery & Classification card under the Security>Advanced Data Security page.
The Overview tab will display information related to the current classification state of your database, including a summary of classified columns, tables containing sensitive data, and information types.
Classification Recommendations
Navigate to the Classification tab which will provide a list of recommended classifications based on the results of the classification engine scan to help you get started with classifying your data.
You can review the list of recommendations and choose to accept or dismiss based on your review. Choosing to ‘Accept selected recommendations’ will apply the information type and sensitivity label to the specified column. When you choose to ‘Dismiss Selected recommendations’ it will remove that recommendation for the specific column. This can be done at mass by choosing to ‘Select all’, or individually by selecting the checkbox to the left of the row.
Manually Apply Custom Classifications
In addition to labeling columns based on the recommendations of the classification engine, you can also choose to manually classify columns by clicking ‘Add classification’ at the top of the window.
This will open the ‘Add Classification’ pane which will allow you to specify the schema name, table name, column name, as well as the information type and sensitivity label you’d like to apply.
Once complete, click ‘Add classification’ to add the custom classification label at the bottom of the Add Classification pane. Make sure to click Save in the Data Discovery & Classification page to save all of the information types and sensitivity labels that have been applied to your columns.
You can export a report in Excel format by clicking the Export option at the top of the Data Discovery & Classification page when on the Overview tab.
Automated Application of Classifications
In addition to manually applying these labels, they can also be applied through T-SQL, the published REST API, or Azure PowerShell.
Add Sensitivity Labels via T-SQL
ADD SENSITIVITY CLASSIFICATION TO
dbo.accountinfo.username, dbo.accountinfo.pwd
WITH ( LABEL='Confidential', INFORMATION_TYPE='Credentials' )Drop Sensitivity Labels via T-SQL
DROP SENSITIVITY CLASSIFICATION FROM
dbo.accountinfo.usernameYou can view all of the sensitivity labels applied by querying the sys.sensitivity_classifications table. Reference articles on how to manage these labels via the published REST API or Azure PowerShell are listed below
- Manage Sensitivity Labels via published REST API
- Manage Sensitivity Labels via Azure PowerShell for Azure SQL Database
- Manage Sensitivity Labels via Azure PowerShell for managed instance
Customize Information Types and Sensitivity Labels
While Azure SQL provides a built-in set of information types and sensitivity labels, end users are able to define their own set as well as rank classification constructs specifically for their environment. This is done within the Azure Security Center for the entire Azure tenant, and will not solely apply to Azure SQL. Once the tenant-wide policy has been defined, the custom types and labels can be leveraged through the Data Discovery & Classification feature for the individual Azure SQL databases. Refer to this Microsoft article on the steps to create the custom information types and sensitivity labels.
Next Steps
Now that classification labels have been applied to the sensitive information stored within the Azure SQL database, they can be used to
- Provide visibility into where sensitive data exists within your Azure SQL environment, adhering to some of the most common compliance standards such as GDPR
- Track access to sensitive within the database in conjunction with Azure SQL Database Auditing.
The Discovery & Classification feature is a great first step in discovering and tracking sensitive information stored within your Azure SQL Instances. Because the discovery feature relies heavily upon the column names though, it could
- Lead to a manual effort to dismiss a large number of false positives. In my classification journey, I found several instances where the recommended classifications were incorrectly categorizing columns as credentials just because the column name contained words like ‘Account’.
- Fail to identify sensitive information in the actual column data, especially in the case of free-text fields. I had several tables that contained information such as credit card numbers and social security numbers, but none were flagged automatically because the column name didn’t indicate that this is the type of data that was being stored. The recommendations do not seem to take into consideration the actual cell values
However, coupling this functionality with a full-fledged sensitive discovery product can have huge benefits by providing more in-depth visibility into the actual table data, and allowing administrators to accurately label and protect their most sensitive information.
In my next blog post, we will take a deep dive into the Vulnerability Assessment feature which is part of the Advanced Data Security portfolio.
Farrah Gamboa is a Director of Technical Product Management at Stealthbits – now part of Netwrix. She is responsible for building and delivering on the roadmap of Stealthbits products and solutions.
Since joining Stealthbits in 2012, Farrah has held multiple technical roles, including Scrum Master and Quality Assurance Manager. Farrah holds a Bachelor of Science degree in Industrial Engineering from Rutgers University
Related Posts
- Sensitive Data Discovery for Compliance
- Using The Azure Information Protection (AIP) Scanner to Discover Sensitive Data
- How to Protect Office 365 by Classifying Your Data with Microsoft’s AIP Labels
- 2019 Verizon DBIR Key Findings
- 5 Cybersecurity Trends for 2019
- EU GDPR: Paving the Way for New Privacy Laws?
- Announcing Stealthbits Activity Monitor 3.0 & StealthINTERCEPT 5.1
- Key Take Aways from the Ponemon 2018 Cost of Insider Threats Report
- Where Real Organizations Are with EU GDPR 10 Days from Launch
- Stealthbits Cyber Kill Chain Attack Catalog: Active Directory Attacks and More
I have a database with 14000+ columns and Azure is only giving me 100 columns of recommendation at a time … it is incredibly slow and tedious to go through this 100 columns at a time.
Is there a way to tell Azure to automatically classify ALL of the columns according to recommendation?