CODEWITHSUNDEEP
sql-servert-sql
Simon Parker
Simon Parker

Reputation: 1834

Adding encrypted columns to a SQL Server database with always encrypted on

I have a SQL Server database, with always encrypted on. I am using command line sql (T-SQL) to update the database, and I want to add a column. I can't find the syntax for adding a column that is marked as encrypted.

I know I can add the column and then use SQL Server Management Studio to set it to encrypted, but I really want to do it in a batch job, hence the T-SQL commands.

Any ideas?

Upvotes: 0

Views: 258

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

There's no TSQL API for it by design:

You can perform the above steps using SQL tools:

SQL Server Management Studio (SSMS)

SQL Server PowerShell

sqlpackage - which automate the setup process

To ensure Always Encrypted keys and protected sensitive data are never revealed in plaintext to the database environment, the Database Engine can't be involved in key provisioning and data encryption, or decryption operations. Therefore, Transact-SQL (T-SQL) doesn't support key provisioning or cryptographic operations. For the same reason, encrypting existing data or re-encrypting it (with a different encryption type or a column encryption key) needs to be performed outside of the database (SQL tools can automate that).

Always Encrypted

Upvotes: 3

Related Questions