Update November 2022: Introducing SQL Server 2022. Find out what’s new.
By Paul Kayley
SQL Server Consultant
10 December 2019
How can I review my SQL Servers?
GDPR regulations require increased protection against data breaches. It is important to identify areas where you could further restrict data access and understand the types of data your business is holding. Did you know there are free Microsoft SQL Server security tools to help you do this?
Within SQL Server Management Studio (SSMS), there is an in-built Vulnerability Assessment tool and a Data Discovery and Classification utility. Both these tools allow you to run a series of checks at the server and database level to review potential security risks and assess what confidential data could be breached.
Vulnerability Assessment
The Vulnerability Assessment is out of the box functionality and can be called from the SSMS Object Explorer as well as being called via Powershell commands. It runs a series of security checks into a colour-coded report that can be saved in Excel format. The checks are categorised as High-Medium-Low risk and provide the following detail:
- Description
- Impact
- Remediation
- Remediation Script
It should be noted that the lowest supported version is SQL Server 2012.
Data Discovery and Classification
The Data Classification discovery is carried out at database level and will scan a database schema to classify columns in a predefined list of categories (including)
- Contact Info
- Credentials
- Credit Card
- Banking
- Financial
- Name
- SSN
- Date of Birth
Each category will be rated in terms of its data sensitivity using the following options
- Public
- General
- Confidential
- Confidential GDPR
- Highly Confidential
- Highly Confidential GDPR
- [n/a]
The classifications are persisted to the database via the extended properties of the associated table. A report allows you to baseline your column classifications and therefore track future schema changes. SQL Server 2019 has also introduced a new DMV sys.sensitivity_classifications which allows querying this metadata.
Note: Data Classification is only compatible with SQL Server 2012 and above.
The following PDF details the steps required to perform these two assessments:
Ongoing SSMS Releases
Microsoft has made many enhancements to Management Studio since it was moved into a separate release programme and these are just two of them. Click here for the latest version of SSMS.
Further Information
Northdoor has an experienced team of SQL consultants who can assist with your data platform and help secure your business-critical systems. Contact our SQL team today.
