I have a local SQL server database with Always Encrypted columns, and a master key stored in Azure Key Vault. I can easily perform CRUD operations with a .Net web application but I have a requirement to modify (insert/update) data via powershell. I have spent a number of days investigating this but I can’t get it working. There are a couple of SO posts from a few years ago (Unable to insert into an always encrypted table using PowerShell and Updating T-SQL always encrypted column with PowerShell), but I haven’t been to get updates/inserts working with AKV.

Can anyone help with an example of how to do this in powershell please?

Invoke-sqlcmd has a KeyVaultAccessToken switch, so while I can pass an Azure access token and perform SELECT queries, it doesn’t support parameterized queries so inserts/updates are not possible. I’ve tried invoke-dbaquery from dbatools, this supports parameterized queries but I can’t figure out how to get this to work with AKV. I’ve also tried using the Microsoft.Data.SqlClient as suggested in the linked posts above, but again I can’t figure out how to initialise and register the AKV, and how to pass/use the access token. I get the following error from both Ddbatools and Microsoft.Data.SqlClient when attempting an insert/update query. I understand the error but don’t know how to resolve it.

Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must de
note either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'