1

I'm hitting an issue when trying to test a legacy classic ASP application against a SQL Server 2016 Always Encrypted column.

This is a sample of the ASP code:

conn.ConnectionString = "Driver={ODBC Driver 13 for SQL Server};Server {myserver};UID=myuser;pwd=mypw;Database=test;ColumnEncryption=Enabled;"
conn.open

Dim sql
sql = "SELECT TOP 10 [text]  FROM TEST"

rs.open sql, conn

The column 'text' is encrypted. When it executes, I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Certificate in key 
path 'CurrentUser/My/704F32193389CACD95E102DA881006F33936C6DE' not found.

I've checked the certificate store, and it's there:

Certificate store

Certificate Path Tab

If I switch the driver version to 11, I get a type mismatch error instead, which I think is because v11 doesn't support Always Encrypted, so just ignores it.

Any help will be much appreciated!

FrugalShaun
  • 166
  • 7
  • 1
    It seems the certificate is installed in the current user cert store and the ASP app is running under a different account. Try importing the CMK certificate in the local machine store as described [in this documentation](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-store-column-master-keys-always-encrypted) so that it's available to all users. – Dan Guzman Mar 06 '18 at 11:19
  • I've exported the certificate (including the private key) and imported it into Local Computer\Personal\Certificates. Still get the same error. The error message would seem to suggest that ASP is looking for the certificate in the current users store, but not finding it for some reason. – FrugalShaun Mar 06 '18 at 13:51
  • Could you pls add a screenshot with the active Certification Path tab? – andrews Mar 06 '18 at 14:12
  • Added above. Thanks – FrugalShaun Mar 06 '18 at 20:38

1 Answers1

1

Managed to sort it. Turned out the problem was permissions related.

Go to Certificates - Local Computer \ Personal \ Certificates. Right click on the certificate, click 'Manage Private Keys'. Give full access to 'Everyone'.

This fixes it, although obviously in production you should grant access to the IIS AppPool user, and not 'everyone'.

FrugalShaun
  • 166
  • 7