How to remove Always Encrypted from a column in SQL Server 2016

The answer for question 1) is to run the Always Encrypted wizard again and select "Plaintext" as Encryption Type.

Side note: I had to remove a default value constraint on a bit column to make the wizard complete without errors.


The above recommendation is a little simplistic and may set you up for problems down the road. First enabling Always Encrypted on a Column will change the collation of that column to one of the Binary2 Collations and moving back to cleartext does not return the column to the correct collation. If you try some comparisons you may get collation issues. Next you have left the Master key and Encryption keys. Maybe you want to keep them maybe not. But be prepared for strange errors like Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0] Statement(s) could not be prepared. If you don't want Always Encrypted get rid of all of it not just the visible piece. I got that error from a server that has Always Encrypted enabled on a database. But I wasn't working on that database. The database I was working on had no encryption enabled.