Always Encrypted: How to insert encrypted columns?

Question

I tried this:

DECLARE @test varchar(50) = 'test'

INSERT INTO Customer VALUES ('Lucas', 'Test', @test)

But I got this error:

Encryption scheme mismatch for columns/variables '@test'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MyCEK', column_encryption_key_database_name = 'AlwaysEncrypted') (or weaker).

And I also tried this:

INSERT INTO Customer VALUES ('Lucas', 'Test', 'test')

But I got this error:

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 = 'MyCEK', column_encryption_key_database_name = 'AlwaysEncrypted') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

My scripts create a encrypted column:

ALTER TABLE [dbo].Customer
ADD [EncryptedValue] [varchar](50) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH(
        ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = MyCEK) NOT NULL

CREATE COLUMN ENCRYPTION KEY MyCEK   
WITH VALUES  
  (  
    COLUMN_MASTER_KEY = MyCMK,   
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',   
    ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86  
  )

CREATE COLUMN MASTER KEY MyCMK  
WITH (  
     KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',   
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'  
   ); 

Answer 1

You can not perform operations on the Encrypted columns directly. As to do any kind of operations you need an encryption key, in this case it is Column Encryption key.

When you look for column Encryption Key, you can find it under the "Security -> Always Encrypted Keys -> Column Encryption Keys". However, this column encryption key can not function independently and it is itself encrypted with "Column Master Key" present in the "Column Master Keys" section.

Normally when DBA's cerate the "Column Master Keys" they either use Thumbprint of existing certificates or create a new Certificate. But after it's creation and while deploying in the live environment, they delete the Certificate from the certificate store.

So that means as soon as they lock the table columns they remove the keys and keep it separately. Now this certificate can be used by the Web Developers while querying the DB and will be installed on the Web Server.

Column Master Key is used to encrypt the Column Encryption Key which in turn encrypts the columns.

So, what you can do here is as follows.

1. Write a Stored Procedure for inserting or updating values in encrypted columns.
2. Write a .net Web API to call that procedure.
3. Install the certificate having thumbprint of the Certificate which was used to create the Column Master Key on the server where Web API will be deployed.
4. call the API from other web applications or from Postman.

Alternatively, If you insist on doing it using SSMS then you should be having the Certificate with which the Column Master Key was created installed on that machine for that user. Then you need to connect the database using Additional Connection Parameters as "Column Encryption Setting = Enabled" And then calling the stored procedure from query window by declaring the variables and values to be passed in the single line. Follow below link for more information on this approach.

Inserting and Updating to Encrypted columns using SSMS

Hopefully, this resolves your problem.