SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsgithub-actions-for-sql

github-actions-for-sql

Data & analytics

View repository

Updated 10-16 00:32

About this skill

Deploy schema to Azure SQL Database from GitHub Actions

This owns the pipeline: the workflow, the login, the firewall and the gate. Not the project (sql-database-projects), the sequencing of a schema change (schema-migrations-safely), the database-side grant (entra-id-auth), or the application (deploy-app-to-azure).

Checked 2026-09-03 against azure/sql-action at tag v2.4, commit eb1f9a80, still the newest tag; SqlPackage 170.4.83.3; sqlcmd 1.10.0; Azure CLI 2.90.0.

The action is three commands. Run them yourself first

Input Notes
connection-string required. This is where the authentication method is chosen
path required. .sql, .dacpac or .sqlproj. A glob is allowed and must match exactly one file
action required for .dacpac and .sqlproj. Publish, Script, DriftReport, DeployReport, nothing else
arguments appended verbatim to the deployment command line, or to the sqlcmd line for a .sql path
build-arguments appended to dotnet build when the path is a project
skip-firewall-check default false
sqlpackage-path point at a SqlPackage the runner already carries

The action shells out. These are the three command lines it composes, read from src/SqlUtils.ts and src/AzureSqlAction.ts at v2.4. A red run that reproduces on a laptop is not a pipeline problem.

sqlcmd -S <server>.database.windows.net,1433 -d master \
  --authentication-method=ActiveDirectoryDefault \
  -Q "SELECT 'Validating connection from GitHub SQL Action'"

That is the firewall probe, not the deployment, and it runs against master first, the target database second. Then the build, for a .sqlproj path only:

dotnet build "./db/ShopDb.sqlproj" -p:NetCoreBuild=true

Then the deployment, and this is the whole argument list rather than an excerpt of one:

sqlpackage /Action:Publish \
  /TargetConnectionString:"Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Default;Encrypt=True;" \
  /SourceFile:"./db/bin/Debug/ShopDb.dacpac"

arguments: is appended to that line, which is how /p:BlockOnPossibleDataLoss=true reaches the deployment. Which properties each action accepts is sqlpackage-import-export; open it before writing a /p: you have not seen in that build's own help output. Four things that shape is quietly telling you:

  • The output path is guessed, not asked for. The action looks for <project dir>/bin/<configuration>/<project name>.dacpac, configuration defaulting to Debug, so an output redirected inside the project file rather than through build-arguments leaves it looking in the wrong place.
  • Extract, Import and Export are refused, real SqlPackage actions though they are: Action Extract is invalid. Supported action types are: Publish, Script, DriftReport, or DeployReport.
  • The runner ships sqlcmd but not the deployment tool. The action downloads go-sqlcmd 1.6.0 into the tool cache itself; nothing brings SqlPackage, and on Linux the action calls the bare name on PATH. One step fixes it with no path override, because ~/.dotnet/tools is already on PATH on the hosted image: dotnet tool install -g microsoft.sqlpackage.
  • macOS runners are refused for the dacpac and project paths: This action is not supported on a Mac environment.

Authenticating without a stored password

Two different secrets are being avoided. A database password goes away with an identity-based keyword in the connection string: Active Directory Default picks up the login step's credentials, where Active Directory Password and Active Directory Service Principal put a secret back and a plain user and password puts the database password back. A client secret goes away by federating the workflow's token, which needs permissions: id-token: write in the workflow below, without which there is no token to exchange.

The correction: read the subject claim, do not compose it

The credential matches on issuer, audience and subject. The subject is the one that goes wrong, and composing it from the documented repo:OWNER/REPO:ref:refs/heads/main is the failure. Ask the repository what prefix it presents instead:

gh api repos/OWNER/REPO/actions/oidc/customization/sub

Measured 2026-09-03 across twelve repositories in one account: eleven answered with the plain name-based prefix, and the twelfth, one that had been renamed, answered with numeric ids in it:

{"use_default":true,"use_immutable_subject":false,"sub_claim_prefix":"repo:OWNER@<ownerId>/REPO@<repoId>"}

