Sqlclient: Always Encrypted - 'Keyset Does not exist'

[ph1294]

Description of Issue

Always Encrypted cannot find the certificate to decrypt the column keys, although it has been generated automatically using SSMS and placed into the machine certificate store. Feature works correctly in a .NET Framework project with exact same steps followed.

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Failed to decrypt column 'Password'.
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: '2E-60-91-33-22-5C-A2-FA-22-B7'.
Keyset does not exist
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at WebAutomation.DBAccess.GetCreds() in H:\Web Automation\WebAutomation\WebAutomation\DBAccess.cs:line 42
   at WebAutomation.SeleniumSite.get_Creds() in H:\Web Automation\WebAutomation\WebAutomation\SeleniumSite.cs:line 21
   at WebAutomation.SeleniumSite.AccessDRAS(Boolean visualCron) in H:\Web Automation\WebAutomation\WebAutomation\SeleniumSite.cs:line 124
   at WebAutomation.Run.RunSiteReports(SeleniumSite SeleniumSite) in H:\Web Automation\WebAutomation\WebAutomation\Run.cs:line 71
   at WebAutomation.Run.Main(String[] args) in H:\Web Automation\WebAutomation\WebAutomation\Run.cs:line 40

Inner Exception 1:
WindowsCryptographicException: Keyset does not exist

To reproduce

Access a SQL 2017 named instance Database in a .NET Core application which uses the Always Encrypted Drivers.

```c#
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Microsoft.Data.SqlClient;
using System.Data;

namespace WebAutomation
{
class DBAccess
{
private readonly string connectionString = "Data Source={FQDN}\{INSTANCE};Initial Catalog=Operations;Persist Security Info=True;User ID=Selenium;Password=***;Column Encryption Setting=Enabled";
private readonly string queryString = "SELECT * FROM selenium.Credentials WHERE [Automator] = @a";

    private string Automator { get; set; }

    public DBAccess(string automator)
    {
        Automator = automator;
    }

    /// <summary>
    /// Gets credentials for a specific automtaor from the database.
    /// </summary>
    /// <returns>A datatable containing decrypted credentials.</returns>
    internal DataTable GetCreds()
    {
        using(SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@a", Automator);
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable ret = new DataTable();
            try
            {
                adapter.Fill(ret);
                return ret;
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                adapter.Dispose();
            }
        }
    }
}

}
```

Expected behavior

Should connect to data source and automatically decrypt data.

Further technical details

Microsoft.Data.SqlClient version: 1.0.19249.1
.NET target: Core 2.1.4
SQL Server version: SQL Server 2017
Operating system: Windows 10 1903

Additional context
I am confident that the client is at least trying to implement always encrypted, because the error varies from the 'not implemented' type of message thrown by System.Data.SqlClient.

[ph1294]

If I had to hazard a guess, it isn't properly reading the meta data associated with the location of the Always Encrypted certificate. I'll see if it's possible for me to take a closer look myself, and when I can I'll share my findings, if any.

[karinazhou]

Hi @ph1294 ,

I tried it with Microsoft.Data.SqlClient v1.0.19249.1 in both .net core and .net framework towards SQL Server 2017. However, I could not reproduce the issue from the code snippet you provided. Neither throws the exception as described above. The actual trigger for this exception could be in other layers from your call stack.

Is there any standalone application you have to reproduce the issue?

Thanks!

[ph1294]

hey @karinazhou, I just want to confirm with you because I didn't actually specify in the bug report:

Are you encrypting any of the columns in the table you are querying? Because the connection string and library works fine when none of the columns are encrypted, but will fail with that error when one of the columns are encrypted using SQL Always Encrypted.

I do not have a standalone application that reproduces the error at this time, but can try to get one together for you if that will help with troubleshooting.

Thanks!

[karinazhou]

@ph1294 Yes, I encrypted one of the columns (e.g. [credential]) from SSMS and run the SELECT * query on the encrypted table. I only tested the GetCreds() method from your example and didn't get any exception.

This is what I have for testing:

static void Main(string[] args)
{
    string connectionString = "Data Source=localhost;Initial Catalog=test;Persist Security Info=True;User ID=xx;Password=yyy;Column Encryption Setting=Enabled";
    string queryString = "SELECT * FROM customer_data WHERE [id] = @a";
    int id = 2;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Parameters.AddWithValue("@a", id);
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable ret = new DataTable();

        try
        {
            adapter.Fill(ret);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            adapter.Dispose();
        }
    }
}

[ph1294]

OK, I have a production table with a known working AE keyset in use by a .NET Framework Site, I will access that directly with .NET Core and get back to you with the results.

[ph1294]

I've tested connecting to the production table from .NET Core and it could not decrypt the prod table with keys installed on my machine, however I can decrypt the prod and the test tables from SSMS on my machine with always encrypted enabled.

I will see if I can provide something standalone.

I'm admittedly new to all this. Is it possible to step through the source of Microsoft.Data.SqlClient from gitlab on my machine while it's running this code? If so, could you point me in the direction of documentation that may help me do so? Thanks!

[karinazhou]

@ph1294
If you would like to build the driver locally, you can clone the sqlclient repository to your machine, go to the root directory of your repository and use msbuild command to build the driver.

For example,
C:\MyRepo\KarinaSqlClient>msbuld /t:BuildAllConfigurations --> build debug version
C:\MyRepo\KarinaSqlClient>msbuld /t:BuildAllConfigurations /p:Configuration=Release --> build release version

You can find all the commands in BUILDGUIDE.md .

The locally generated NuGet package (Microsoft.Data.SqlClient.1.1.0-dev.nupkg or Microsoft.Data.SqlClient.1.1.0-dev-debug.nupkg) will be in your_repo\packages folder. You can manage NuGet packages for your testing application to use the local driver. The local NuGet package contains the pdb files which enables you to debug the code.

Usually, all the NuGet cache are in C:\Users\your_username\.nuget\packages\ by default. In case of inconsistency of the cache, you can manually delete the Microsoft.Data.SqlClient package there and install it again from Visual Studio.

[ph1294]

This was a case of configuration error. It was a problem with the level of access of the context that Visual Studio was being executed in. Specifically, the user account running Visual Studio did not have access to the store which contained the certificate. Once run with elevated privileges (as the environment should have been), the issue was resolved.

How embarrassing! 😨

Thank you very much for the help.