Encrypting an existing column(Always Encrypted) using script

Question

I need to encrypt the name column with "Always Encrypted"(Deterministic) using column encryption key "MyCEK".

I don't want to do it through wizard but I need to write script for it which I can run across multiple servers (for different environment like DEV, TEST, PROD).

I already have the script for key creation.

ALTER TABLE MyTable
ALTER COLUMN [Name] [varchar](200) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [MyCEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL

Just wanted to know if I can alter the table using something like this it gives an error :

Operand type clash: nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'TwoValueCE', column_encryption_key_database_name = 'EmpData2') is incompatible with varchar(200) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'TwoValueCE', column_encryption_key_database_name = 'EmpData2') collation_name = 'Latin1_General_BIN2'

You can't. The whole point of Always Encrypted is that the server never has the key. You can't then submit a plain script to the server and get it to perform encryption actions. You have to pull the data out of the server as plain text, encrypt it and then give it back to the server in its encrypted form, and that's why you'll only find Wizards and the like for doing this sort of work. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 10, 2019 at 9:51
With T-SQL script - no, but you can do it with PowerShell. E.g. this article. — Andrey Nikolov
– Andrey Nikolov, Commented Jan 10, 2019 at 10:45

Aura · Accepted Answer · 2019-02-05 14:56:38Z

0

See if you can DROP and CREATE the TABLE, I run scripts to create new tables with encrypted columns without a problem, e.g.

CREATE TABLE [dbo].[Table1](
[ScannedInput] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
(COLUMN_ENCRYPTION_KEY = [CEK_Auto2], ENCRYPTION_TYPE = Deterministic, ALGORITHM 
= 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

edited Feb 5, 2019 at 14:56

Aura

1,3032 gold badges18 silver badges31 bronze badges

answered Feb 5, 2019 at 14:35

UppityNorrie

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Encrypting an existing column(Always Encrypted) using script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related