General Data Protection Regulation (GDPR) on SQL Server and Azure SQL Database

What is GDPR and why should I take note

General Data Protection Regulation (GDPR) is the process of protecting and enabling the privacy of individuals data while developing business processes. GDPR is a far-reaching global initiative governing how organizations manage and protect personal data while respecting individual choice.

GDPR is structured around 6 principals:

1. Requiring transparency on handling and use of personal data
2. Limiting personal data processing to specified, legitimate purpose
3. Limiting personal data collection and storage to the intended purpose
4. Enabling individuals to correct or request deletion of their personal data
5. Limiting the storage of personally identifiable data for only as long as necessary for its intended purpose
6. Ensuring personal data is protected using appropriate security practices

In this article, we will focus on the data protection part of GDPR specifically on SQL Server and Azure SQL Database.

There are 5 main steps in protecting a SQL Server and Azure SQL Database environment which revolve around database access and authorization

1. Azure SQL Database Firewall
2. Authentication
3. Authorization
4. Dynamic Data Masking
5. Row Level Security

Let us expound on each of these 5 steps

Azure SQL Database Firewall – limits access to a database by restricting access to only authorized connections. Azure SQL Database accessibility requires public IPs to be defined in Azure Portal thus ensuring only authorized connections have access.

Authentication

SQL Server provides several types of

• Windows Authentication – Allows users to connect to SQL environment using their Windows User Account
• SQL Server Authentication – allows SQL Server to manage authentication, its best practice to avoid using this authentication method if possible
• Active Directory – Universal with MFA (my new favorite) utilizes Azure AD multi-factor authentication capability. Currently available on Azure SQL Database only.
• Active Directory – Password - Connection using Azure AD identity, useful when on-premise domain is not federated with Azure thus not syncing credentials.
• Azure Directory – Integrated – Connection to Azure SQL database using Windows Credentials from a federated domain.

It’s important to ensure the method used in authentication aligns with your environment security requirements.

SQL Server Authorization

SQL Server has utilized role-based authorization for some time now, which provides granular control of data permission using role membership and object level permissions management

For a complete infographic of roles and permissions https://aka.ms/sql-permissions-poster.

Dynamic Data Masking (DDM) – SQL Server has a built-in capability that limits exposure of sensitive data by masking it when accessed by a non-privileged user or application. Data is masked on the fly thus protecting its integrity while remaining unchanged in the database. No application changes are required for DDM to be implemented

Masked Data

Row Level Security

Row- Level Security (RLS) simplifies the design and development of security in an application. RLS enables the developers to implement restrictions on data row access. For example in a reporting application that requires multiple users accessibility from different regions and department, it would require development for each region and or department in order to ensure only privileged users access data that they need.

With RLS, one application can cater for all users, their regions and departments by restricting rows that they can see

In conclusion, SQL Server and Azure SQL Database are well positioned when it comes to GDPR implementation and they allow you to keep your environment secure against unauthorized access of data. Hopefully, this article gives you insight on some of the possibilities with GDPR and SQL environment.