dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted August 2, 2018

WEBINAR:
On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


How to Remove Transparent Data Encryption From a Database

By Greg Larsen

There may be a time when you want to turn off transparent data encryption on one of your TDE enabled databases.  One of these times might be if you want to send a copy of your database to someone else without having it encrypted.  Another reason might be you no longer need to have TDE turned on because you have either removed the confidential data you wanted encrypted, or you have encrypted your confidential data using something like Always Encrypted.

It is very simple to remove Transparent Data Encryption.  You can run the following code:

-- Turn off TDE
USE master;
GO
ALTER DATABASE MyEncryptedDB SET ENCRYPTION OFF;
GO
-- Remove Encryption Key from Database
USE MyEncryptedDB;
GO
DROP DATABASE ENCRYPTION KEY;
GO

Keep in mind, when you ALTER  your database and set the ENCRYPTION to OFF, that this is not instantaneous.  It will take SQL Server a while to decrypt your database. It does this decryption asynchronously.  If you want to see the status of your decryption you can run the following command:

SELECT db_name(database_id), encryption_state 
FROM sys.dm_database_encryption_keys;

If the encryption_state is set to “5” the database is still being decrypted.  If the encryption_state is set to a “1” then the database is decrypted, and if the encryption_state is set to “0”, then the database does not have an encryption key present.

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.