Clever Geek Handbook

How to remove Always Encrypted from column in Sql Server 2016

How to remove an encryption property from a column in SQL Server 2016?

I enabled this feature using the Always Encrypted wizard in SQL Server Management Studio 2016, but I would like to remove encryption from some of the columns I added earlier.

I wonder if the following 2 things are possible (and if so: how?):

  • Really cancel encryption using the certificate used for encryption.
  • Remove encryption and leave encrypted data in cells.
+3


source to share


3 answers


Answer to question 1) - Run the Always Encrypted wizard again and select "Plaintext" as the encryption type.



Side note: I had to remove the default constraint on the bit column to make the master error free.

+2


source


The above recommendation is a little oversimplified and may help you fix future problems. Turning on "Always Encrypted" for the first time on a column will change the collation of that column to one of the binary collages, and reverting to cleartext will not return the column to the correct collation. If you try some comparisons, you might get sorting problems. Then you leave the master key and encryption keys. Maybe you want to keep them, maybe not. But be prepared for strange errors such as Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0] Statements cannot be prepared. Unless you want Always Encrypted to get rid of all this not only the visible part. I got this error from a server that is always encrypted enabled in the database.But I didn't work on this database. The database I was working on didn't have encryption enabled.



+1


source


You should also check your tempdb. Is it encrypted, for example. Select is_encrypted from sys.databases.

0


source






More articles:


All Articles