Category: SQL Server

by Fran Lens 2019-01-24

🔒 Protecting data at Rest, in Use and in Transit – GDPR Series (4/5)

In the previous entry of the series we learned to manage the access and use of data, but to be completely safe, it is not enough to just secure the data in use, we also have to protect it at rest and in transit.

Why is this important for the GDPR?

This relates to the GDPR obligation to take into account “risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, [or] unauthorized disclosure of” that data. In this case, the protection is at the level of the physical device—and prevents the risk of compromising the storage itself, for example via copying the physical data out to another server.

GDPR Article 32(2)—“Security of processing.”

Before starting with the technical part, I will explain with a quick example each of the three states in which the data can be found:

• At rest, on a physical layer, in our case it could be the .mdf, .ldf file or the backups that are made from the database.
• In use, at the logical layer, that is, the data that we find in the tables of a database.
• In transit, when our data travels in the form of packets over the network.

Once we have clear the three states, we will see how SQL Server can help us protect ourselves:

• Use encrypted connections to prevent data traveling (in transit) without encrypting with Connection Encryption.
• Secure personal data through encryption in the physical layer of storage (at rest) using Transparent Data Encryption.
• Prevent unauthorized or highly privileged users from accessing data in transit, at rest and in use with the Always Encrypted feature.
• Maximize data availability and avoid downtimes with Always On Availability Groups.
• In Azure SQL Database and Azure SQL Data Warehouse detects anomalous activities and potential security risks with SQL Database Threat Detection.

Making an entry that covers all these features would be a bit long, let’s focus on two of them, which can also be deployed very quickly, Connection Encryption and Transparent Data Encryption.

Connection Encryption

In this example I’m going to show you how easy it is to activate the encryption of connections so that our data stops traveling in plain text, since by default in SQL Server only users and passwords travel encrypted.

Before this, we can do a quick test with Wireshark to understand how “someone with bad intentions” would see our data. For this we need to know the IP of our SQL Server, and the port used by SQL Server, by default is 1433.

We will open Wireshark and we will create a filter with the following statement to see all the data that is going through SQL Server:

ip.addr == YOUR_IP || tcp.port== YOUR_SQL_PORT

As soon as we activate it, we will see how it starts to obtain data:

Now we are going to execute a query against our SQL Server, in our case we are going to use the table[Person].[Person] de AdventureWorks2016.

SELECT TOP 10 [FirstName]
	,[LastName]
FROM [AdventureWorks2016].[Person].[Person]

And the result that shows us is the following:

We move to Wireshark and search among the captured packets, we can filter by the packets that in the Protocol column the TCP text appears, we will also notice that the variable Len (Length) has a slightly high value.

And once we have located the package, we will only have to see its contents to see how all the results of our query are unencrypted:

Now that I’ve scared you, it’s time to see how to solve it.

On our SQL Server we open the SQL Server Configuration Manager application, and go to the Properties of Protocols for MSSQLSERVER.

And we change the Force Encryption option to Yes.

Now we restart the SQL Server service and our packages will already travel encrypted.

To prove this, we execute the query on [Person].[Person].

And this is the result that will appear in Wireshark:

All well encrypted and unless you are a descendant of Alan Turing you will not be able to decrypt.

So the recommendation that I can give you is:

ALWAYS ACTIVATE THE ENCRYPTION OF CONNECTIONS (Testing previously that your applications support it).

Transparent Data Encryption

We have seen how fast it is to encrypt the connections and the data in transit, and now we are going to see the quickest and easiest way we have to encrypt the data at rest, which is none other than using TDE.

The first thing we need to know is that Transparent Data Encryption is only available with SQL Server Enterprise version, so if you have a Standard version you will not be able to use this functionality.

Before enabling Transparent Data Encryption we have to know its dependencies and the hierarchy that must be fulfilled to enable this encryption:

