How to Disable TDE

In an earlier post Transparent Data Encryption (TDE), we saw how TDE could be enabled on a database in SQL Server. Today, we are going to see how this can be disabled though not a recommended approach. The process/steps described here are applicable to Azure SQL Managed Instance too.

Disabling the encryption is easier than to enable it. This can be disabled by the following command

Again, you can run the following query to check the status of the progress

Encryption State = 1 indicates that this has been disabled. However, note that decryption process runs in background. The query you execute to disable it runs quickly but that doesn’t mean that it has been disabled instantly. You can see the progress of decryption process in the output of above query against the column “percent_complete”

Good! All done? Not yet. Merely decrypting the database doesn’t mark it as done. You need to run another query to finally say that this has no encryption enabled on it.

…and now, run the query again to check the status

Here, you see that the database, encryption was disabled on, is not coming in the list though TempDB has been left there which is not an issue.

It’s easier to forget to run the last step of dropping the encryption key at database level which doesn’t mark the process of setting it off as complete so, be careful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s