Decrypt error

Question

Decrypt error

David Chase 681

I am testing Always Encrypted and I am getting the error below when my web page tries to read an encrypted column in a stored procedure. I ran the stored procedure from the server in SSMS and it worked fine. When I run the same stored procedure from my workstation SSMS connected to the same server I get the exact error below. What am I missing?

An unhandled exception occurred:
Message: Failed to decrypt column 'FirstName'.
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: 'DD-73-EE-95-6F-75-A3-0A-63-84'.
Certificate with thumbprint '0F76519B0D08D52D6EBAF5BD2B86FA46E6DBAA7F' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

Ben Miller (DBAduck) 966 Reputation points

2020-10-22T03:20:26.38+00:00

Do you have the certificate that was created for Always Encrypted on the web server in the Machine Cert store?
Do you have the attribute in your connection string?

The message indicates that you don't have it in the right place for the driver to decrypt things. I am happy to help you get this running.
tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-22T06:38:04.17+00:00

I'm no expert in these things, but to me it looks line the cert it looks for is in the user store (not machine store). And that won't fly between different users. Perhaps user certs are roaming in the domain, but there's a time lag for these to replicate?
David Chase 681 Reputation points

2020-10-22T13:41:17.683+00:00

This may be related to my problem. I do not even get the opportunity to select the Machine Cert Store (see image attached). When setting up always encrypted I was logged in as Server Administrator.

3 answers

Your answer

Ben Miller (DBAduck) 966 Reputation points

2020-10-22T03:20:26.38+00:00

Do you have the certificate that was created for Always Encrypted on the web server in the Machine Cert store?
Do you have the attribute in your connection string?

The message indicates that you don't have it in the right place for the driver to decrypt things. I am happy to help you get this running.
tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-22T06:38:04.17+00:00

I'm no expert in these things, but to me it looks line the cert it looks for is in the user store (not machine store). And that won't fly between different users. Perhaps user certs are roaming in the domain, but there's a time lag for these to replicate?
David Chase 681 Reputation points

2020-10-22T13:41:17.683+00:00

This may be related to my problem. I do not even get the opportunity to select the Machine Cert Store (see image attached). When setting up always encrypted I was logged in as Server Administrator.

Answer 1

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @David Chase ,

Please try below solution;

1.Run (MMC)
2.Select certificate in snap console.
3.Locate your always encrypted certificate, either My user, or Machine Account
4.Right click-> All tasks-> Manage private key.
5.Add the windows user which is making SQL connection.
6.User could the process user, it can be either IIS or any windows Logon user.

Form this similar thread.

Best regards,
Cathy

If the response is helpful, please click "Accept Answer" and upvote it.

David Chase 681 Reputation points

2020-10-22T16:09:45.643+00:00

When I run MMC it just shows Console Root but there is nothing in it and no options.
Ben Miller (DBAduck) 966 Reputation points

2020-10-22T16:19:13.74+00:00

You have to do File | Add Snapin and then choose Certificates and it will have you choose User or Computer stores.
Then you go to Personal and Certificates.
David Chase 681 Reputation points

2020-10-22T16:31:47.92+00:00

I'm not a network guy but a web programmer. I see the list of certificates but none stand out as one from SQL Server. This whole encryption thing is driving me nuts! If the certs were created when I went through the wizard then why am I not seeing them. Also, still not sure why I don't have an option for "Local Machine" in the store provider or master key source.
David Chase 681 Reputation points

2020-10-22T16:36:47.387+00:00

I found then in MMC but I'm not sure what to do with them.
Ben Miller (DBAduck) 966 Reputation points

2020-10-22T16:37:43.847+00:00

They would be exported and added to the webserver certificate store for LocalMachine.
David Chase 681 Reputation points

2020-10-22T16:46:37.783+00:00

Sorry, I'm still lost. I followed the directions published for "Enabling Always Encrypted" wizard but I did not get an option to select "Local Machine" for the auto generated keys. The documentation says you have to be logged in as Administrator to get that option and I was. Help!!
Ben Miller (DBAduck) 966 Reputation points

2020-10-22T16:48:08.557+00:00

Let me see if I can work up a simple comprehensive step guide for you. I do it in a presentation of SQL Encryption. I will pull out my demo and share it here.
David Chase 681 Reputation points

2020-10-22T19:08:32.217+00:00

That would seem helpful. The SQL Server and Web Server are on the same physical computer, if that helps. It sounds like I need to do some work after encryption is setup from the wizard. I did not read anything like that so I just assume that the CMK and CEK were created and stored somewhere that the web application would just need the addition to the connect string.
tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-23T09:45:27.243+00:00

As for creating the CMK pointing to the local machine, how about trying that specific part using a different GUI than the one you're using? I'm thinking that it can be a GUI issue/restriction. The GUI I'm thinking of is the security folder in the database, Always Encrypted Keys, Column Master Keys. This allow at least me to View, select and generate local machine certs.
David Chase 681 Reputation points

2020-10-23T13:33:51.593+00:00

When I right click I do not get the option to "Manage private key". Also, there are Always Encrypted Auto Certificate1, 2, 3, 4 in there that were probably created in the wizard. If I go back to re-create the encrypted columns can I just delete all of these on my test server? There are certificate folders for (Local Computer) and separate set for Current User. Is there any other way to have the certificates be created in the (Local Computer) certificates because I think that would solve my problem?

Answer 2

Ben Miller (DBAduck) 966

Here is a pretty comprehensive setup of Always Encrypted. I think that the piece you are missing may be the certificate that needs to be put on the web server which completes the encryption. If you have the Column Master Key and the Column Encryption key and all these are good (your graphic is about those), then you need the certificate that is used to communicate with these keys.

Take a look. configure-always-encrypted-sql-server-2016-using-ssms-powershell-t-sql

David Chase 681 Reputation points

2020-10-22T15:00:59.17+00:00

I thought the certificate was created as part of the encryption setup? As you can see there is no option for a master key from any other source than "Current user".
Ben Miller (DBAduck) 966 Reputation points

2020-10-22T15:13:32.787+00:00

Just the Master Key and the Column Encryption key
David Chase 681 Reputation points

2020-10-22T16:00:55.013+00:00

What other key is there?
David Chase 681 Reputation points

2020-10-23T14:54:21.6+00:00

Another question for you as nobody has succeeded in getting this resolved. The web application uses a connection string for an SQL Server User and not a Windows account. This is because the production application is being run on a hosted company, not one owned by us. Does this make a difference in how we setup Always Encrypted in SSMS?
Ben Miller (DBAduck) 966 Reputation points

2020-10-23T15:02:06.437+00:00

I am still working on the simpler explanation, but SQL authentication will not cause problems as the connection string is outside SQL Servers control. That is merely authentication. So you are good. No changes in SSMS setup.

Answer 3

David Chase 681

One issue I found is that when I log into the server as the machine administrator I get the option for a machine master key source but not if I log in as the domain administrator.

tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-26T15:04:46.13+00:00

Talk to your windows people. Perhaps domain admin isn't a local admin?

Share via

Decrypt error

3 answers

Your answer