Both flags say the default is in force and the shape is still different. GitHub's OIDC reference explains why: repositories created, renamed or transferred after 15 July 2026 get an immutable default subject built from owner and repository ids, and use_immutable_subject reports only the separate opt-in, not the shape. Learn covers the Azure side under immutable subjects. So a template works on eleven repositories and silently fails on the twelfth, which is worse than failing everywhere.

Take sub_claim_prefix and append the job segment: :ref:refs/heads/<branch>, :ref:refs/tags/<tag>, :environment:<name>, or :pull_request. A job naming an environment presents :environment:<name> in place of the ref segment, not in addition, so the two jobs below need two credentials.

az identity federated-credential create \
  --name gh-deploy-production --identity-name <identity> -g <resource group> \
  --issuer "https://token.actions.githubusercontent.com" \
  --subject "<the prefix>:environment:production" \
  --audiences "api://AzureADTokenExchange"

Copy the prefix rather than retyping it: Learn records that credential matching became case-sensitive in September 2024, so a name typed in the wrong case fails with no other symptom.

A flexible federated identity credential can cover several job shapes at once by wildcard-matching sub alongside repository_id or repository_owner_id. Learn calls it preview, application objects only, and says Azure CLI errors on it, while Azure CLI 2.90.0 here exposes --claims-matching-expression-value. The two disagree, so measure before planning around it.

The error names the wrong thing

A subject that does not match produces AADSTS700213: No matching federated identity record found for presented assertion subject '<the subject>'. That line is the most useful thing in the run, because it quotes the subject actually presented. Copy it into the credential. The line right after it is the misleading one:

##[error]Login failed with Error: The process '/usr/bin/az' failed with exit code 1.
Double check if the 'auth-type' is correct.

auth-type is an input of the login step and is not the problem. An agent reading only the last error changes it, or reaches for a client secret to go green, throwing away the point of federating. Read upward to the AADSTS line and read its number: a directory can also refuse on policy grounds, and that refusal is not fixable in the subject at all.

The firewall problem, and why it disguises the last one

A hosted runner's public address changes between runs, so a fixed rule is not an option. What the action does instead:

  1. Runs the sqlcmd probe above, against master first and the target database second.
  2. If that fails, it searches the error text for an IPv4 address and adds a server-level rule named ClientIPAddress_<year>-<month>-<day>_<address> for exactly that address.
  3. It deploys, then drops the rule in a finally block, including when the deployment failed.

Two consequences follow, and the second wastes the most time.

It needs a role on the server resource. Adding a rule is a control-plane call, so the login step must have run and the identity must be able to write firewall rules on the logical server. Without that, the deployment fails at a step that is not the deployment.

Any login failure is reported as a firewall failure. If the probe fails for a reason other than the firewall, the error carries no address, and the action raises Failed to add firewall rule. Unable to detect client IP Address. <the underlying error>. That message is about the firewall. The cause is usually the credential. Read past the first sentence.

The two alternatives both pair with skip-firewall-check: true: a self-hosted runner on a fixed address, which costs infrastructure and leaves a standing rule, or one reaching a private endpoint, which exposes nothing publicly and is the most to set up. Allowing all Azure services is not on that list on purpose. It is not a narrow rule.

Environment approvals, and what they do not protect

The gate holds the job that names it, and nothing else in the workflow:

  • Another job reads the same repository secrets. They are available to every job, so anything reachable with them is reachable without waiting. Put the deployment credential in environment secrets, so the gate and the credential cover the same ground.
  • The credential subject is the real binding. A subject ending :environment:production cannot be exchanged by a job that does not name that environment. That is enforcement, where the approval is only a pause.
  • The rule may not exist at all. A required-reviewer rule on a private repository whose plan excludes it is refused with Failed to create the environment protection rule. The environment is still created, the workflow still references it, and every job runs unblocked. Read the protection rules back rather than assuming the environment implies them.

A workflow shape that holds together

name: deploy-schema
on:
  push:
    branches: [main]
permissions:
  id-token: write
  contents: read
