Remove Transparent Data Encryption (TDE)

-> I had a request to remove TDE on a SQL Server Instance.

-> Execute below query and check what databases are encrypted on the SQL Server instance,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

0.Cert_List.PNG

-> From the above screenshot, it is clear that database JB_DB and JBS_DB are encrypted with different certificate. Database JBS_App_DB is encrypted with a different certificate. Check the “encryptor_thumbprint” for the difference.

-> Execute below query to find the name of the certificates,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,b.name as Certificate, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys a INNER JOIN sys.certificates b ON a.encryptor_thumbprint=b.thumbprint

0_1.Cert_List.PNG

-> Checking the certificates from SSMS,

0_2.Cert_List_SSMS.PNG
-> Before removing TDE, I made a backup of the certificate using below TSQL,

USE master;
GO
BACKUP CERTIFICATE JBS_TDE_Certificate
TO FILE = 'C:\temp\JBS_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

BACKUP CERTIFICATE JBS_PROD_Certificate
TO FILE = 'C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_PROD_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

Removing TDE the wrong way

-> I tried removing the master key using below Tsql,

use master
go
DROP MASTER KEY

-> Received below error,

1.Error_master_key

Msg 15580, Level 16, State 1, Line 3
Cannot drop master key because certificate ‘JB_TDE_Certificate’ is encrypted by it.

-> It seems like JBS_TDE_Certificate should be dropped before removing the master key. Lets try removing the certificate JBS_TDE_Certificate using below Tsql,

USE [master]
GO
DROP CERTIFICATE [JBS_TDE_Certificate]
GO

-> Received below error,

3.Drop_cert_error.PNG

Msg 3716, Level 16, State 15, Line 3
The certificate ‘JBS_TDE_Certificate’ cannot be dropped because it is bound to one or more database encryption key.

-> You will receive a similar error when you try removing certificate JBS_PROD_Certificate.

Removing TDE the Right way

-> Execute below query to disable TDE on the affected databases,

USE master;
GO
ALTER DATABASE [JB_DB] SET ENCRYPTION OFF;
GO
ALTER DATABASE [JBS_App_DB] SET ENCRYPTION OFF;
GO
ALTER DATABASE [JBS_DB] SET ENCRYPTION OFF;
GO

-> Execute below query and make sure encryption_state_desc is set to “Unencrypted” for all User databases,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

4.Decrypt.PNG

-> Execute below query to remove the encryption key,

