1

I have a Azure SQL DB with Always encrypted table and keys stored in Azure KeyVault. I wish to insert data into the "always encrypted" table using Powershell.

I understand the Invoke-sqlcmd does not support and I am using the SqlCommand.ExecuteNonQuery Method () to achieve the same

    $connStr = "Server={0};Database={1};User ID={2};Password={3};Column Encryption Setting=enabled;" -f $SqlDBServerInstance, $SqlConnectionInfo.DatabaseName, $SqlConnectionInfo.UserName, $SqlConnectionInfo.Password
    
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = $connStr
    $sqlConn.Open()
    $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlcmd.Connection = $sqlConn    
    $sqlcmd.CommandText = "INSERT INTO dbo.SecureTable (Column1) VALUES (@Param1)"
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param1", [Data.SQLDBType]::VarChar,50)))
    $sqlcmd.Parameters["@Param1"].Value = "$Param1"
    
    $sqlcmd.ExecuteNonQuery();

When I run this code I get below error even though I have right cryptographic access (Unwrap, Wrap, Verify, Sign) given to the service principal at key vault level in azure

Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote 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'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly."

The way described here does not work for me probably due to I am using Key Vault to store my keys.

To resolve this error, there is one step needed from C# to use InitializeAzureKeyVaultProvider but since I am trying to achieve this from Powershell and SqlServer PS module only, is there any way I can achieve this using Powershell only? Without going the C# way explained here

Harsh
  • 149
  • 3
  • 14
  • You do not need to add the key in code. You can always load the key using the Internet Options in your browser. It is only done once. – jdweng Sep 17 '20 at 14:17
  • sorry @jdweng, did not fully understand. _internet options in your browser_? – Harsh Sep 17 '20 at 14:23
  • For IE : Tools : Internet Options : Content. The keys are in the certificate. – jdweng Sep 17 '20 at 14:31
  • I think jdweng's referring to the [Windows Certificate Store](https://learn.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-certificate-store-configure) article, which is sibling to your Azure Key Vault link. I believe the wizard there is creating the CMK and CEK as X509.2 certificates in your Personal Certificate store, which you can also manipulate via the Security settings in Internet Options, but is different to Azure Key Vault. – AlwaysLearning Sep 17 '20 at 14:35
  • Okay, Thanks @AlwaysLearning, but we are using KeyVaults to manage the keys and not Windows Certificate Store, so I believe we need some different solution to initializeAzureKeyvault provider and insert the data in encrypted table – Harsh Sep 18 '20 at 05:21
  • I've not tried it from PowerShell, sorry, but I'd imagine you're going to have to start with `Add-Type 'Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.dll'`, initialize the provider, and apply `$sqlConn.ColumnEncryptionSetting = [SqlConnectionColumnEncryptionSetting]::Enabled` just like the C# code does. I don't imagine there's going to be a pure PowerShell solution for this unless you crack open .NET Reflector to see what AzureKeyVaultProvider is doing behind the scenes and implement it. – AlwaysLearning Sep 18 '20 at 07:19

0 Answers0