Close Menu

Encryption

 


TDE: Disabling Transparent Data Encryption In SQL Server


• Published on august 1, 2017


Before You Proceed we recommend you to
read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.


In the previous articles we have seen how to implement TDE by Enabling TDE on SQL Server and Moving a TDE Enabled Database on to a different instance. Let Us now Discuss how to Disable encryption (TDE) and Remove cert

Disabling TDE in Sql Server.

Case1:If you want to remove the certificate attached to a database it gives the below ERRORS'S

"Msg 3716, Level 16, State 15, Line 1
The certificate 'XXXVI_DB_Cert' cannot be dropped because it is bound to one or more database encryption key."

Case2: If we try to drop the masterkey we get the below errors

Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'XXXVI_DB_Cert' is encrypted by it.

In order to remover encryption we have to follow the below steps

  • Step 1: Remove Encryption
USE MASTER
GO
ALTER DATABASE xxxxxvi
SET ENCRYPTION OFF
GO

Step 2: Drop Encryption key associated with the database

USE xxxxxvi
GO
DROP DATABASE ENCRYPTION KEY
GO

Step 3: Drop the server certificate database with a server Certifcate

USE [master]
GO
DROP CERTIFICATE [XXXVI_DB_Cert]
GO

now the main master ke can be dropped
Step 4: Drop the masterkey

USE master
GO
DROP MASTER KEY
GO

Step 5: Verification

USE master 
GO 
SELECT *FROM sys.certificates WHERE pvt_key_encryption_type <> 'NA' 
GO
--Check Certificates
USE master
GO
SELECT key_length, key_algorithm, encryption_state, create_date 
FROM sys.dm_database_encryption_keys
GO
--Check Encryption Key Details for 2008--s

USE master
GO
SELECT encryptor_type, key_length, key_algorithm, encryption_state, create_date 
FROM sys.dm_database_encryption_keys
GO
--Check Encryption Key Details for 2012 and further versions