New-AzSqlDatabaseImport requires a storage account access key, cannot be authenticated through Entra ID

Question

I am using the Az PowerShell command New-AzSqlDatabaseImport to create a fresh Azure SQL test database from a testdb.bacpac as part of my Azure DevOps deployment pipeline.

testdb.bacpac is stored as a blob in a Storage Account, and the command is able to access it using an access key, but I've now decided to disable account key access on the storage account for security reasons.

So, now the only way to access storage is through Entra ID and RBAC, but New-AzSqlDatabaseImport doesn't seem to support that. What other options do I have for importing this bacpac?

Answer 1

Based on your requirement, you may use the az storage blob download command in the AzureCLI@2 pipeline task to download testdb.bacpac file from your storage account into the System.DefaultWorkingDirectory on the pipeline agent and then import the testdb.bacpac with the help of the task SqlAzureDacpacDeployment@1. Here is a sample for your reference.

pool:
  vmImage: windows-latest

steps:
- task: AzureCLI@2
  inputs:
    azureSubscription: '$(ARMSvcCnn)'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
      az storage blob download `
          --account-name $(storageAccountName) `
          --container-name $(containerName) `
          --name testdb.bacpac `
          --file $(System.DefaultWorkingDirectory)\testdb.bacpac `
          --auth-mode login
- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '$(ARMSvcCnn)'
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(serverName).database.azure.com'
    DatabaseName: 'NewDB-$(Build.BuildId)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Import'
    BacpacFile: '$(System.DefaultWorkingDirectory)\testdb.bacpac'
    IpDetectionMethod: 'AutoDetect'

Bothe the AzureCLI@2 task and SqlAzureDacpacDeployment@1 task authenticate against the underlying service principal (with Client Id and secret) which is referenced by the ARM service connection. Therefore, please make sure the service principal has sufficient permissions to access the storage account and to administer the SQL server.

The SqlAzureDacpacDeployment@1 task is basically using SQLPackage tool to import from the BACPAC file.

Hope the information helps.