TDE procedure

TDE creation for database and restoring that database to another server

*         CREATION OF TDE ENCRYPTION FOR DATABASE

=======================================================================

--CREATION OF MASTER KEY ON PRIMARY SERVER

--step-1

create database test

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Shiva@123'

--VALIDATE IF A MASTER KEY HAS BEEN CREATED ON PRIMARY SERVER

--step-2

use master

select * from sys.symmetric_keys

--CREATATION OF CERTIFICATE ON PRIMARY SERVER

--step-3

use master

create certificate mytestcert

with subject='Mytestcertificate'

--VALIDATE THAT A DATABASE CERTIFICATE HAS BEEN CREATED OR NOT

SELECT * FROM sys.certificates

--CREATE DATABASE ENCRYPTION KEY

--step-4

use test

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM=AES_256

ENCRYPTION BY SERVER CERTIFICATE mytestcert

--SET THE DATABASE TO USE ENCRYPTION

--step-5

ALTER DATABASE test

SET ENCRYPTION ON

-- TO BACKUP THE CERTIFICATE AND PRIVATE KEY

--STEP-6

USE master

GO

BACKUP CERTIFICATE mytestcert

TO FILE = 'D:\mytestcertbkp1.bak'

WITH PRIVATE KEY ( FILE = 'D:\mytestcert_private_key1.bak' ,

ENCRYPTION BY PASSWORD = 'Shannu@123' )

GO

--To confirm whether the database is set to encryption you can use the query below, if it returns 1 then the database is encrypted else it’s not encrypted.

select name,is_encrypted

from sys.databases where name='TEST'

--TO KNOW THE STATUS OF THE ENCRYPTION FOR DATABASE9(I’ve used a where condition on encryption_state column for 3, Where 3 represents an encrypted state on the database and transaction logs.)

USE test

GO

SELECT DB_NAME(database_id),*

FROM sys.dm_database_encryption_keys WHERE encryption_state = 3

*                  MOVING THE TDE DATABASE TO ANOTHER SERVER

On secondary server execute following script to create master key and certificate using previos server certificate file and private key file:

USE master

-- Create a new master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Shiva@123'   

-- Restore the certificate.

CREATE CERTIFICATE MySQLCert

FROM FILE='D:\mytestcertbkp1.bak'

WITH PRIVATE KEY ( FILE = 'D:\mytestcert_private_key1.bak',

DECRYPTION BY PASSWORD='Shannu@123')

*                          RESTORE THE DATABASE IN NORMAL MANNER

On secondary server restore the database form back up files