Configuring SQL Data Discovery and Classification in SQL Server On-Premises Using SSMS

In today’s data-driven world, securing sensitive information is paramount for organizations. SQL Data Discovery and Classification in SQL Server provides a powerful feature to identify, label, and classify sensitive data. This functionality helps enhance your data security and compliance posture by enabling organizations to better understand their data landscape. In this blog, we’ll guide you through configuring SQL Data Discovery and Classification in an on-premises SQL Server instance using SQL Server Management Studio (SSMS).

Prerequisites

Before proceeding, ensure the following:

• SQL Server Version: SQL Server 2012 or later, though the feature is more robust in SQL Server 2016 SP1 and above.
• SSMS Version: SQL Server Management Studio v17.5 or higher.
• Permissions: Access to the database with ALTER permissions.

Step-by-Step Guide

Step 1: Connect to Your SQL Server Instance

1. Open SQL Server Management Studio (SSMS).
2. Connect to your on-premises SQL Server instance.
3. Expand the Databases node and select the database you want to work with.

Step 2: Navigate to Data Discovery and Classification

1. Right-click the target database and choose Tasks > Data Discovery and Classification > Classify Data.

 2. This will open the Data Classification pane, where you can manage sensitivity labels and classifications.

Step-3: Discover and Review Recommendations

1. The classification engine scans your database for columns containing potentially sensitive data and provides a list of recommended classifications based on column names.
2. To view the list of recommendations, click the recommendations notification box at the top or the recommendations panel at the bottom of the window.

3. Review the recommendations:

• To accept a recommendation, check the box in the left column of the relevant row. You can also accept all recommendations by selecting the checkbox in the table header.
• Modify the recommended Information Type and Sensitivity Label using the drop-down menus, if necessary.

      4. To apply the selected recommendations, select the Save selected recommendations button.

Step 4: Manually Classify Data

To display the classified columns, select appropriate schema and corresponding table from the drop-down, then select Load Columns.

You can also manually classify columns as an alternative, or in addition, to the recommendation-based classification:

Select Add classification in the top menu of the window.

In the context window that opens, enter the column name that you want to classify, information type and the sensitivity label. Schema and table are selected based on the entries in the main page.

If you want to add classification for all the unclassified columns for a specific table in a single attempt, then select All Unclassified in the Column drop down of Add Classification page.

To complete your classification and persistently label (tag) the database columns with the new classification metadata, select the Save button on the top menu of the window.

Step 5: Generate a Classification Report

To generate a report with a full summary of the database classification state, select View Report in the top menu of the window. (You can also generate a report using SSMS. Select the database where you would like to generate the report, and choose Tasks > Data Discovery and Classification > Generate Report...)

Benefits of SQL Data Discovery and Classification

1. Enhanced Data Security: Identify sensitive data and apply appropriate security measures like encryption and access controls.
2. Compliance: Simplify adherence to regulatory requirements such as GDPR, HIPAA, and CCPA.
3. Audit-Ready: Generate reports to demonstrate compliance and track classification changes.
4. Integration: Seamlessly integrates with Azure Information Protection and Microsoft Purview Information Protection for unified data governance.

Best Practices

• Periodic Reviews: Regularly review and update classifications to reflect schema or data changes.
• Automate: Use T-SQL scripts to automate classifications for large databases.
• Restrict Access: Limit who can view or modify classifications to ensure data security.
• Enable Auditing: Track changes to classified data using SQL Server Audit.

Microsoft Information Protection (MIP)

Microsoft Information Protection (MIP) offers advanced data protection features that integrate with SQL Server to:

• Automatically label and classify data based on predefined policies.
• Provide consistent protection across environments, including on-premises and cloud.
• Enhance visibility into sensitive data locations and usage.

Conclusion

SQL Data Discovery and Classification is a powerful feature that simplifies the management of sensitive data in SQL Server. By leveraging this tool, organizations can strengthen their security posture, ensure regulatory compliance, and gain deeper insights into their data. Explore this feature in SSMS to take control of your sensitive data today.