Transparent Data Encryption Implementation And Moving TDE Database to another instance

Configuring TDE should go through 4 steps.

Create Master Key -> Create Certificate protected from Master Key -> Create database Encryption Key & protect by Certificate -> Enable Encryption on database.

Step 1: Create Master Key

This is mandatory step. Without this Master Key you won’t be able to create Certificate.

USE Master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEkey123'

GO

Step 2: Create Certificate protected from Master Key.

USE Master

GO

CREATE CERTIFICATE TDE_Demo WITH SUBJECT = 'TDE Certificate'

GO

If you are already having a Certificate, you can check using sys.certificates view.

Step 3: Create database Encryption Key & protect by Certificate

Here we are going to use AES algorithm for encrypting database.

USE TDE_DEMO

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDE_Demo

GO

Once you execute the above T-SQL, it will ask to back up the encryption key

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: Enable Encryption on database

ALTER DATABASE TDE_DEMO

SET ENCRYPTION ON

GO

Query to check whether database is encrypted or not.

SELECT name,is_encrypted FROM sys.databases WHERE name='TDE_DEMO'

Backup the certificate as discussed in Step 3.

USE Master

GO

BACKUP CERTIFICATE TDE_Demo TO FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_Certificate_backup'

WITH PRIVATE KEY (FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_key' ,

ENCRYPTION BY PASSWORD = 'TDEdemo123')

GO

  

  

Note: Please make sure to take backup of the certificate, because without that certificate you can’t do anything with the physical files of the database.

Moving TDE enabled database.

In order to move TDE enabled database you need to create certificate in destination instance from the backup of certificate we create while implementing in source instance.

Copy the Certificate and Private key file to destination server.

USE Master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEdemo123'

GO

CREATE CERTIFICATE TDE_Demo

    FROM FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_Certificate_backup'

    WITH PRIVATE KEY (FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_key',

    DECRYPTION BY PASSWORD = 'TDEdemo123');

GO

All the above paths and passwords are taken from source instance. You can find them in above TDE implementation article.

Now you can move TDE enabled database from source to destination.

You can use normal Backup and restore method or Attach and Detach method. Without creating Encryption key and certificate in destination you can move the TDE database.

Restore Database failed: Exclusive access could not be obtained because database is in use

This is quite common error message we see, when we restore database. This occurs if any other users connected to the database you tried to restore.

We have different of methods to resolve this based on our requirement.

1)      Alter Database to Single User Mode

Changing database to single user mode roll backs all connections at once.

ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE DATABASE_NAME FROM DISK = 'C:\DATABASE_NAME.BAK'

GO

2)      Kill all active connections on database you need to restore.

Use SP_WHO2 to find connections active on that database. Use KILL command to kill all the connections and then restore the database.

KILL (SPID)

3)      Make database OFFLINE (this will kill all the connections on that database), make database back ONLINE. Now perform restore database task.

ALTER DATABASE DATABASE_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE DATABASE_NAME SET ONLINE

GO