USE [JB_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE [JBS_App_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE [JBS_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

-> Execute below query again and see if there are any user databases encrypted,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

5.Only_Temdb.PNG

-> Drop certificates using below query,

USE [master]
GO
DROP CERTIFICATE [JB_TDE_Certificate]
GO
DROP CERTIFICATE [JBS_PROD_Certificate]
GO

-> Drop master key using below query,

use master
go
DROP MASTER KEY

-> Execute below query again and see if there are any databases encrypted,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

5.Only_Temdb

-> Tempdb is still there. Lets restart the SQL services and allow Tempdb to be recreated. Executing above query after restart will show no databases including Tempdb.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Restoring a Transparent Data Encryption (TDE) Enabled User Database on a SQL Server Instance with a different master key

-> I had to restore a database on a development server using a backup from a Production Server.

-> When I tried restoring the backup I got the below error,

1.Restore_Error.PNG

TITLE: Microsoft SQL Server Management Studio
——————————
Restore of database ‘JBS_App_DB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint ‘0xAA2EBEFF26F6DC56959BF42BFAFF09ABC6F12D39’. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> It seems like the user database from where the backup was performed is TDE enabled .

-> We should backup the Certificate and Private key from SQL Server Instance where this backup was performed using below,

USE master;
GO
BACKUP CERTIFICATE JBS_PROD_Certificate
TO FILE = 'C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_PROD_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

-> The created Certificate should be added to the SQL Server Instance where we are performing the restore.

-> Executing below TSQL to create the master key on Instance where the backup should be restored,

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Pa$$w0rd';
GO

-> Executing above Tsql produces below error,

2.Master_Key_Error.PNG

Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

-> It seems like the SQL Server instance already has a master key created.

-> Lets just create the certificate using below,

CREATE CERTIFICATE JBS_PROD_Certificate
FROM FILE='C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JBS_PROD_Certificate.pvk',
DECRYPTION BY PASSWORD='Pa$$w0rd')

-> Try restoring the database and it works without any issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Adding Transparent Data Encryption (TDE) Enabled User Database to Always On Availability Group

Environment

TDE_Blog.PNG

Enabling TDE on JBSERVER1


	--Create database JB_DB

	create database JB_DB
	go

	-- Create Master Key for TDE

	USE master;
	GO
	CREATE MASTER KEY ENCRYPTION
	       BY PASSWORD='QKyrk@%wIj$p97';
	GO

	-- Create Certificate 

	USE master;
	GO
	CREATE CERTIFICATE JB_TDE_Certificate
	       WITH SUBJECT='Certificate for TDE';
	GO

	-- Create Encryption key

	USE JB_DB
	GO
	CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;  

	-- Enable TDE for database JB_DB

	ALTER DATABASE JB_DB SET ENCRYPTION ON;
	GO

-> Enable TDE for another database.


	--Create database JBS_DB

	create database JBS_DB
	go

        -- Create Encryption key

	USE JBS_DB
	GO
	CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;  

	-- Enable TDE for database JBS_DB

	ALTER DATABASE JBS_DB SET ENCRYPTION ON;
	GO

-> Execute below query and make sure the databases are encrypted.

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
       WHEN '0' THEN 'No database encryption key present, no encryption'
	   WHEN '1' THEN 'Unencrypted'
	   WHEN '2' THEN 'Encryption in progress'
	   WHEN '3' THEN 'Encrypted'
	   WHEN '4' THEN 'Key change in progress'
	   WHEN '5' THEN 'Decryption in progress'
	   WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
	   ELSE 'No Status'
	   END,
	   percent_complete,encryptor_thumbprint,encryptor_type
 from sys.dm_database_encryption_keys

-> This is what I see,

Encrypt_DB.PNG

Backup the Certificate and Private Key

USE master;
GO
BACKUP CERTIFICATE JB_TDE_Certificate
TO FILE = 'C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JB_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='QKyrk@%wIj$p97');

Create certificate on JBSERVER2

-> Copy certificate C:\temp\JB_TDE_Certificate.cer and private key C:\temp\JB_TDE_Certificate.pvk from JBSERVER1 to JBSERVER2.

-> Execute below on JBSERVER2.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='QKyrk@%wIj$p97';
GO

CREATE CERTIFICATE JB_TD_Certificate
FROM FILE='C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JB_TDE_Certificate.pvk',
DECRYPTION BY PASSWORD='QKyrk@%wIj$p97')

-> Backup a database from JBSERVER1 and Restore it in JBSERVER2 and make sure restore works.

Adding Database JB_DB and JBS_DB to an Existing Alwayson Availability group JB_AG

-> Perform a Full database backup of JB_DB and JBS_DB on JBSERVER1.

-> Perform a Transaction Log backup of JB_DB and JBS_DB on JBSERVER1.

-> Try adding databases JB_DB and JBS_DB to Availability group JB_AG using GUI.

Add_DB.PNG

-> Adding a TDE enabled database using GUI is not possible. We need to use TSQL.

-> Create a database called Test and make sure it is not enabled with TDE. Create availability group JB_AG using database Test. Once the Availability group JB_AG is created on JBSERVER1 and JBSERVER2, Remove Test database from Availability group and delete the database Test from JBSERVER1 and JBSERVER2. This step is to create JB_AG Availability group before performing below steps.

-> Execute below query on JBSERVER1.

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JB_DB]

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JBS_DB]

-> Restore Full database backup and Transaction Log backup with NO RECOVERY on JBSERVER2.

-> Execute below query on JBSERVER2 to join the databases JB_DB and JBS_DB to Always On availability group JB_AG


USE master
GO
ALTER DATABASE [JB_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

USE master
GO
ALTER DATABASE [JBS_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

-> Check if the databases are added to Availability group JB_AG.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.