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

Question

How can I remove the encryption property from a column in SQL Server 2016?

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

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

1. Really undo the encryption using the certificate used for the encryption.
2. Remove the encryption and leave the encrypted data in the cells.

Answer 1

PowerShell is the best way to do this. You can encrypt and decrypt columns on the fly. First, launch the Always Encrypted wizard by right-clicking the table and choosing "Encrypt Columns." Next, go through the wizard until you get to "Run settings." Here you have the option to generate the ps script and save it. Make a copy of the script. One will be your script for decrypting columns, and the other for encrypting columns. Open the scripts in a text editor and edit as needed. Execute in PowerShell. I've included an example below on how to decrypt columns using an edited PowerShell script. Read the doc here:

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-column-encryption-using-powershell?view=sql-server-ver15

Example PowerShell script to decrypt two columns

#Generated by SQL Server Management Studio at 4:41 PM on 5/14/2020

Import-Module SqlServer #Set up connection and database SMO objects

#$password = "your password"

$sqlConnectionString = "Data Source=BLAH\MSSQL2014;Initial Catalog=BLAHDB;User ID=sa;Password=$password;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"" $smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

#If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate: #* Prompt for a username and password: #Add-SqlAzureAuthenticationContext -Interactive

#* Enter a Client ID, Secret, and Tenant ID: #Add-SqlAzureAuthenticationContext -ClientID '' -Secret '' -Tenant ''

#Change encryption schema

$encryptionChanges = @()

#Add changes for table [dbo].[blah_table]

$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.blah_table.field1 -EncryptionType Plaintext $encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.blah_table.field2 -EncryptionType Plaintext

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Answer 2

You should also check your tempdb. Is it encrypted e.g. Select is_encrypted from sys.databases.

Answer 3

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.

Answer 4

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.