Always Encrypted Error in Sanbox Database
-
Hi,
So I finally got some time to try our some SQL 2016 functionality and I've run into a bit of an issue.
The attached script shows what I've done but in short.I create a Column Master key (CMK) then a column encryption key to use the CMKD, then I create a table to use the CEK. All works a dream until I try to insert some data then I get the following back.
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ShowCaseCEK', column_encryption_key_database_name = 'ShowCase') collation_name = 'Latin1_General_CI_AS'
I dont know where its getting the varchar(8000) or why a varchar would be incompatiable with a varchar(8000).
Any help would be appreciated and thanks for taking the time to read this post.
Thanks,
-
Hi,
Values which are processed against Always Encrypted Columns have to be of type System.Data.SqlClient.SqlParameter - it is a limitation of the current implementation.
This even happens with SQL Server 2017 RC1.
First in SSMS user Version 17.1. Go to Query->Query Option->Execution->Advanced->Turn on "Enable Parameterization for Always Encrypted".
Then change the code to an INSERT..VALUES...statement with values which are variables this will work.
Notice that SSMS will underline the variables as having been converted to a System.Data.Sqlclient.SQLParameter object.
-
Hi,
Thanks for getting back to me on this, seems there a few limitations/consideration on this feature. I made he changes you mentioned but I'm still getting an error this time its this
Insert Statement
DECLARE @SecureID VARCHAR(100) = '11234',@FirstName VARCHAR(100) = 'Nic',@LastName VARCHAR(100) = 'Hopper',Address1 VARCHAR(100) = '123 High Street',@BirthDate DATE = '01 Jan 2017'INSERT INTO dbo.CustomersEncryptedShowCase
(SecureID, FirstName, LastName, Address1, BirthDate)
VALUES (@SecureID,@FirstName,@LastName,@Address1,@BirthDate)Error Message
Msg 33299, Level 16, State 6, Line 68
Encryption scheme mismatch for columns/variables '@SecureID'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '9' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ShowCaseCEK', column_encryption_key_database_name = 'ShowCase') (or weaker).
Whats interesting is that I don't get the blue lines in SSMS to tell me its going to convert the values, even though it is enabled. Also if I restart SSMS it turns the setting back off and I have to put the tick back in the box.
I'm going to do a bit more digging around to see what I can find on this, I'm fairly sure its something simple. If you have any ideas then please let me know.Thanks.
Nic
-
Nic
Have you enabled encryption in your connection? Right-click the query tab in SSMS, point to Connection, and then click Change Connection. This launches the Connect to Database Engine dialog box. Here you must click Options and then go to the Additional Connection Parameters tab and type in Column Encryption Setting=Enabled. I'm running into some of the same problems you are, so I hope you'll find an answer.
RMc
(for the record: I pasted the directions for enabling encryption from https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-encryption-always-encrypted/)
Further down in that article, he states that you can't insert encrypted data into a table using SSMS. I'm going to be real upset if that is the case. -
Hi,
Thanks for the extra information. Your correct my connection was not encrypted. I did as you said in the connection properties and it connects but I still had an issue with the date value going in. I was getting the following error;
Unable to convert @BirthDate to a System.Data.SqlClient.SqlParameter object. The specified literal cannot be converted to Date(System.Data.SqlDbType), as it used an unsupported date/time format. Use one of the supported date/time formats. Literal value: 01 Jan 2017
Turns out this error is raised in the DECLARE section which is trying to do the conversion to 'Enable Parametisation for Always Encrypted', turn this off and the running just the DECLARE bit works fine. Turn it back on and it returns the error above.
So then I decided I'd had enough of muttering swear words to myself and just set it to be NULL. That worked, the row was inserted. You can imagine my delight at this.Then I ran a SELECT on the table, I expected to see the encrypted value in the SecureID column, instead I got back the plain text value. So it didn't behave how I was expecting.
Then I had an idea, I disconnected, removed the encrypted connection option and parameter and reconnected and then the value was encrypted. So if you have the encryption properties in your connection it will decrypt values, if you don't then you will see the encrypted text.So it works.😀
Thank you so much for all your help. I hope the attached script shows things clearly.Nic
-
I'm glad you got everything working. In regards to your date field; I read that you need to use an ISO date format rather than a US based format. You should be able to convert your standard date '8/4/2017' into ISO format 112 (or 12 if only using 2 digit year) and it should work then. Haven't tested it with your code though.
-
Thanks (again).
I'll give it a try over the weekend and see how that goes.
Have a good weekend.
Nic
- I'll be waiting to se how it turns out. Meanwhile, I'm going to try using your code in my testing to see if it makes any difference. I've been gnashing my teeth trying to get records inserted too.
RMc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply