How to Restore TDE (Transparent Data Encryption) Enabled Database from one SQL Server to Other SQL Server 2012 & Demo of Restore TDE enabled database in SQL Server

How to Restore TDE (Transparent Data Encryption) Enabled Database from one SQL Server to Other SQL Server 2012

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

available in the Enterprise edition of SQL Server 2008 and later

Getting Started with Transparent Data Encryption (TDE)

These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.

• Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
• Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
• Create a database encryption key and protect it by the certificate .
• Set the database to use encryption – When you enable TDE on any user database, encryption is also automatically enabled for the tempdb database. This prevents temporary objects that are used by the user database from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE.

TDE Eligible SQL Server Editions

First we must determine the correct version of SQL Server that allows Transparent Data Encryption. I like to call it an expensive feature as it requires Enterprise Editions. It also works with Developer Edition, but of course, this is just for testing and development purposes. When implementing this in a production environment you must have the correct version of SQL Server. I’ve listed the eligible editions below.

• SQL 2016 Evaluation, Developer, Enterprise
• SQL 2014 Evaluation, Developer, Enterprise
• SQL Server 2012 Evaluation, Developer, Enterprise
• SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
• SQL Server 2008 Evaluation, Developer, Enterprise

SQL Server TDE is a light encryption method that will not affect the queries performance, as the encryption is at the database files level. On the other hand, using the TDE, the database backup files will not take benefit from the backup compression feature completely.

Demo

use master
select getdate() 'Today Date and Time'

Select @@version as Version
SELECT
            @@SERVERNAME AS ServerName,SERVERPROPERTY('ProductVersion') AS ProductVersion,
            SERVERPROPERTY('ProductLevel') AS ProductLevel,
            SERVERPROPERTY('Edition') AS Edition,
            SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime', 

            SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'

Here Source database is MS SQL 2012 Enterprise RTM Version

Here Destination database is MS SQL 2012 Enterprise SP3 Version

Now we need to create Master Key by encryption and decryption and certificate in master database

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO
Use Master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

USE Master

CREATE CERTIFICATE TDE_CertTestTDE
WITH 
SUBJECT='Database_Encryption';
GO

after that we need to create Database encryption  on user database and we can see in DMV

USE [TestTDE]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_CertTestTDE;

GO

  --To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys

now we need to enable encryption on database level

after that we need to backup certificate on master database

ALTER DATABASE [TestTDE]
SET ENCRYPTION ON;
GO

USE Master

BACKUP CERTIFICATE TDE_CertTestTDE
TO FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',

ENCRYPTION BY PASSWORD='Pass@w0rd') 

We now need to go for destination server and create master key and certificate as we gave in source

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

use master

CREATE CERTIFICATE TDE_CertTestTDE
FROM FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
DECRYPTION BY PASSWORD='Pass@w0rd') 

Restore process:


we need to take backup of source database

now we are going to restore in Destination database

Be aware if you not create certificate on destination from backedup Certificate you might get Error as see below

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database other wise we will get below error

we are pointing to backup location from destination (our case we already created certificate from backup on destination )

we need to tick relocate and file name on restore

You can see we are successfully restore database of TDE enabled database

Here are below complete script on source and destination

Script:(source server)

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO
Use Master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

USE Master

CREATE CERTIFICATE TDE_CertTestTDE
WITH 
SUBJECT='Database_Encryption';
GO

USE [TestTDE]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_CertTestTDE;

GO

  --To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys

ALTER DATABASE [TestTDE]
SET ENCRYPTION ON;
GO

USE Master

BACKUP CERTIFICATE TDE_CertTestTDE
TO FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
ENCRYPTION BY PASSWORD='Pass@w0rd') 

Script:(destination server)

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'


use master

CREATE CERTIFICATE TDE_CertTestTDE
FROM FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
DECRYPTION BY PASSWORD='Pass@w0rd') 




Ref:
http://www.databasejournal.com/features/mssql/transparent-data-encryption-tde-in-sql-server.html

https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/



Happy Blogging!!!!!!!!!!!!!!!!!!