Transparent Data Encryption(TDE) - Encryption and Decryption on SQL Server 2008/2012/2014/2016*

TDE only provides I/O level encryption to data file and log file, not on network level and cause 2-5% performance when perform DB Backup/DML operations/Indexing rebuild/TempDB operations. And Filesttream data and buffer pool extension data does not encrypt by TDE, Also Replication HA does not enable encryption automatically.

Request 1: Encrypt the TDE_Test database on SQL Server A.

Request 2: Take the encrypted database backup file from Server A and restore on Server B.

Request 1: Encrypt the database<TDE_test> on SQL Server A.

Solution: Connect to SQL Server A with SA account. This is the server where we need to encrypt the database<TDE_test>

Note:

1. Must have to take a Full backup before starting the encryption activity.
2. Follow below mentioned restrictions. Refer page no. 4.

Step 1: Execute below query. The below query to Keep/set database in Single user mode and only for Read only operation.

Note: No open transaction on the user database<TDE_Test>.

USE master;

GO

ALTER DATABASE TDE_test

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE TDE_test

SET READ_ONLY;

GO

Step 2: Execute below query. The below query will create master encryption Key in master Database.

-- Create Master Key with password. Note: Provide strong password and upload on Keepass

USE master;

GO

Create MASTER KEY ENCRYPTION BY PASSWORD = '2#TDEPassword$11';

go

Step 2: Execute below query. The below script will create certificate in master database.

-- Create certificate

USE master;

CREATE CERTIFICATE TDETest1Certificate WITH SUBJECT = 'TDETest1Certificate';

go

Step 3: Execute below query. The below script to..

-- Create Database Encryption key to Encrypt database using created certificate.

USE [TDE_test]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDETest1Certificate;

GO

--RESULT OF ABOVE [TDE_test] DB ENCRYPTION

-- 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.

Step 4: Execute below query. The below script will generate two backup files: certificate file and private key file. Also here the password should be same as master Key Password. Refer Step 1.

--Take backups of both the certificate and the private key.

use master

BACKUP CERTIFICATE TDETest1Certificate

TO FILE = 'D:\TDETest1Certificate.cer'

with private key (file='D:\DTE_test_Cert_Key.pvk', encryption by password='2#TDEPassword$11');

GO

Step 5: Execute below query. The Below script will on the encryption.

-- On Encryption for DB

ALTER DATABASE [TDE_test]

SET ENCRYPTION On;

GO

Step 6: Execute below query. The below script will change the database mode from Single user to multi user mode.

Use Master

GO

ALTER DATABASE TDE_test

SET MULTI_USER;

GO




Step 7: Execute below script. The below script to verify the Encryption.

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

USE TDE_test;

SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3;

Encryption Activity is completed from TDE_test database on Server A.

Below steps to complete 2^nd request.

Request 2: Take the database encrypted backup file from Server A and restore on Server B.

Solution: Please follow below steps:

Step 1: Now Connect to Server B with SA account.. This is destination server where we have to restore the encrypted database<TDE_test>

1. Take a Full backup of database as we have to restore on Server B.
2. Also move the backup file and Certificate file and private key file in Server B.

-- Move or copy the database files from the source server to the same location on the destination server.

-- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.

Step 2:

-- Create a database master key on the destination instance of SQL Server B.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2#TDEPassword$11';

GO

Step 3:

-- Recreate the server certificate by using the original server certificate backup file.

-- The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TDETest1Certificate2

FROM FILE = 'D:\TDETest1Certificate.cer'

WITH PRIVATE KEY (FILE = 'D:\DTE_test_Cert_Key.pvk', DECRYPTION BY PASSWORD = '2#TDEPassword$11');

GO

Step 4: Now restore the backup file on server B. and verify the encryption with below script.

SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3;

Restrictions when you perform TDE:

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

• Dropping a file from a filegroup in the database
• Dropping the database
• Taking the database offline
• Detaching a database
• Transitioning a database or filegroup into a READ ONLY state
  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.
• Dropping a file from a filegroup in the database.
• Dropping the database.
• Taking the database offline.
• Detaching a database.
• Transitioning a database or filegroup into a READ ONLY state.
• Using an ALTER DATABASE command.
• Starting a database or database file backup.
• Starting a database or database file restore.
• Creating a snapshot.
  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.
• The database is read-only or has any read-only file groups.
• An ALTER DATABASE command is executing.
• Any data backup is running.
• The database is in an offline or restore condition.
• A snapshot is in progress.
• Database maintenance tasks.
  When creating database files, instant file initialization is not available when TDE is enabled.
  In order to encrypt the database encryption key with an asymmetric key, the asymmetric key must reside on an extensible key management provider.

Question: How to drop Encryption from database.

Answer:

Step 1: Turn off encryption on your user database

ALTER DATABASE your_user_database SET ENCRYPTION OFF

Step 2: Drop the Database Encryption Key of your user database

USE your_user_database

GO

DROP DATABASE ENCRYPTION KEY

Step 3: Drop the certificate and master key on master database

USE master

GO

DROP CERTIFICATE [TDETest1Certificate]

DROP MASTER KEY

Question: How to Alter Encryption

Answer: