I removed a hardcoded SQL admin password from a production-style Azure platform, which is exactly what you’re supposed to do. What followed was a week of HTTP 500s, a CI pipeline that reported success while doing nothing, a binary identifier that refused to be computed, and a database error message referencing a database with no name.

This is the write-up I wish had existed when I started. Every dead end is preserved, because the dead ends are where the learning was. The platform is public — azure-telemetry-platform — and it’s the same system I later ported to Google Cloud in a day, but that’s another post.

The setup

The platform is a real-time vehicle telemetry system on Azure — App Service API, Azure Functions for ingestion, Azure SQL Serverless, all provisioned with Terraform and deployed via GitHub Actions. The last piece of hardening was moving SQL authentication to Entra ID Managed Identity: no passwords in the application layer at all. Terraform made the control-plane change cleanly. The app deployed.

And then every database query returned HTTP 500.

The identities existed. The apps were authenticating. But authentication isn’t authorization — the Managed Identities had no data-plane mapping inside the database itself. No CREATE USER, no role membership, no access. Fair enough: I added the mapping statements to the schema init script that runs in the deployment pipeline, watched the workflow go green, and re-tested.

Still 500s.

Dead end #1: the green checkmark that meant nothing

The pipeline said success. The database said the users didn’t exist. One of them was lying.

It turned out to be both, in a way. The azure/sql-action step was still authenticating with the legacy sqladmin fallback credentials — and a traditional SQL login is prohibited from creating Entra ID users (CREATE USER ... FROM EXTERNAL PROVIDER requires an Entra-authenticated principal). So the statement failed every run.

Why was the workflow green? Because sqlcmd, which underpins the action, swallows script execution errors by default. The step ran, the statement failed, the exit code said fine, and GitHub Actions painted a checkmark over an unsecured database.

Two fixes, both of which I’d now consider mandatory hygiene for any SQL-in-CI setup:

1. Authenticate the pipeline itself with Entra ID. I changed the Terraform SQL module to output an Authentication=Active Directory Default connection string, so the sql-action step authenticates through the federated GitHub service principal — a principal that’s actually allowed to create external users.
2. SET XACT_ABORT ON at the top of every deployment script. Any statement failure aborts the transaction and surfaces a fatal exit code to the runner. The pipeline is no longer capable of lying about this class of failure.

That second one is the transferable lesson: your tools’ default error handling is a production risk. Audit what “success” means to every step in your pipeline.

Dead end #2: the SID that couldn’t be computed

New problem. In locked-down tenants, the deployment principal often lacks Directory.Read.All — and without directory read access, the SQL engine can’t resolve identity names during CREATE USER ... FROM EXTERNAL PROVIDER. Login failed, again.

The commonly documented workaround is “offline SID mapping”: compute the binary SID from the identity’s Object ID and create the user with an explicit SID = 0x... clause, no directory lookup needed. Every blog post makes it look like a simple endianness conversion.

It isn’t. Managed Identity SIDs in Azure SQL are not a byte-reordering of the principal’s Object ID — they’re internally assigned binary values from Entra ID. I burned real hours trying conversion permutations before accepting that the input I was converting simply didn’t contain the answer.

The value does exist in two places, though. It’s embedded (base64) in the service principal’s servicePrincipalNames metadata, and — more directly — it’s sitting in sys.database_principals once a mapping exists anywhere. Using a whitelisted Entra admin context, I queried the authoritative hex SIDs straight out of the database and hardcoded them into the init script.

Service restored. Zero-trust intact. And a fresh piece of technical debt, knowingly taken: hardcoded SIDs are ghost identities waiting to happen. Destroy and re-apply the Terraform, and Azure mints new identities with new SIDs; the script would map users to principals that no longer exist. I flagged it in the doc with a warning box the moment I wrote it, because debt you don’t label becomes debt you forget.

The database called ""

With users mapped, ingestion still failed — now with Error 916: the server principal is not able to access the database "" under the current security context.

A database with an empty string for a name is a wonderful clue once you know how to read it. Managed Identities in Azure SQL are contained database users — they exist inside the user database and nowhere else, including master. The sql-action step was connecting to the server without pinning the session to TelemetryDb, so it landed in master, where my identity had no existence, and the catalog transition produced that eerie empty name.

