1

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'

Jayendran
  • 9,638
  • 8
  • 60
  • 103
Sudhanshu jha
  • 11
  • 1
  • 2
    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 Jan 10 '19 at 09:51
  • With T-SQL script - no, but you can do it with PowerShell. E.g. [this article](https://www.sqlshack.com/configure-always-encrypted-sql-server-2016-using-ssms-powershell-t-sql/). – Andrey Nikolov Jan 10 '19 at 10:45

1 Answers1

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,
Aura
  • 1,283
  • 2
  • 16
  • 30
UppityNorrie
  • 1