jobs:
  plan:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: dotnet tool install -g microsoft.sqlpackage
      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
      - uses: azure/sql-action@v2.4
        with:
          connection-string: ${{ secrets.SQL_CONNECTION_STRING }}
          path: ./db/ShopDb.sqlproj
          action: DeployReport
  deploy:
    needs: plan
    runs-on: ubuntu-latest
    environment: production      # this changes the subject claim
    steps:
      - uses: actions/checkout@v4
      - run: dotnet tool install -g microsoft.sqlpackage
      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
      - uses: azure/sql-action@v2.4
        with:
          connection-string: ${{ secrets.SQL_CONNECTION_STRING }}
          path: ./db/ShopDb.sqlproj
          action: Publish

Two jobs, two credentials, because they present different subjects: one ending :ref:refs/heads/main and one ending :environment:production. The connection string holds no password, so it is a secret only to keep the server name out of a public log. DeployReport writes the planned changes and touches nothing, which is what the reviewer releasing the second job should be reading.

Check it worked

A green run means the action exited zero. It does not say the schema landed, nor that the firewall was left as it was found. Three checks, none of which changes anything.

gh run view <run id> --json conclusion,jobs --jq '.conclusion, [.jobs[] | {name, conclusion}]'

Expect success and every job present. A job at waiting is the gate working, not a failure, and a deploy job that never appears is a needs: that never released.

sqlpackage /Action:DeployReport /SourceFile:"./db/bin/Debug/ShopDb.dacpac" \
  /TargetConnectionString:"<the same connection string>" /OutputPath:after.xml
test -f after.xml || { echo "no report was written"; exit 1; }
grep -c "<Operation " after.xml || true

Expect 0. A publish that landed leaves the next deploy report nothing to do, and any count above zero names an object the run did not apply. /OutputPath is the DeployReport parameter; /DeployReportPath belongs to Publish, so the wrong flag writes no report at all, which is what the test -f is for: a missing report makes the count 0 as well, and without that line the check reports a clean deployment when it measured nothing.

|| true is not decoration, and it is the reason this check used to fail on success. grep -c prints 0 and exits 1 when it selects no lines. A GitHub Actions run: block is bash -e, so the step goes red on precisely the outcome you want. Measured 2026-09-05 against a real logical server: after a clean publish the report was <DeploymentReport ...><Alerts /></DeploymentReport>, grep -c printed 0, and the exit code was 1.

Expect it to be slow, and do not read slow as hung. On a Basic tier database, measured the same day, publishing a single three-column table took 3 minutes 16 seconds and the DeployReport after it took 2 minutes 50 seconds. DacFx compares the whole model, so the floor is set by the tier rather than by the size of the change. While it runs, sys.dm_exec_sessions shows a DacFx Deploy session, which is how to tell a working deployment from a stalled one.

One line in a successful publish reads like a failure. Between Updating database (Start) and Creating Table, a project built from the default template prints 'QUERY_STORE=OFF' is not supported in this version of SQL Server. The publish continues and exits zero. It is not an error and there is nothing to fix.

az sql server firewall-rule list -g <resource group> -s <server> --query "[].name" -o tsv

Expect no name beginning ClientIPAddress_. The action removes its own rule even when the deployment failed, so a leftover means the job was cancelled rather than failed. Do not read the date out of that name: it is built from getMonth(), zero-based, and getDay(), the weekday.

Do not

  • Do not compose the subject from a template. Read the prefix the repository reports and append the job segment, and do not add an environment to a job without adding its credential too.
  • Do not act on the login step's closing line about auth-type, or treat "unable to detect client IP address" as networking. Both name the step that reported, not the step that failed.
  • Do not fall back to a client secret or a database password to get a red run green. That is the one thing the design was for.
  • Do not open the firewall to all Azure services to avoid managing a rule. It is not narrow.
  • Do not assume declaring an environment created a gate, or that a gate on one job protects another job holding the same secrets.

References

  • references/oidc-and-firewall.md holds the token payloads for both job shapes, the failure messages in full, the credential commands for an application identity as well as a user-assigned one, and the action's steps in order. Open it when a login or a firewall step fails and the message on screen does not say which of the two broke.
  • GitHub's OIDC reference when the subject claim is in question; SqlPackage Publish and DeployReport when the deployment step's behaviour is.