End-to-End Azure SQL Automation with Bicep and DevOps Pipelines

Introduction

This post is a follow-up to my recent session on Microsoft's Zero to Hero series, in this session, I walk you through practical techniques for migrating SQL databases to Microsoft Fabric, using tools like Bicep, YAML pipelines, and SqlPackage.exe

MSFT Zero to Hero - SQL Meets Fabric

Last Friday, I had the pleasure of presenting a live session as part of the Microsoft Zero to Hero series, focused on a topic close to my heart: modernizing SQL workloads in Microsoft Fabric without starting from scratch.

TechTacoFridayHector Sven

At the time, I shared the pipeline I use to deploy a DACPAC to a Fabric-compatible SQL target. I also posted this open invitation

In this branch, I'm only including the YAML pipeline I'm using to execute the BACPAC. Would you be interested in having a full solution including the Bicep code to create a SQL logical server?
👉 Subscribe and let me know in the comments section.

Well — you responded! Many of you asked for the full solution, and I got to work... only to run into a pair of issues that changed the scope of this follow-up article entirely.

Setting the Stage (This One's a Bit Longer)

Normally, this section would just recap what we're building — but because I hit two very real, very impactful roadblocks in this workflow, I’ve broken this section into two parts to tell the full story

Unexpected Problem #1: SQL Admin Password Keeps Changing

Once I added Bicep to provision the SQL logical server and database, I included an adminPassword parameter marked with @secure() decorator

What I didn’t realize was that every time the pipeline run, sys admin password value changed 😬, even when I provided the exact same password value from Key Vault — no changes, no rotation, same secret

This is by design in ARM's secure string handling — but it was the first red flag in an otherwise clean DevOps setup.

Unexpected Problem #2: Get-AzAccessToken stopped working

Previously, my BACPAC/DACPAC deployment step used Azure AD authentication with a service principal. It pulled an access token like this:

For months, it worked, but somewhere after May 16, it stopped. The token still issued — but SQL Server no longer accepted it. No useful logs, just broken pipelines.

This forced me to fall back to SQL authentication using the admin login and password — which, thanks to the problem above, had been reset without me knowing.

What started as a small bonus ("I’ll just share the Bicep too!") turned into a freaking mess 😤 ... in the next section I'm showing you the fix! 😅

Solution

After a lot of troubleshooting, here’s the solution that works

Bear in mind the following notes:

• Whenever possible, I'm going to use the same names I'm using to match the code, but remember that some Azure resources, like SQL logical servers require globally unique names, therefore, be sure to adjust those names to avoid name collisions.
• I'm not going to explain all the code, just the relevant parts and the solutions to the issues outlined on the Setting the Stage section.
• If you need additional details on the pipeline and DevOps in general, check my series on this here

The Anatomy of a Resilient Azure DevOps YAML Pipeline (Series)

This is a series of 3 articles: Part 1 to provide guidance to get Azure & DevOps accounts, Part 2 explains how to work with code repositories and my suggested branching strategy, and Part 3 I explain the code behind the YAML pipeline and how to use it to achieve a multi-environment deployment.

TechTacoFridayHector Sven

Pre-requisites

1 - Create a new Entra ID App registration my.srvprincipal take note of its Application ID from Overview, also create a new secret for the application from Certificates & secrets and take note of it as well; from the API permission, grant application permission User.Read from Microsoft Graph.

2 - Create a new resource group and grant Contributor role to my.srvprincipal

3 - Create Security Group dbserver-sql-administrators , add my.srvprincipal as member of the group, take note of the group's Object Id from Properties.

4 - Create a key vault name_of_company-shared-d-kv-ne , grant Key Vault Secrets Officer to my.srvprincipal additionally, add a new secret scrt-entraid-automation-srvprincipal-password , its value should be the application's secret (pre-requisite#1), another secret necessary is scrt-azure-storage-bacpac-sas, its value should be the shared access signature (SAS) token of the BACPAC

5 - Create a new DevOps Azure Resource Manager service connection, choose App registration or managed identity (manual) as Identity type and Secret as Credential, use my.srvprincipal application Id and & secret (pre-requisite#1), name the connection azdo-serviceconnection-arm-d2

Fixing Problem #1. Setting SQL Admin Password

First to understand is that you can choose to create your SQL logic server with Mix authentication (SQL & Entra ID) or Entra ID ONLY, for this particular case, I'm using mix, therefore, set the parameter azADOnlyAuthentication bool = false (1), the BICEP file by default uses Entra ID ONLY (2)

Adjust the values in the file variables-d.yml , use the security group's Object Id (pre-requisite #3) for variable azSqlServerAADAdminSid

In this task, I first try to get the secret stored in Key Vault (1), is it does not exist, I assigned a 24 random password (2) and make it available as a variable (3)

In the ARM Template Deployment task, I'm using the variable $sysAdminPsw (1)

Anticipating a password reset after deployment, I set the qlAdministratorPassword again (2) 😅

Needless to say, that I'm not a big fan of this, but, while I find a better solution, this will have to work ... if you know a different way, open to suggestions 😉🤞

Fixing Problem #2. Use Get-MsalToken to generate Auth. tokens

To work around the broken service principal login, I replaced Get-AzAccessToken with Get-MsalToken from PowerShell module MSAL.PS to generate a valid AAD token.

$result = Get-MsalToken -RedirectUri $uri -ClientId $userAccount -ClientSecret (ConvertTo-SecureString "$targetPsw" -AsPlainText -Force) -TenantId $tenantId -Scopes $tokenResourceUrl
$accessToken = $result.AccessToken 

It works — but there’s a catch: MSAL.PS is not pre-installed on DevOps agents 😒, I need to install and import the module on each pipeline run, this slows down the pipeline — but at least it works 😮‍💨

Conclusion & Call to Action

This post started with a simple promise: I’d share the Bicep code behind the demo. But like many real-world devops stories, that path led to a much more interesting problem — and a valuable workaround.

I don’t love having to patch things with PowerShell post-deployment. It feels like a step backward. I’ll be reaching out to Microsoft to better understand:

• Why ARM doesn’t support secure string comparison.
• Why the original AAD login method broke.

Until then — this workaround holds and, as Deng Xiaoping, (architect of modern China) quoted "it doesn't matter if a cat is black or yellow, as long as it catches mice" 😉🤞

Have you seen this issue in your pipelines? Found a better way? Drop a comment, share your repo, and let’s make repeatable SQL deployments something we don’t have to think twice about