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.