Why my app user couldn't find always encrypted certificate?

Question

Using SQL Server 2017 I followed this article to apply Always Encrypt tech: https://www.codeproject.com/Articles/1110564/Always-Encrypted-feature-in-SQL-Server Which is simply says:

1. Create Column Master key.
2. Create Column Encryption key.
3. Encrypt the columns using the latter.

The certificate that being used for step 1. was generated using the wizard and saved in:
Key Store: Windows Certificate Store - Local Machine

I created very simple console app that is using EF to try to get data from the table that I encrypted the column in.

I added to connection string this: column encryption setting=Enabled.

When I try to get the data:

    using (MyEntities en = new MyEntities())
    {
        var x = en.TableHasColEnc.ToList();
    }

I get this error:

'Failed to decrypt column 'X'. 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'. Certificate with thumbprint '...' not found in certificate store 'My' in certificate location 'LocalMachine'. 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'

What I got that the user that is running this app is not able to access that location to get the certificate, but why ?
If I ran the app as Admin it'll work normally.

I opened mmc and checked Certificates (Local Computer) and found the certificate that has been used in step 1. and it looks like it's correctly in local machine not current user.

Why I'm getting this error and how to solve it ?

Answer 1

This answer helped me to solve the problem:
How to give ASP.NET access to a private key in a certificate in the certificate store?

The idea is to grant the user who's gonna run the app (or in case of web app the IIS application pool user) the permission to be able to read private keys.

In the correct answer explains how to grant IIS user the permission, you can do the same for who ever is running your app.

UPDATE If your app is on one server and the DB is on another (so your Encryption Certificate most probably on DB's server) you need to export and then import that certificate from DB's server to your app server and give the user who is running your app the permission to use the private key of this certificate.