C# console app for SQL Server Always encrypted operations - Azure Key Vault CMK with cert authentication

Question

I want my C# console app to be able to perform SQL DML against Always-Encrypted columns in on-prem database using column master key in Azure Key Vault, and I want to Authenticate using AppId and certificate stored in my local certificate store. I already registered the App and Service Principal, configured the cert, and assigned the permissions in Azure CLI. I have the encryption set up, as verified in SQL Mgmt Studio.

I was able to use ODBC driver to connect and execute SQL https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=sql-server-2017 using both userid/pwd and interactive modes with something like this connection string:

Driver={ODBC Driver 17 for SQL Server};server={instanceName};uid={myUid};trusted_connection=Yes;database={myDBName};columnencryption=Enabled;keystoreauthentication=KeyVaultPassword;keystoreprincipalid={MyADUserId};KeyStoreSecret={MyADPassword}

Using ODBC, there is also the option to connect with AppId/Secret in connect string, but I don't want to insert those into my app.

The last option thru ODBC is ManagedIdentity but I don't think I can use that from on-prem... not sure.

That's why I decided upon AppId + Cert for authentication.

As POC, I can connect to the AKV and see the column master key like this:

X509Certificate2 cert;
string tenantId = "{my tenant id}";
string appId = "{my app id}";
string keyvaultURI = "https://mykeyvault.vault.azure.net/";

store.Open(OpenFlags.ReadOnly);
var certs = store.Certificates;

var certFound = certs.Find(X509FindType.FindBySubjectDistinguishedName, "CN={myCertSubject}",    false).OfType<X509Certificate2>();
                
if (certFound.Count() > 0)
{
   cert = certFound.Single();
   var credential = new ClientCertificateCredential(tenantId, appId, cert);

   var client = new KeyClient(new Uri(keyvaultURI), credential);
   KeyVaultKey key = client.GetKey("MyColumnMasterKey");
   Console.WriteLine(key.Name);
   Console.WriteLine(key.KeyType);

The above works, though I'm not sure I'm on the right track. What I ultimately want to do is, using Microsoft.Data.SqlClient, instantiate a SQLCommand and then connect it to the AzureKeyVault for the CMK. I don't know how to retrieve the token, though, since I need to pass in some kind of struct called "TokenRequestContext." Supposedly, I can retrieve the token from the credential I got above and connect that to the SQL column encryption using something like this:

var token = credential.GetToken( t ); // t is "TokenRequestContext"...  what is that???
var x = new SqlColumnEncryptionAzureKeyVaultProvider(token); // pass credentialToken

So, I can authenticate, but not quite in the way I need in order to make the jump from auth to using that in SQLCommand.

Does it look like I am on the right track here? How can I get from the authenticated ClientCertificateCredential to the token?

Thanks for any help.

Answer 1

• Use Azure.Identity to create a token credential (DefaultAzureCredential), which simplifies token acquisition.

• Register the Azure Key Vault provider for Always Encrypted using the SqlColumnEncryptionAzureKeyVaultProvider with the tokenCredential.

• using below code i achevied authenticate application using the certificate and access the column master key from Azure Key Vault to enable Always Encrypted operations on your SQL Server database.

Here's the corrected code

using System;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using Azure.Identity;
using Azure.Security.KeyVault.Keys;

class Program
{
    static async Task Main(string[] args)
    {

       string keyVaultUrl = configuration["AzureKeyVaultUrl"];
       string columnMasterKeyName = configuration["ColumnMasterKeyName"];

        // Use Azure.Identity to create a token credential
        var tokenCredential = new DefaultAzureCredential();

        // Register the Azure Key Vault provider for Always Encrypted
        SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(tokenCredential);

        Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
        providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);

        // register the provider with ADO.net
        SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

        string connectionString = "Server=localhost;Database=master;Trusted_Connection=True;TrustServerCertificate=True";
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Perform SQL DML operations using Always Encrypted columns
            // Example: Insert, Update, Select with encrypted columns

            connection.Close();
        }
        Console.WriteLine("Accessed ");
    }
}

Result