Always Encrypted and SSDT Post Deployment

Question

Struggling with an issue in SSDT when deploying seed data to a table that has AlwaysEncrypted Enabled.

SQL Server 2019 Enterprise Windows Server 2016 Data Center Visual Studio 2019 Community Edition 16.7.2 (current release as of this post)

Column Master Key, Column Encryption Keys, and Column definions are in project, and DB deploys without issue. Here is the post deployent script:

SET IDENTITY_INSERT DBO.Table1 ON

IF NOT EXISTS (SELECT * FROM DBO.Table1 WHERE Table1ID = 0)
BEGIN
    --need to parameterize values for always encrypted columns
    declare @fname nvarchar(20) = ''
    declare @lname nvarchar(25) = ''

    INSERT INTO DBO.Table1 (Table1ID, [FirstName], [LastName], [DateOfBirth] )
    VALUES (0,@fname,@lname,'2999-09-09')
END

SET IDENTITY_INSERT DBO.Table1 OFF

When executing this directly in SSMS with the AlwaysEncrypted flag set, the insert is successfully. This is true for both in normal and SQLCMD modes.

When publishing with SSDT via a published profile, I get the following error:

(101,1): SQL72014: .Net SqlClient Data Provider: Msg 33277, Level 16, State 6, Line 13 Encryption scheme mismatch for columns/variables '@fname'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '13' expects it to be DETERMINISTIC, or PLAINTEXT. (89,0): SQL72045: Script execution error. The executed script:

Doing a bit of research, it appears as if the connection string setting "Column Encryption Setting=Enabled" Isn't set in the publish profile. I get the same error if I try running the generated deployment script in SSMS without the AlwaysEncrypted flag set. Trying to set this through the UI, it reverts back to 'Disabled' and won't save no matter what I do. I then try to save it directly to the publish profile XML, rather that through the UI, and while it does save, the error doesn't change.

I try executing the generated deployment script from SSDT in SSMS with SQLCMD mode enabled and the AlwaysEncrypted flag set = true and interestingly, I get a different error. That error is:

An error occurred while executing batch. Error message is: ExecuteReader: CommandText property has not been initialized

Questions:

1. Is it possible to do a post deployment script into a table that has columns encyrpted with AlwaysEncrypted? If so, what do I need to do to get it working?
2. Why do I get the error in SSMS when running in SQLCMD? Is anyone able to replicate this and/or know what's going on?

Thank you for the help.

Nathan

Answer 1

Please see:

https://matthewmcgiffen.com/2017/04/10/working-with-data-in-always-encrypted/comment-page-1/

https://techcommunity.microsoft.com/t5/azure-sql-database/parameterization-for-always-encrypted-using-ssms-to-insert-into/ba-p/386124

Answer 2

Usually the certificate for the Column Master Key is stored in the client machine, not on the SQL server machine. If that is the case, you are not able to insert data into the table with an Always Encrypted column.

Answer 3

I'm having this exact same issue. Has there been an update?