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