Always Encrypted Feature - Failed to decrypt column. Call from Windows-Service App

Question

.NET Framework Ver = 4.6.2 , Database = SQL Server 2016

App Type = Windows Service

We are working on the "Always Encrypted" feature in the SQL 2016 db to perform the encryption on certain customer data columns. Our web application is built in the ASP.NET MVC architecture and is working fine with this new feature. We have copied and imported the certificate from the database server over to the IIS web-server. And the web-application is working smoothly.

But when we try to access the DB from a windows service application running on a separate server, it throws the following exception.

Failed to decrypt column 'ColumnX'. Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '76-34-51-DA-41-8F-52-D1-A1-EE'. Keyset does not exist

We have copied and imported the same certificate with the similar steps over to the server running the windows-service application.

Please suggest, if i am missing something in the certificate installation. Do we need to edit some propertied in the installed certificate ?

tukan · Accepted Answer · 2017-07-28T08:19:31.020

2

Always Encrypted functionality requires for the user that wants to access the database to have both public AND private key.

From the message Keyset does not exist I assume you have imported keys only partially or just one part of the set.

Edit
Do you have access to these items? %ALLUSERSPROFILE%\Microsoft\Crypto\RSA\MachineKeys OR C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys

Usually when the private key is missing or there is some permission error to access MachineKey folder (registry). It gives the "Keyset does not exist" error.

edited Jul 28 '17 at 08:19

answered Jul 28 '17 at 07:56

tukan

17,050
1
20
48

2

It was a permission issue on the Certificate used inside the windows server. After i gave the read access to a particular user group, it starts working.. – Karan Jul 31 '17 at 11:43
May I know what is the particular user group that require the permission ? – Toshihiko Dec 06 '19 at 07:11
@Toshihiko the user (group) that is running the service which is trying to access the DB. – tukan Dec 06 '19 at 07:50
Thanks. My side working after I add the IIS_IUSRS in the Manage Private Keys when right clicking the respective certificate in the certificate store. I was using a certificate for column encryption and using web service to access database, that's why I need to grant access to IIS users. – Toshihiko Dec 09 '19 at 01:04

score 2 · Answer 2 · answered Jun 20 '18 at 11:55

2

I was also facing this issue and resolved when changed user access level of account to Administrator. However running process as administrator can be a security issue,

answered Jun 20 '18 at 11:55

dilipkumar katre

128
1
12

score 0 · Answer 3 · answered Oct 04 '22 at 09:37

Run MMC console and add Local Machine Certificate Snap-In

Search for Always Encrypted Certificate in Personal Folder, open the context menu and click Manage Private Keys.

Add the user who uses that certificate on Your DB and give him full control of that key.

Always Encrypted Feature - Failed to decrypt column. Call from Windows-Service App

3 Answers3

Linked