Transparent Data Encryption In SQL Server

Priyaranjan K S

SQL ServerTransparent Data Encryption

11 Comments

Join the conversation! Your thoughts help the community grow.

Rayees Kuniyil8y ago
Thanks for sharing....,,,,,,,,,
Former member9y ago
In which sql server version Column Encryption will be possible?
Former member9y ago
you said "Encryption has been implemented, the end user wouldn’t feel the Encryption in an action. " If user A use transparent encryption to encrypt data and if user d can see the data then what is the objective of encrypting data using transparent encryption ?
- Priyaranjan K S9y ago
  However TDE is intended to protect the DB from being exploited by unintended users. If you want to attach it to a different server, you will have to get a back up of the certificate as well as the Encryption Key from the original server and restore it in the new server. Since the hacker cannot get the certificate from the original server, he cannot do anything with the encrypted DB. If the certificate and encryption key is present in the server, the decryption happens internally without specifying the key in the query . This process is different from Column Encryption. You can read abt Col Encryption from here : https://social.technet.microsoft.com/wiki/contents/articles/37513.implement-column-level-encryptiondecryption-in-sql-server-2016.aspx
- Priyaranjan K S9y ago
  TDE encrypts data at rest and if the end user is the intended user who has the key to the encrypted data, obviously he sees the decrypted data and he doesnt have to run any specific query to decrypt the data(unlike in column encryption where the user has to write extra query to decrypt). If the user doesn have the key, he doesn get to see the data at all ! https://social.technet.microsoft.com/wiki/contents/articles/37513.implement-column-level-encryptiondecryption-in-sql-server-2016.aspx
- Priyaranjan K S9y ago
  Rafnas T P TDE does not require the user to explicitly use the key to decrypt the data. If we were using column level encryption, then to get the decrypted data we would have to explicitly use the decryption key as OPEN SYMMETRIC KEY SQLSymmetricKey DECRYPTION BY CERTIFICATE SelfSignedCertificate; SELECT FirstName, LastName,LoginID,UserPassword,EncryptedPassword
Former member9y ago
Which sql server version support this kind of encryption ?
- Priyaranjan K S9y ago
  Versions from 2008 and upwards supports TDE
Dharmraj Thakur9y ago
Really helpful..........................thanks
Thiruppathi R9y ago
Thanks for sharing....

Transparent Data Encryption (TDE) is an encryption mechanism of encrypting the data at rest. The data is encrypted before writing to the database and is decrypted before being read from it. This process happens behind the scene such that the client is unaware about the Encryption/Decryption process, hence the name ‘Transparent’.

Encryption Internals

Transparent Data Encryption is implemented by converting the plain text to a Cipher text, using an encryption algorithm. In our case, it is AES_256 and a key. The key acts as the locking mechanism. To decrypt Cipher text, the same algorithm and key must be used, which prevents spurious hackers from decrypting the Cipher as they don’t know the key used for initial encryption. Thus, the key serves the locking/unlocking mechanism of the encryption algorithm.

The overall steps involved in the implementation of TDE are given below.

Create a Master key, which is a Symmetric Key.
Create the Certificate, using the Master Key.
Create Encryption Key and Use the certificate to protect the Asymmetric Encryption key.
Alter the database to enable TDE

Create Master key

As the first step, we must create a Master key, which is a Symmetric key. It will be later used to create the certificate, which protects the Asymmetric Encryption key. We will be using the Create Master Key command to generate the Master key.

USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password-1';

Create the Certificate

Once the Master key is in place, we will then use it to create the certificate. This certificate will finally be used to generate the Asymmetric Encryption key, which is required to encrypt the data.

CREATE CERTIFICATE SelfSignedCert
WITH SUBJECT = 'Self Signed Certificate';

Create Encryption key

Once the certificate has been created, we will create the final Encryption key, which will be used to encrypt the data. The Encryption key in turn will be encrypted, using the previously created certificate.

USE DB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SelfSignedCert;

Set Transparent Data Encryption

As the final step, we will enable the Transparent Data Encryption on the database, using the Set Encryption On command.

ALTER DATABASE DB
SET ENCRYPTION ON;

Summary

Thus, we have enabled Transparent Data Encryption on the database, using Asymmetric and Symmetric keys. Let’s check Encryption status given below.

SELECT DB_NAME(database_id) DatabaseName,
encryption_state EncryptionState,
key_algorithm EncryptionAlgorithm,
key_length KeyLength,
encryptor_type EncryptionType
FROM sys.dm_database_encryption_keys;

SQL Server

As we can see, there are two databases that are listed. DB is the name of the database in use. When we implement TDE on any database, the Tempdb will also be encrypted along with it. The encryption State ‘3’ indicates that the DB is in encrypted state. The values of Encryption state are given in the table below.

Unencrypted
Encryption in progress
Encrypted

Though the Encryption has been implemented, the end user wouldn’t feel the Encryption in an action. We can check the results by running the select query and we will get the results as usual.

SQL Server