Solved: LinqPad 5 with SQL Server Always Encrypted
In case anybody else is considering using the Always Encrypted feature of SQL Server, and Azure SQL Database, 2012 - and using the "KeyVault" solution for managing the Column Master Key(s): here's how to make it work in LinqPad 5:
Regards,
Fritz
- be sure that you have .Net 4.6.2 installed on your machine
- Edit the LinqPad config files (LINQPad.exe.config and LPRun.exe.config)
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2" /> <!-- version is probably 4.6 by default --> </startup>- 4.6.2 is required in order to have all driver supported features actually work.
- Set up AE on your database and stash your keys in a key vault (see here)
- In your connection propertied, click "Advanced..." and add this to the "Additional connection string parameters": "Column Encryption Setting=Enabled" (this is the flag to enable AE in the SqlServer driver)
- Go to the "My Extensions" code block and add the following to the static MyExtensions class:
public static class MyExtensions { private static ClientCredential _clientCredential; private static bool initialized = false; public static void InitializeAzureKeyVaultProvider() { if(initialized) return; _clientCredential = new ClientCredential( "YOUR CLIENT ID", "YOUR SECRET KEY"); SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken); Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(); providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider); SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers); initialized = true; } public async static Task<string> GetToken(string authority, string resource, string scope) { var authContext = new AuthenticationContext(authority); AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential); if (result == null) throw new InvalidOperationException("Failed to obtain the access token"); return result.AccessToken; } } - Add references via F4
- from NuGet: Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider
- from NuGet: Microsoft.IdentityModel.Clients.ActiveDirectory
- Additional References: System.Threading.dll
- Additional References: System.Threading.Tasks.dll
- Run InitializeAzureKeyVaultProvider() either from a code editor or via the "My Extensions" main
- Query as if nothing were ever encrypted secure in the knowledge that your data is stored and transferred in cyphertext and is decrypted only once it's within the driver
Regards,
Fritz