Table of Contents:


    Follow us

    GDPR in SQL Server and Azure SQL Database

    Piotr Kononow - Dataedo Team Piotr Kononow 2018-06-25 2018-10-09

    Some of you might be aware that on 25 May 2018 new important EU regulation enters into force - General Data Protection Regulation (GDPR). This is important not only for European organizations but to all that process personal data of European citizens.

    GDPR requirements

    GDPR aims at improving protection and management of personal data. To do it right, every organization should discover and document where personal data is stored in their databases.

    New feature in SQL Server Management Studio: Data Discovery and Classification

    Microsoft introduced a very interesting feature in its SSMS and SQL Server and Azure SQL databases - Data Discovery and Classification. It enables you to :

    • Discover sensitive data in your database with recommendations - SSMS scans for columns that contain potentially sensitive information,
    • Classify and label columns as holding sensitive data,
    • Report classification and sensitive data held in your databases.

    Discovering / Recommendations

    All functionality is available in new SQL Server Management Studio 17.5. It works only with SQL Server 2008 or newer and Azure databases. If you have an older version of SQL Server, please skip to "Do more" chapter where I talk about Dataedo tool.

    Connect to your server and right-click a database, choose Tasks and Classify Data....

    Data classification window will open with a list of suggested columns and their classification.

    There are two fields used for classification:

    Information Type:

    • Banking
    • Contact Info
    • Credentials
    • Credit Card
    • Date Of Birth
    • Financial
    • Health
    • Name
    • National ID
    • Networking
    • SSN
    • Other

    Sensitivity Label:

    • Public
    • General
    • Confidential
    • Confidential - GDPR
    • Highly Confidential
    • Highly Confidential - GDPR

    Labeling sensitive data

    How suggestions work?

    SSMS has hardcoded a list of keywords that are potentially used in columns holding personal data. Pretty simple and powerful.

    A test

    I created a sample table with a list of columns holding potentially sensitive data. Here's how SSMS labeled the information:

    I marked my reservations, some of those fields hold sensitive data as defined in GDPR and were not labeled.

    I also didn't find any field labeled as highly confidential, and those are ethnicity, religion, political views, trade union membership health and biometric information.

    The conclusion is that this is just a supporting tool, but you should review each column yourself.

    Where is this information stored?

    All information is stored in extended properties, key being:

    • sys_information_type_name for Information Type
    • sys_sensitivity_label_name for Sensitivity Label

    Reporting fields

    Auditing

    Do more

    This was it for the features in SSMS, SQL Server, and Azure and now I'd like to show you how you can share this information in convenient HTML, easily add more descriptive fields and do the same exercise for other engines. You can achieve this with an easy tool - Dataedo.

    HTML export of your schema with extended fields looks like this:

    • sys_information_type_name
    • sys_sensitivity_label_name

    Add more fields

    Do it for other engines

    Dataedo enables you to import the schema from other engines, like MySQL or Oracle. I'm not aware features similar to the ones in SSMS for those databases, so Dataedo will bridge the gap there. You can import multiple databases into one repository.

    Download Dataedo for free and document your databases for GDRP

    Learn more

    Download Dataedo now

    Share this article

    Comments (0)