How to enable/remove Transparent Data Encryption (TDE)
[Note: Before working with the example please read the VERY IMPORTANT NOTE at the end of this page.]
If your database files (data and log files) are stolen, those database files can be attached in another SQL Server instance, or if your backup is stolen, it is restored in another SQL Server instance. So non-encrypted data can be accessed easily. With Transparent Data Encryption (TDE), the data and log files and also backup files are automatically encrypted, and the data within these files cannot be accessed without an encryption key.
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. TDE does not provide encryption across communication channels. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.
As a summary
When the data is written from buffer pool to disk (MDF, LDF & NDF) the data is first encrypted and then then encrypted data will be sent to disk.
When the data is read from the disk, first the data will be decrypted and then it will be sent to buffer pool.
If a database is being used in database mirroring or log shipping, both databases will be encrypted. The log transactions will be encrypted when sent between them.
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.
Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files.
FILESTREAM data is not encrypted even when TDE is enabled.
The performance impact of TDE is minor. The overall performance impact is estimated to be around 3-5% (As per Microsoft document) and can be much lower if most of the data accessed is stored in memory. Encryption is CPU intensive and is performed at I/O. Therefore, servers with low I/O and a low CPU load will have the least performance impact. We need to capture performance baseline for cpu and disk so performance tool will help us for that purpose.
[Reference BOL]
How to enable encryption
Go To SQL SERVER 1
--drop database tde create database tde go use tde go USE master GO --create service master key --drop master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1_batuhan' go --create certificate --drop certificate TDECert CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate' go --check the certificate SELECT name, pvt_key_encryption_type_desc FROM sys.certificates WHERE name = 'TDECert' go --don't forget to backup the certificate and keep it in secure place BACKUP CERTIFICATE TDECert TO FILE='C:\Backup\TDE\TDECert.certbak' WITH PRIVATE KEY ( FILE='C:\Backup\TDE\TDECert.pkbak', ENCRYPTION BY PASSWORD='PrivateKey1_Password') go use tde go --A database encryption key is required before a database can be encrypted --by using Transparent Database Encryption (TDE). --When a database is transparently encrypted, --the whole database is encrypted at the file level CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECert go USE master GO --enable TDE over tde database ALTER DATABASE tde SET ENCRYPTION ON go --check database tde whether encrypted or not SELECT name, is_encrypted FROM sys.databases where name = 'tde' go --check the database encryption keys --when you enable tde over a user database --tempdb will be encrypted at the file level --tempdb database_id is 2 and you will see other databases --which are encrypted at the file level using tde select * from sys.dm_database_encryption_keys go --backup database backup database tde to disk = 'c:\backup\tde.bak' go restore filelistonly from disk = 'c:\backup\tde.bak'
Go to SQL SERVER 2
Try to run those statements below
restore filelistonly from disk = 'c:\backup\tde.bak' go RESTORE DATABASE tde FROM DISK = N'C:\Backup\tde.bak' WITH FILE = 1, MOVE N'tde' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde.mdf', MOVE N'tde_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde_1.LDF', NOUNLOAD, STATS = 10 GO
And you will get the following error below
Msg 33111, Level 16, State 3, Line 1 Cannot find server certificate with thumbprint
'0x4C910C726DA8DD569148D3B2525015978FACD35D'. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What is the reason to get this error?
We get the error because we haven't transferred the certificate yet. Lets transfer the certificate
USE master GO --create service master key --drop master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1_batuhan_sql2' go --transfer certificate from the certificate backup --drop certificate TDECert CREATE CERTIFICATE TDECert FROM FILE='C:\Backup\TDE\TDECert.certbak' WITH PRIVATE KEY( FILE='C:\Backup\TDE\TDECert.pkbak', DECRYPTION BY PASSWORD='PrivateKey1_Password') go
And you will have the output like below
14 percent processed.
23 percent processed.
33 percent processed.
42 percent processed.
52 percent processed.
61 percent processed.
70 percent processed.
80 percent processed.
94 percent processed.
100 percent processed.
Processed 168 pages for database 'tde', file 'tde' on file 1.
Processed 2 pages for database 'tde', file 'tde_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.246 seconds (5.371 MB/sec).
How to remove encryption
Go to SQL SERVER 1
use master go --remove encryption from tde database ALTER DATABASE tde SET ENCRYPTION OFF go use tde go --drop the encryption key from tde database drop database encryption key go --check database tde whether encrypted or not SELECT name, is_encrypted FROM sys.databases where name = 'tde' go --get the new backup without tde. --we will test the restore on another server backup database tde to disk = 'c:\backup\tde1.bak' go
Go to SQL SERVER 2
--removed encryption from tde database -- from other sql server use master go --drop previously created database drop database tde go --check the certificate whether exists or not SELECT name, pvt_key_encryption_type_desc FROM sys.certificates WHERE name = 'TDECert' go --if exists drop the certificate to see --the restore without the certicate DROP CERTIFICATE TDECert go --if it is not required for any other database --master key can be dropped DROP MASTER KEY go --check the restore from previous backup with encryption enabled restore filelistonly from disk = 'c:\backup\tde.bak' go RESTORE DATABASE tde FROM DISK = N'C:\Backup\tde.bak' WITH FILE = 1, MOVE N'tde' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde.mdf', MOVE N'tde_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde_1.LDF', NOUNLOAD, STATS = 10 GO --It cannot be restored because the previous backup was encrypted with tde --The error is like below /* Msg 33111, Level 16, State 3, Line 1 Cannot find server certificate with thumbprint '0x8562FC9A0F3FBA33AAEABF36E2BE8C93AA365D5A'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. */ --check the restore from last backup with encryption disabled restore filelistonly from disk = 'c:\backup\tde1.bak' go RESTORE DATABASE tde FROM DISK = N'C:\Backup\tde1.bak' WITH FILE = 1, MOVE N'tde' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde.mdf', MOVE N'tde_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2\MSSQL\DATA\tde_1.LDF', NOUNLOAD, STATS = 10 GO
So we couldn’t restore the backup with encryption enabled because we didn’t have certificate on the SQL SERVER 2. We already dropped it from SQL SERVER 2. After removing encryption from the database from SQL SERVER 1, we got another backup and we could restore it even if we didn’t have certificate on the SQL SERVER 2.
14 percent processed.
23 percent processed.
33 percent processed.
42 percent processed.
52 percent processed.
61 percent processed.
71 percent processed.
80 percent processed.
94 percent processed.
100 percent processed.
Processed 168 pages for database 'tde', file 'tde' on file 1.
Processed 1 pages for database 'tde', file 'tde_log' on file 1.
RESTORE DATABASE successfully processed 169 pages in 0.292 seconds (4.521 MB/sec).
VERY IMPORTANT NOTE
FIX: You cannot restore a backup of a database on another instance of SQL Server if you disable transparent data encryption before you create the backup in SQL Server 2008 or in SQL Server 2008 R2
https://support.microsoft.com/kb/2300689
I removed encryption from SQL SERVER 1 and got backup but couldn’t restore it over SQL SERVER 2. Log file was corrupted. I got the errors below.
System.Data.SqlClient.SqlError: The file "<database name>_log" failed to initialize correctly.
Examine the error logs for more details. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint
'0xCB62FF76463A6BF86E8F769B541BA6483AFC2FF2'. (Microsoft.SqlServer.Smo)
And also if you remove the encryption from SQL SERVER 1 and drop the certificate from SQL Server 1 you can also lose your database from SQL SERVER 1.
I strongly suggest to apply the fix before working with Transparent Data Encryption (TDE).