CODEWITHSUNDEEP
sql-serverodbcoledbalways-encryptedsqlncli
FrugalShaun
FrugalShaun

Reputation: 176

SQL Server Always Encrypted - Certificate not found (ODBC)

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!

Upvotes: 1

Views: 2074

Answers (1)

FrugalShaun
FrugalShaun

Reputation: 176

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

Upvotes: 1

Related Questions