SQL Server GDPR Information
This guide shares some of Microsoft’s
recommended approaches using SQL Server
for achieving the data privacy goals of GDPR.
The journey to complying with the GDPR
Indentify what personal data is being managed and where it resides.
Govern how personal data is used and accessed.
Establish security controls to prevent, detect and respond to vulnerabilities and data data breaches.
Discovering personal data in SQL Server
Microsoft SQL provides mechanisms to help identify personal data. Initially, it is possible to query metadata, such as analysing all column names via querying sys.columns, to identify column names which potentially contain personal data such as “Name”, “Birthdate”, “ID number”, etc. These identified columns should be added into a data map for further analysis and to identify data flows throughout an organization. For more advanced discovery capabilities, it is possible to use Full-Text Search in Microsoft SQL to search for keywords located within freeform text. Additionally, sensitive data can be tagged using Extended Properties to add sensitivity labels to relevant columns.
Reducing the attack surface
Generally, it is a recommended best practice to disable all features that are not in use to reduce the attack surface area. These can generally be disabled via T-SQL queries or via a management console like SQL Server Management Studio (SSMS). Example features that should be checked and, if possible, disabled include (but are not limited to): XP_CMDSHELL, CLR, Filestream, Cross DB Ownership Chaining, OLE AUTOMATION, External Scripts, Ad-hoc Distributed Queries, and disabling the Trustworthy bit. Additional recommendations are to disable network protocols that are not in use, turn off the SQL Server browser service and to uninstall sample databases.
What is personal data?
Personal data in scope of the regulation can include, but is not limited to, the following:
- Identification number
- Email address
- Online user identifier
- Social media posts
- Physical, physiological, or genetic information
- Medical information
- Bank details
- IP address
- Cultural identity
SQL Server GDPR Considerations
SQL Server Authentication helps ensure that only authorised users with valid credentials can access the database server.
SQL Server supports two authentication modes, Windows authentication mode and mixed mode. Windows authentication is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Windows user and group accounts are trusted to log in to SQL Server, based on authentication credentials directly within Windows. Mixed mode supports authentication both by Windows and by SQL Server, using user names and passwords.
Dynamic Data Masking (DDM) limits sensitive data exposure by masking the data to non-privileged users or applications. DDM allows the database administrator to select a particular table-column that contains sensitive data, add a mask to it and designate which DB users are privileged and should have access to the real data. Once configured, any query on that table/ column will contain masked results, except for queries run by privileged users.
Row Level Security
Row-Level Security (RLS) restricts access according to specific user entitlements. Use RLS to control access to rows in a database table based on the characteristics of the user executing a query. In this way, only database users that have a specific need to access data in a database row will be granted that access.
Transparent Data Encryption
Transparent Data Encryption (TDE) addresses the scenario of protecting the data at the physical storage layer. TDE performs real-time encryption and decryption of the database, associated backups, and transaction log files without requiring changes to the application.
Transport Layer Security
Microsoft SQL encryption technologies can be applied at different levels. It is a best practice to always use connections secured with Transport Layer Security (TLS). This ensures that data is encrypted in transit to and from the database, and reduces susceptibility to “man-in-the-middle” attacks.
Always Encrypted allows customers to encrypt sensitive data inside client applications and never reveal the encryption keys to the database engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).
Always On – (Business Continuity)
In SQL Server, Always On Availability Groups can be used to maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
SQL Server Audit
SQL Server Audit enables the customer to understand ongoing database activities, and analyze and investigate historical activity to identify potential threats or suspected abuse and security violations. SQL Server Audit enables the creation of server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.
Download the complete Microsoft and the GDPR guide here:
Time until GDPR becomes enforceable