1

I have a scenario where I need to encrypt few of the existing columns through "Always Encrypted", but I am in need to do through t-Sql scripts(The tested script needs to be executed in production environment). How could this be done?

Similar to below

ALTER TABLE testTable ALTER COLUMN StringColumn  NVARCHAR(20) COLLATE
Latin1_General_BIN2  ENCRYPTED WITH (
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK_Auto4)

But,The above code throws the following error

Operand type clash: nvarchar is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEKName', column_encryption_key_database_name = 'DBName')

Note: I don't have provisions to execute PowerShell scripts. I already have t-SQL script for generating keys

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Samuel A C
  • 406
  • 3
  • 16
  • A way to generate the raw SQL is to use the wizard for your operation and use the 'Script to new window' function that is available usually at the top right of the window in a options(?) button. – dfundako Oct 31 '18 at 15:13
  • This *should* be possible once you move up to 2019 with the [Secure Enclaves](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sqlallproducts-allversions) feature. One of their first *examples* of the use for this feature is "You can issue in-place encryption using the ALTER TABLE Transact-SQL statement" – Damien_The_Unbeliever Oct 31 '18 at 15:14
  • what is the SSMS version you are using ? – Jayendran Nov 01 '18 at 00:46
  • I only have 2017 version... – Samuel A C Nov 01 '18 at 03:09
  • @dfundako does this feature exist in sql server 2017..? – Samuel A C Nov 01 '18 at 03:10
  • @Damien_The_Unbeliever thx...but for the near future 2019 is not an option for me – Samuel A C Nov 01 '18 at 03:11

1 Answers1

1

You can't do that, because SQL Server can't decrypt and encrypt your data. This can happens only on client side (application, SSMS, PowerShell). Essentially, you must add new encrypted columns and copy the unencrypted data from the existing one. Please note, that this can't be done by executing simple "update table set encrypted_column = unencrypted_column" T-SQL statement, because SQL Server can't encrypt/decrypt the data. So either you must do this in the application level, or run the SSMS wizard and save the process as PowerShell script, which you can run against your production server later.

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32