Enable TDE for a database and how to restore TDE Enabled Database on other database server

Step by Step Enable TDE for a database and how to restore TDE Enabled Database on other database servers

---------------------------------------------------------------------------------------------------

For Theory refer this link 

-------------------------------

In Server 1:

USE Master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

CREATE CERTIFICATE TestSQLServerCert

WITH SUBJECT = 'Certificate to protect TDE key',
EXPIRY_DATE = '2030-04-23';

GO

-- Create a database to be protected by TDE.

CREATE DATABASE CustRecords;

GO

-- Switch to the new database.

-- Create a database encryption key, that is protected by the server certificate in the master database.

-- Alter the new database to encrypt the database using TDE.

USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

you will receive this warning..

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

Hence, we will take backup of that certificate in below steps

-- Create a backup of the server certificate in the master database.

-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server

-- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).

Use master
go

BACKUP CERTIFICATE TestSQLServerCert

TO FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO
Then execute above command again to remove those warning..USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

use CustRecords
go

ALTER DATABASE CustRecords SET ENCRYPTION ON;

GO

/* Now Verifying TDE is enabled for which database and what is status.. */
At any point, you can use the sys.dm_database_encryption_keys DMV to determine which databases are currently encrypted (or in progress of being encrypted) on your server.
This DMV also gives you the status of the encryption process of newly database.

USE master;

GO

SELECT

    db.name,

    db.is_encrypted,

    dm.encryption_state,

    dm.percent_complete,

    dm.key_algorithm,

    dm.key_length

FROM

    sys.databases db

    LEFT OUTER JOIN sys.dm_database_encryption_keys dm

        ON db.database_id = dm.database_id;

GO

or


SELECT DB_NAME(database_id) as database_name,
database_id,
CASE encryption_state
  when 0 Then N'No database encryption key present, no encryption'
  When 1 Then N'Unencrypted'
  When 2 Then N'Encryption in progress'
  When 3 Then 'Encrypted'
  When 4 Then 'Key change in progress'
  When 5 Then 'Decryption in Progress'
END as encryption_state,
key_algorithm,
key_length,
Percent_complete
from sys.dm_database_encryption_keys;

/*
select *
from  sys.certificates
inner join sys.key_encryptions
on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
where (sys.certificates.[name] = '<Certificate Name>')
*/

use master;
go

select * from sys.certificates
go

select @@SERVERNAME,database_name = d.name,
cert_name = c.name    --dek.encryptor_type,
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;
      
go                          

SELECT db_name(database_id),encryption_state,
case encryption_state
when 3 then '3=Encrypted'
end 'encryption_state',
encryptor_thumbprint, encryptor_type, percent_complete
FROM sys.dm_database_encryption_keys 


--Output will show -- > encryption_state is 3=Encrypted for <Database>


After successful execution, you will find 2 files in this location (C:\SQLCert)

 Detach the TDE protected database from the source server. (optional) if you want then.. 

USE Master ;

GO
EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';

GO

In Server-2
----------------
when you will try to restore TDE enabled database in 2nd server,will get below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally.

Hence

Create a database master key on the destination instance of SQL Server. For more information, see Using Transact-SQL below.

Recreate the server certificate by using the original server certificate backup file. For more information, see Using Transact-SQL below.

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

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

 USE master;

   GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

--- After creating a Master key when i tried to restore the database again, got below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Msg 33111, Level 16, State 3, Line 8
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 8
RESTORE FILELIST is terminating abnormally.

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

FROM FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO

-- Attach the database that is being moved.

-- The path of the database files must be the location where you have stored the database files.

CREATE DATABASE [CustRecords] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )

FOR ATTACH ;

GO

Thanks for Reading..

other Interesting link on this topic:-

https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

Link for Understanding Encryption Hierarchy is written in this blog link..

http://kushagrarakesh.blogspot.com/2015/09/there-is-already-master-key-in-database.html