Same class of bug, one layer down: the .NET ingestion service was handing SqlBulkCopy a connection string and letting the driver sort out timing. Explicitly await conn.OpenAsync() before initializing the bulk copy, so the Managed Identity token negotiation and Initial Catalog binding complete deterministically first.

Lesson: with contained users, catalog context is part of authentication, not an afterthought. Pin the database everywhere — CI steps, connection handling, all of it.

Paying down the debt

The endgame was getting the hardcoded SIDs out of source while keeping the stack deployable without manual surgery. The textbook path is native resolution — CREATE USER [...] FROM EXTERNAL PROVIDER with identity names passed from Terraform outputs — but native resolution requires the SQL Server’s identity to hold the Directory Readers role, and my first attempt automated that role assignment in Terraform. The pipeline promptly failed with a Graph API 403: assigning directory roles is a tenant-level, high-privilege operation, and granting the deployment principal RoleManagement.ReadWrite.Directory just to automate a one-time step is a terrible trade.

My first response was a call I think is underrated in IaC culture: move it out of automation on purpose. The role assignment became a documented one-time prerequisite for a tenant admin — removing the azuread provider dependency made the pipeline more reliable than automating it would have.

But sitting with it a while longer produced a better answer: delete the dependency entirely. The final script keeps SID-based mapping — the thing that never needed directory permissions in the first place — but parameterizes it: CREATE USER [${APP_NAME}] WITH SID = ${APP_SID}, TYPE = E, with names and SIDs flowing in as deployment variables, and a drop-and-recreate pattern for idempotency. No hex literals in source. No Directory Readers role. No Graph permissions anywhere in the pipeline. XACT_ABORT ON still standing guard at the top.

And here the SID mystery finally gave up its real answer. Remember the failed offline computation — the object ID that refused every byte-order permutation? It turns out the client ID of a managed identity does convert deterministically (a little-endian GUID-to-binary conversion), and the reason my earlier attempts failed is that I was converting the wrong identifier. One catch: cleanly getting client IDs without Graph API calls meant migrating from system-assigned to user-assigned managed identities, whose client IDs are first-class Terraform outputs. With that in place, the deployment workflow computes both SIDs at deploy time — uuid.UUID(client_id).bytes_le.hex() — renders them into the script, and the whole stack survives terraform destroy && terraform apply with zero manual steps. The value I once extracted surgically from sys.database_principals is now derived, on every deploy, from infrastructure the pipeline already owns. Between “grant tenant-level directory access,” “hardcode fragile binary values,” and “make the value computable from inputs you control,” the third option wasn’t on the menu until I’d fully understood why the first two were bad.

What I’d tell you to steal

1. Make every pipeline step incapable of lying. XACT_ABORT ON, explicit exit-code checks, and skepticism toward any tool’s default error handling.
2. Restore first, refactor second — but label the debt in the same commit. The hardcoded SIDs were the right emergency move because the warning box shipped with them.
3. Contained users mean catalog context is authentication. Pin the database explicitly at every layer.
4. First make the risky step manual; then see if you can delete it. A documented one-time admin action beats the permissions required to automate it — and sometimes staring at the manual step reveals it was never needed at all.
5. When a computed value won’t compute, question the input before the method. The object ID never contained the answer; the client ID did — but only after migrating to user-assigned identities made it a clean deploy-time fact. In between, the authoritative source (sys.database_principals) was queryable the whole time, and querying it kept production alive while I figured that out.

A note on method: I worked through this with heavy AI assistance — for hypothesis generation, for decoding the SID structure attempts, for the T-SQL and YAML mechanics. What AI didn’t do was decide when to stop converting bytes and go query the source of truth, or when to pull role management out of the pipeline entirely. That division of labor — AI for velocity, judgment for direction — is how I work now, and this problem is a decent case study in why it’s faster than either alone.

The raw engineering notes behind this post live in the repo: docs/ci-deployment-learnings.md, alongside the SLO definitions and ADRs.