Set-SqlColumnEncryption cannot find or retrieve master column key from ms certificate store when setting up Always-Encrypted feature

Question

I am trying to implement the Always-Encrypted feature on a few columns of an MS SQL Server 2016 DB. I have, in fact, successfully implemented it on my UAT DB. However, when trying to do the identical steps on my production DB, I am getting the following error:

Set-SqlColumnEncryption : 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: 'xx-xx-xx-xx-xx-xx-xx-xx-xx-xx'. Keyset does not exist

Some details: I am executing the command

Set-SqlColumnEncryption -ColumnEncryptionSettings $l_columnEncryptionSettings -InputObject $l_database

within powershell. The master column key IS in the ms sql user store of the account running the process. It can be seen in certmgr in "Certificates - Current User\Personal\Certificates", and it contains both the public and private key (displaying the cert info shows "you have a private key that corresponds to this certificate").

Looking at the Always-Encrypted Keys entries in the DB, the Column Master Key shows with a keypath of CurrentUser/my/<fingerprint>, where the <fingerprint> does indeed match the fingerprint of the certificate in the MSSQL_CERTIFICATE_STORE.

I have seen this question (Always Encrypted Feature - Failed to decrypt column. Call from Windows-Service App), but the "solution" there either does not apply (I have imported public and private key into store), or is unclear (it mentions "access" to a directory, but details of what kind of access are not provided, and the account I am dealing with does indeed have read access to the directory mentioned.)

What is most strange is that I have already successfully implemented it on the same machine for the UAT DB (different DB, same SQL instance, different user), but I can see no differences between the two setups.

Does anyone know what might be causing this error or how to correct it? Thanks.

Edit: When I posed this question, the problem appeared to be with some incorrect setup for the feature, so the question was reasonable. On further investigation (see below), it turned out to be data corruption, so no one but myself could possibly have answered this.

Answer 1

CertMgr was showing that the certificate in the local store had been imported with the private key: opening the cert clearly showed "You have a private key that corresponds to this certificate", and the certificate was imported from a .pfx file containing the private key. HOWEVER, I downloaded, compiled and ran the "findprivatekey.exe" tool from Misrosoft. I ran

findprivatekey My currentUser -t "xx xx ... xx"

and oddly it came back with

FindPrivateKey failed for the following reason: Unable to obtain private key file name

In fact, no matter how I tried to specify the certificate (thumbprint, CN, etc), it always came back with the same failure. I deleted the cert and re-ran my import script. Same script, different day. And then everything worked.

I can only conclude that the directory containing the private key file was somehow corrupted.

Addendum:

For others with a similar problem, details of the cause of corruption and its fix: The next day, the private certificate data disappeared again. A technician's sharp eye noticed the file path for the private key (as shown by the findprivatekey command immediately after re-installing the cert) was located in the "Roaming" profile subtree, although the account was configured for local profile. Some investigation revealed a Windows feature called "Credential Roaming". Perusing this document: https://social.technet.microsoft.com/wiki/contents/articles/11483.credential-roaming.aspx, at the end is a section "Certificates Become Unusable or Renewed", which in this case applied. Issuing the command certutil -user -repairstore My "{Serialnumer}" fixed the issue.