Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

/*

 There is only one master key, you can not create

 multiple

*/

use master

go

CREATE MASTER KEY ENCRYPTION BY PASSWORD='StrongPasswordHere'

GO

/*

 Creating certificate in the master database, after creating

 master key

*/

use master

GO

CREATE CERTIFICATE MyFirstCert

WITH SUBJECT='TDECertificate';

GO

/*

Enable databse encryption key but you can not see under this

specific user database.

*/

USE G

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM=AES_128

ENCRYPTION BY SERVER CERTIFICATE MyFirstCert

GO

ALTER DATABASE G
SET ENCRYPTION ON; 

/*

 Backing up certificate

*/

use master

go

BACKUP CERTIFICATE MyFirstCert

TO FILE='C:\Certificates\MyFirstCert'

WITH PRIVATE KEY (FILE='C:\Certificates\MyFirstCertKey',

ENCRYPTION BY PASSWORD='StrongPasswordhere');

/*

Restoring same database in another server after creating new certificate from source

server,then we can restore the database. Backup file already copied to the destination along with the

certificate and private key

Need capture source server password

 I have given the source server certificate name here. If you want

 to you can change the name here.

Run this command in the destination server and then restore .bak file

*/

Also create master key here

USE master

go

CREATE MASTER KEY ENCRYPTION BY PASSWORD='StrongPasswordHere'

 

USE master

go

CREATE CERTIFICATE MyFirstCert

FROM FILE = '\\node4\c$\Certificate\MyFirstCert'

WITH PRIVATE KEY

(

FILE = '\\node4\c$\Certificate\MyFirstCertKey' ,

DECRYPTION BY PASSWORD = 'SourceServerPasswordHere'

)

 

select * from sys.certificates

go

select * from SYS.master_key_passwords

GO

SELECT * FROM sys.key_encryptions

go

select * from sys.symmetric_keys

#######################################################
The below encryption is talking about SYMMETRIC KEY encryption at User database level, this is not comes under Server level, certificate and master key is going to create under user database level
#####################################################


USE UserDatabaseNameHere
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD='StrongPasswordHere'
GO
USE UserDatabaseNameHere
GO
CREATE CERTIFICATE ProvideCertificatename
WITH SUBJECT='ProvideCertificatenameSubject'
GO
USE UserDatabaseNameHere
GO
CREATE SYMMETRIC KEY ProvideAnyNameHereforKey
WITH ALGORITHM=AES_256
ENCRYPTION BY CERTIFICATE ProvideAboveCertificateNamehere
GO
--Checking purpose
  USE UserDatabaseNameHere
  GO
  select * from sys.certificates
  go
  select * from sys.key_encryptions
  go
  select * from sys.symmetric_keys

GO
----Here the main column is "is_master_key_encrypted_by_server"
  select is_encrypted,is_master_key_encrypted_by_server,* from sys.databases
where
--is_master_key_encrypted_by_server=1
name ='UserDatabaseNameHere'

The below command will disable encryption key under user database level. At this stage we are only disabling encryption key but not certificate. Certifcate will remain as it is at master database level.

ALTER DATABASE [AdventureWorks2016]  
SET ENCRYPTION OFF;  
GO  
/* Wait for decryption operation to complete, look for a   
value of  1 in the query below. */  
SELECT encryption_state  
FROM sys.dm_database_encryption_keys;  
GO  
USE [AdventureWorks2016];  
GO  
DROP DATABASE ENCRYPTION KEY;  
GO 

The below query will tell what are the databases that configured with TDE

SELECT db_name(database_id),* 
FROM sys.dm_database_encryption_keys

Below query will tell you how to drop server level certificate which is created in master database. Database names have been changed when you compare with above queries.So please ignore.

ALTER DATABASE [AdventureWorks2016_tde] 
SET ENCRYPTION OFF
go
USE [AdventureWorks2016_tde]
GO
DROP DATABASE ENCRYPTION KEY   
go
USE master
GO
DROP CERTIFICATE [MyServerCert]

The below scripts are similar to initial one, but providing the same as the queries are properly aligned.

---Create encryption on master for server level
USE master
GO
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD='Virtusa@2023'
GO
-- Check if the master key was created
select * from sys.symmetric_keys;

---Creating certificate on master database
USE master
go
CREATE CERTiFICATE NODE1ServerCertificate
WITH SUBJECT='I am NODE1 Certificate'
GO

-- Check that a certificate was created
select name
      , pvt_key_encryption_type_desc
      , issuer_name
      , subject
      , expiry_date
      , start_date
from sys.certificates
----Creating Encryption Key on AB Database
USE AB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=AES_128
ENCRYPTION BY SERVER CERTIFICATE NODE1ServerCertificate
GO
----Enabling encryption for AB database
USE master
GO
ALTER DATABASE AB
SET ENCRYPTION ON
GO
-- Check if the database encryption was set on for the database
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 

-----Backp the certifcate of NODE1
USE master
GO
BACKUP CERTIFICATE NODE1ServerCertificate
TO FILE ='\\Node1\backup\NODE1ServerCertificate.cer'
WITH PRIVATE KEY(FILE='\\Node1\backup\NODE1ServerCertificateKey.pvk',
ENCRYPTION BY PASSWORD='Virtusa@2023')

---------------------------------------------In the secondary replica if it is AOAG or the destination server where you are restoring

use master;
GO
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'Virtusa@2023'

use master;
CREATE CERTIFICATE NODE1ServerCertificate
FROM FILE = '\\Node1\backup\NODE1ServerCertificate.cer'
WITH PRIVATE KEY (FILE = '\\Node1\backup\NODE1ServerCertificateKey.pvk',
DECRYPTION BY PASSWORD = 'Virtusa@2023')
go
--After restoring the below command will join to aoag afer restoring with latest FULL and DIFF
USE master 
go 
ALTER AVAILABILITY GROUP AGGROUPNameHere ADD DATABASE [AB]


---- Just check endpoint should be enabled with ENCRYPTION
USE [master]
GO

/****** Object:  Endpoint [Hadr_endpoint]    Script Date: 9/13/2023 2:07:35 PM ******/
CREATE ENDPOINT [Hadr_endpoint] 
	STATE=STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

The script below will explain about deletion of Certificate, master key and encryption from SQLServer and its associated databaes.

----Check TDE on SQL Server Instance
SELECT DB_Name(database_id) As [DB Name], encryption_state,*
FROM sys.dm_database_encryption_keys
GO
SELECT name,is_encrypted
FROM sys.databases
GO
USE master;
GO
ALTER DATABASE AB SET ENCRYPTION OFF;
ALTER DATABASE DEF SET ENCRYPTION OFF;
go
USE AB
GO
DROP DATABASE ENCRYPTION KEY
GO
USE DEF
GO
DROP DATABASE ENCRYPTION KEY
go
----Check TDE on SQL Server Instance
SELECT DB_Name(database_id) As [DB Name], encryption_state,*
FROM sys.dm_database_encryption_keys
GO
SELECT name,is_encrypted
FROM sys.databases
---Drop TDE certificate from master databse
USE master
Go
DROP CERTIFICATE node1ServerCertificate
GO
-- Drop master key
USE master
Go
DROP MASTER KEY;
GO