Always Encrypted Feature - Failed to decrypt colum

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 ?

Answer 1

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.

Answer 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,