Transparent Data Encryption in SQL Server

This feature is only available in the Enterprise edition of SQL Server 2014 and later, which secures the database from being sniffed by intruders as the primary files and the backups are useless until you have the encryption details.

Create the Master Key and Certificate to be used for the databases

/**************************/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sql@123';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE DBATest;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE DBATest
SET ENCRYPTION ON;
GO

/* The value 3 represents an encrypted state on the database and transaction logs. */

USE DBATest;
GO
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

Backup certificate and key on source for restore purpose on a secondary server.

USE master;
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'N:\adhoc\_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'N:\adhoc\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Sql@123')

Create master key and certificate at the destination before restore. Until you have the key and certificate create as given below and you will not be able to restore the database from the backup taken on the primary server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test@123'

CREATE CERTIFICATE MyServerCert
FROM FILE = 'D:\SQLEX2016\Backup\_TDE_Test_Certificate.cer' 
WITH PRIVATE KEY (FILE = 'D:\SQLEX2016\Backup\certificate_TDE_Test_Key.pvk',
DECRYPTION BY PASSWORD = 'Sql@123')

Restrictions

The following operations are not allowed during initial database encryption, key change, or database decryption:

1. Dropping a file from a filegroup in the database
2. Dropping the database
3. Taking the database offline
4. Detaching a database
5. Transitioning a database or filegroup into a READ ONLY state
6. The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
7. Dropping a file from a filegroup in the database.
8. Dropping the database.
9. Taking the database offline.
10. Detaching a database.
11. Transitioning a database or filegroup into a READ ONLY state.
12. Using an ALTER DATABASE command.
13. Starting a database or database file backup.
14. Starting a database or database file restore.
15. Creating a snapshot.
16. The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
17. The database is read-only or has any read-only file groups.
18. An ALTER DATABASE command is executing.
19. Any data backup is running.
20. The database is in an offline or restore condition.
21. A snapshot is in progress.
22. Database maintenance tasks.

Advantages

1. The performance impact of TDE is minor. Estimated to be around 3-5%
2. Performs real-time I/O encryption and decryption of the data and log files
3. Encrypts the Entire Database in rest
4. No architectural changes needed
5. No application code changes are required and the user experience is the same
6. Easy to implement
7. DBAs can still see the data

Disadvantages

1. No protection for data in memory
2. Not granular – Cannot just encrypt specific tables/columns
3. Not good for high CPU bottleneck servers
4. Not protected through communication/networks

To disable Transparent Data Encryption (TDE) on SQL Server database, below code can be used.

USE MASTER

GO

ALTER DATABASE DBATest

SET ENCRYPTION OFF

GO

USE DBATest

GO

DROP DATABASE ENCRYPTION KEY

GO

  

We can pause and resume TDE scanner process using trace flag 5004.

DBCC TRACEON(5004,-1)

----- Restart

DBCC TRACEOFF(5004,-1)

ALTER DATABASE TDE_Monit

SET ENCRYPTION ON;

By Mohammed Adil