Update column encryption in background process

113 Views Asked by At

I am attempting to create a PowerShell script (or template, rather) that can be run from an Azure WebJob to dynamically update SQL Always Encrypted column configurations.

E.g., A new [Col1] NVARCHAR(1) NULL column is added to table [Table1] that needs to be encrypted.

Attempt 1 The WebJob's App Service host has a managed identity configured - I understand that it should have permission to access (and modify) the database.

$env:PSModulePath += ";<webjob_dir>"
Import-Module Az.Accounts #2.12.1
Import-Module SqlServer #22.0.59

# Set up SQL connection and database object
$smoDatabase = Get-SqlDatabase -ConnectionString "<connection_string>"

# Authentication
Connect-AzAccount -Identity

# Change encryption schema
$encryptionChanges = @()
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Table1.Col1 -EncryptionType Deterministic -EncryptionKey "<encryption_key>"

# Apply changes
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Attempt 2 I configured an Azure AD app that has permission to access (and modify) the database. This script works if I run it locally with a preceding Connect-AzAccount -Tenant <tenant_id> command.

$env:PSModulePath += ";<webjob_dir>"
Import-Module Az.Accounts #2.12.1
Import-Module SqlServer #22.0.59

# Set up SQL connection and database object
$smoDatabase = Get-SqlDatabase -ConnectionString "<connection_string>"

# Authentication
Add-SqlAzureAuthenticationContext -ClientID <client_id> -Secret '<secret>' -Tenant <tenant_id>

# Change encryption schema
$encryptionChanges = @()
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Table1.Col1 -EncryptionType Deterministic -EncryptionKey "<encryption_key>"

# Apply changes
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

When deployed to the WebJob, both attempts - and everything else I've tried too - result with this error:

Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.

Is what I'm attempting achievable? If so, where am I going wrong?

0

There are 0 best solutions below