.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 ?
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.