SQL Server GDPR Information
Microsoft’s recommended approaches for achieving the data privacy goals of GDPR using SQL Server
COMPLYING WITH GDPR
Indentify what personal data is being managed and where it resides.
Govern how your customers’ personal data is used and accessed.
Establish security controls to prevent, detect & respond to vulnerabilities & data breaches.
Keep required documentation, manage data requests and provide breach notifications.
Discovering personal data in SQL Server
Microsoft SQL provides mechanisms to help identify personal data. Initially, it is possible to query metadata, e.g analysing column names via querying sys.columns, to identify column names which may contain personal data such as “Name”, “Birthdate”, “ID number”, etc. These identified columns should be added into a data map for further analysis & to identify data flows throughout an organization. For more advanced discovery capabilities, it’s possible to use Full-Text Search in Microsoft SQL to search for keywords located within freeform text. Sensitive data can also be tagged using Extended Properties to add sensitivity labels to relevant columns.
Reducing the attack service
It’s a recommended best practice to disable all features that aren’t in use to reduce the attack surface area. These can 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: XP_CMDSHELL, CLR, Filestream, Cross DB Ownership Chaining, OLE AUTOMATION, External Scripts, Ad-hoc Distributed Queries, & disabling the Trustworthy bit. Additional recommendations are to disable network protocols that aren’t in use, turn off the SQL Server browser service & 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 (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 fail over 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:
Talk to an Expert
We're here to bounce off, to challenge, to create and most importantly to help. All our customer relationships have started with a conversation, so if you have questions, challenges or just need a helpful pointer in the right direction then please get in touch.