2

I'm trying to deploy a dacpac to an Azure Sql Database with Always encrypted enabled. The Devops agent is running in a self-hosted VM with sqlpackage.exe version 19 with build 16.0.5400.1 installed on it.

I've been able to trace down the issues by adding /diagnostics as an argument to the task and the exception that is raised is:

Unexpected exception executing KeyVault extension 'Object reference not set to an instance of an object.' at Microsoft.SqlServer.Dac.KeyVault.DacKeyVaultAuthenticator.Validate(IList`1 keyVaultUrls, CancellationToken cancelToken)

Anybody have a suggestion on how to solve this?

FinneVirta
  • 374
  • 1
  • 4
  • 14

2 Answers2

0

Please check below points

  1. Microsoft.SqlServer.Dac.KeyVault.DacKeyVaultService Provides a service for discovering and configuring a Microsoft.SqlServer.Dac.KeyVault.KeyVaultAuthenticator to handle key vault access requests. These requests will occur during deployment if an encrypted table is being altered. It also supports initialization of general key vault support in an application

  2. If you store your column master keys in a key vault and you are using access policies for authorization:

    Your application's identity needs the following access policy permissions on the key vault: get, unwrapKey, and verify.

    A user managing keys for Always Encrypted needs the following access policy permissions on the key vault: create, get, list, sign, unwrapKey, wrapKey, verify.

SEE Create & store column master keys for Always Encrypted - SQL Server | Microsoft Docs

3.

To publish DAC package if Always Encrypted is set up in the DACPAC or/and in the target database, you might need some or all of the below permissions, depending on the differences between the schema in the DACPAC and the target database schema.

ALTER ANY COLUMN MASTER KEY, ALTER ANY COLUMN ENCRYPTION KEY, VIEW ANY COLUMN > MASTER KEY DEFINITION, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION

we need to enable that Azure virtual machine check box

enter image description here

References:

  1. Configure column encryption using Always Encrypted with a DAC package - SQL Server | Microsoft Docs
  2. azure-sql-advanced-deployment-part4.
  3. KeyVaultAuthenticator.Validate(IList, CancellationToken) >> Microsoft.SqlServer.Dac.KeyVault Namespace | Microsoft Docs
kavyaS
  • 8,026
  • 1
  • 7
  • 19
  • Okay, now I've tried ticking the "Azure Virtual Machines for deployyment" box, otherwise I had it all configured. Still getting the same error though. – FinneVirta Feb 16 '22 at 15:22
  • If you have turned on managed identity to work with azure active directory [this way](https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql#:~:text=To%20grant%20your%20VM%20access%20to%20a%20database,and%20Azure%20PowerShell%20in%20the%20Azure%20SQL%20documentation.) , Please check if the user or group is given admin rights set i.e; full access rights . After changes made,Please restart and try again.Also make sure to have latest versions of sdks . – kavyaS Feb 16 '22 at 16:28
0

I managed to find a solution. I downgraded the sqlpackage.exe version. If I understand it correctly apparently version 19 seems to be targeted for SQL Server compatibility level 160 which is shipped with SQL Server 2022. When using version 18 it seems to be working with the current 150 that my Azure DB is set to.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
FinneVirta
  • 374
  • 1
  • 4
  • 14