Encrypt/Decrypt column in sql server using entity framework

Question

I am trying to encrypt and decrypt some columns in SQL database using Entity Framework 6 and database project.

I created my master key:

CREATE COLUMN MASTER KEY [CMK_1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/my/D4331582F1485517DA1807CF0A5500C........'
)

and my encryption key:

CREATE COLUMN ENCRYPTION KEY [CEK_1]
WITH VALUES
(
     COLUMN_MASTER_KEY = [CMK_1],
     ALGORITHM = N'RSA_OAEP',
     ENCRYPTED_VALUE = 0x016E00000163007500....
);

and mt table is:

CREATE TABLE [dbo].[MyWords]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [MyWord] [NVARCHAR](4) COLLATE Latin1_General_BIN2 ENCRYPTED WITH(ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_1]) NOT NULL,
)
GO

The database is created successfully. Both the table and the keys exist.

I am trying to add a new entry to the database using entity framework but I am getting this error:

Failed to decrypt a column cipher key using the following key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: 'AF-B3-97-27-5A-BC-33-24-75-FA'. \ R \ nDegumental certificate 'D4331582F1485517DA1807C.....' was not in the 'My' certificate store under the location 'CurrentUser' found.

I don't know why.

P.S. I added the enable parameter in the connection string:

Column Encryption Setting = Enabled;

Am I missing something here? Why do I receive this error on .SaveChanges? a little explanation would be very helpful! Thank you!

score 1 · Answer 1 · answered Mar 14 '18 at 14:27

After two days of searching, I found the answer. The problem was with not knowing the certificate. So I generated the certificate using Always Encrypted: https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx

and then I went to certificate manager and copied the fingerprint of the generated certificate and paste it when generating the master key:

KEY_PATH = N'CurrentUser/my/[new_fingerprint]'

and it works. It will always be encrypted on my local machine. I tried to back up the database and use it on another machine. the column was encrypted until I export/import the certificate.

Encrypt/Decrypt column in sql server using entity framework

1 Answers1