1. The first layer that we have at OS level is the Windows OS Data Protection API.
2. The second layer is the Service Master Key that is created with the installation of SQL Server, which is encrypted by the Windows OS Data Protection API.
3. The third layer is the Master Database Key created in the master database that is encrypted by the Service Master Key.
4. The fourth layer is the Certificate that is created in master using the Master Database Key.
5. The fifth and last layer is the Database Encryption Key at the user database level and uses the Certificate.

Of the first and second layer we do not have to worry, and of the next three I’ll explain now how to create it and what things to keep in mind.

We start with the Master Database Key, which has to be created in the master database, we will choose a strong password and of course SAVE IT IN A SAFE PLACE.

-- Create Master Database Key
USE [master]
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='GDPRAlesonITC2019'
GO

Once we have created (and saved) the Master Database Key, we proceed to create the Certificate in master, the name of the certificate is free, in my case I like to put the name of the database that is going to be encrypted.

-- Create Certificate
USE [master]
GO
CREATE CERTIFICATE TDE_AdventureWorks2016_Cert
WITH 
SUBJECT='Database_AdventureWorks2016_Encryption';
GO

Now we change to the database that we want to encrypt to create the Database Encryption Key, as you see we will use the Certificate that we have just created.

-- Create Database Encryption Key
USE [AdventureWorks2016]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_AdventureWorks2016_Cert;
GO

At the moment of creating it, it will show us an Alert message.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

It is very important that you make a backup of the certificate, because if we need to restore the database in another environment we will need to previously restore the certificate and recreate the Master Database Key (using the same password). So, we make the certificate backup:

-- Backup Certificate
USE [master]
GO
BACKUP CERTIFICATE TDE_AdventureWorks2016_Cert
TO FILE = 'C:\Backup\TDE_AdventureWorks2016_Cert'
WITH PRIVATE KEY (file='C:\Backup\TDE_AdventureWorks2016_CertKey.pvk',
ENCRYPTION BY PASSWORD='GDPRAlesonITC2019')

Finally, we only have to enable encryption in the database, in our case AdventureWorks2016:

-- Now Enable Encryption in AdventureWorks2016
ALTER DATABASE [AdventureWorks2016]
SET ENCRYPTION ON;
GO

To verify that the entire process has been done correctly, we can run the following query to see all the encrypted databases:

-- Verify Encryption and some details
SELECT D.name, K.*
FROM sys.dm_database_encryption_keys K
INNER JOIN sys.databases D ON D.database_id = K.database_id

You will see that the tempdb database is also encrypted, this happens because it is automatically encrypted when it detects that another database of the instance has been encrypted, in this way when we use tempdb for temporary tables, aggregations … our data will be safe .

To finish, if we want to disable the encryption and delete all the created components, we can do it with the following query:

-- Remove Encryption on AdventureWorks2016
USE [AdventureWorks2016]
GO
ALTER DATABASE [AdventureWorks2016] SET ENCRYPTION OFF;
GO
-- Wait 10 seconds
-- Drop Database Encryption Key
DROP DATABASE ENCRYPTION KEY; 
GO
USE [master]
GO
-- Drop Certificate
DROP CERTIFICATE TDE_AdventureWorks2016_Cert
GO
-- Drop Master Database Key
DROP MASTER KEY

Before finishing, I want to emphasize that although it is a relatively easy and quick process to apply, it is very important that we keep the Master Database Key and the Certificate in a safe place, since even the daily backups that we make will be encrypted and we will need these two components if we want to restore it.

In these first four entries of the GDPR series we have discovered how to detect, manage and protect our data, and we only have the last part to know how to Monitor and Inform if any violation or breach of the GDPR occurs.

GDPR Series:

1. How to adapt SQL Server to the GDPR
2. Discovering and classifying personal data with SQL Server
3. Managing access and use of data with SQL Server
4. Protecting data at Rest, in Use and in Transit
5. Monitor and Report unauthorized access to Personal Data