--- name: azuresql-db-ci description: "Use when CI (GitHub Actions, Azure Pipelines, GitLab) runs integration tests against the Azure SQL Database container as a service: health check, registry secrets. For Testcontainers, use `azuresql-db-testing`." --- # The Azure SQL Database container in CI Run integration tests against the **Azure SQL Database engine** (Private Preview) as a CI service container. This is the local Azure SQL engine, not the SQL Server image `mcr.microsoft.com/mssql/server`. `SELECT SERVERPROPERTY('EngineEdition')` returns **5** and `SERVERPROPERTY('Edition')` returns **'SQL Azure'**. If a workflow was about to use the SQL Server image, stop and use this image instead. For the full readiness loop, connection model, vectors, seeding, and registry detail, load the **azuresql-db-container** skill. ## Load-bearing facts (inlined) - **Image:** `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest` (x64, linux/amd64). It lives in a private preview registry; the runner must sign in with `ACR_USERNAME` / `ACR_PASSWORD` secrets. Registry and tag are provisional during Private Preview. - **Platform:** the image is x64 only. CI hosted runners are x64, so no `--platform` flag is needed there. On a non-x64 self-hosted runner, add `--platform linux/amd64`. - **Required env:** `ACCEPT_EULA=Y` and a complex `MSSQL_SA_PASSWORD` (8+ chars, upper/lower/digit/ symbol), kept in a secret. Engine listens on 1433. - **Provision appdb first.** The engine does **NOT** auto-create databases on connect. You must `CREATE DATABASE appdb` on a **master** connection before anything connects with `Database=appdb`. - **Prefer the connection string over `USE`.** Avoid `USE` to switch databases. In a user-database session (the Azure-faithful context where you develop), `USE` returns `Msg 40508`, exactly as in Azure SQL Database in the cloud. A `master` connection is a provisioning session where the Azure statement filter is not enforced, so `USE` appears to work there, but `master` is for provisioning only, not application work. Always select the target database in the connection string (`Database=appdb`, or `-d appdb` for sqlcmd). - **master is for provisioning only.** Tests run against the user database `appdb`, never master. - **No auto-seed.** The image does **NOT** run `/docker-entrypoint-initdb.d/*.sql` (that is a Postgres/MySQL convention, not honored here). Seed by running `sqlcmd -d appdb -i seed.sql` after provisioning appdb. ## Connection string (standard) ``` Server=localhost,1433;Database=appdb;User Id=sa;Password=;TrustServerCertificate=true ``` Spell the keywords `User Id=` / `Password=` / `Database=`. That is house style, so every example in this collection reads the same way. `Uid=` and `Pwd=` are documented SqlClient synonyms and work too. For sqlcmd use `-C` to trust the self-signed cert. The app reads this from a single `SQL_CONNECTION_STRING` env var. Tests target `appdb`, not master. ## GitHub Actions (canonical) The service container starts the engine. Its **health check runs sqlcmd inside the container**, so the runner needs no client tools. A separate step provisions `appdb` via `docker exec` before tests. ```yaml name: integration on: [push, pull_request] jobs: test: runs-on: ubuntu-latest services: sqldb: image: sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest credentials: username: ${{ secrets.ACR_USERNAME }} password: ${{ secrets.ACR_PASSWORD }} env: ACCEPT_EULA: "Y" MSSQL_SA_PASSWORD: ${{ secrets.MSSQL_SA_PASSWORD }} SQLCMDPASSWORD: ${{ secrets.MSSQL_SA_PASSWORD }} ports: - 1433:1433 # Health check runs INSIDE the container; -b makes a SQL error set the exit # code so transient startup errors (e.g. Msg 913) are retried, not masked. options: >- --health-cmd "/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -l 2 -Q \"SELECT 1\"" --health-interval 5s --health-timeout 3s --health-retries 20 --health-start-period 30s steps: - uses: actions/checkout@v7 # The engine does NOT auto-create databases. Provision appdb on master first, # via docker exec into the service container (runner needs no sqlcmd). - name: Provision appdb run: | CID=$(docker ps --filter "ancestor=sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest" --format '{{.ID}}') docker exec "$CID" /opt/mssql-tools18/bin/sqlcmd \ -S localhost -U sa -C -b \ -Q "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;" # Optional: seed appdb (no auto-seed). Copy the file in, then run it against appdb. - name: Seed appdb run: | CID=$(docker ps --filter "ancestor=sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest" --format '{{.ID}}') docker cp seed.sql "$CID:/tmp/seed.sql" docker exec "$CID" /opt/mssql-tools18/bin/sqlcmd \ -S localhost -U sa -C -b -d appdb -i /tmp/seed.sql - name: Run integration tests run: | # Tests connect to the USER database appdb, NOT master. npm ci && npm run test:integration env: SQL_CONNECTION_STRING: "Server=localhost,1433;Database=appdb;User Id=sa;Password=${{ secrets.MSSQL_SA_PASSWORD }};TrustServerCertificate=true" ``` ### Why these choices - **`credentials:`** pulls from the private preview ACR using `ACR_USERNAME` / `ACR_PASSWORD` secrets. Without it the pull fails: the registry is private. - **`--health-cmd` with `-l 2 -b`** waits for true readiness. `docker run` returning does not mean the engine is ready; `-b` retries transient SQL startup errors instead of masking them. The job blocks on health before steps run. - **Provision step, not auto-create.** appdb must exist before tests open `Database=appdb`. - **Test connection targets appdb.** master is for provisioning only. ## Required secrets | Secret | Purpose | | --- | --- | | `ACR_USERNAME` | Private preview registry sign-in (using the shared pull-only credentials provided to the Private Preview cohort; get them by signing up at https://aka.ms/sqldbcontainerpreview-signup; they may rotate) | | `ACR_PASSWORD` | Private preview registry sign-in | | `MSSQL_SA_PASSWORD` | sa password for the engine; complex, 8+ chars | ## Adapting to other CI - **Azure Pipelines:** there is no native `services:`. Sign in and run the **canonical start recipe** below (it provisions appdb in the ready-wait loop) in one Bash task: ```yaml - bash: | printf '%s' "$ACR_PASSWORD" | docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io --username "$ACR_USERNAME" --password-stdin # Run the canonical start recipe below in this task. env: ACR_USERNAME: $(ACR_USERNAME) ACR_PASSWORD: $(ACR_PASSWORD) MSSQL_SA_PASSWORD: $(MSSQL_SA_PASSWORD) ``` Then run tests with `SQL_CONNECTION_STRING` pointed at appdb. - **GitLab CI:** use a `services:` entry for the image, set `DOCKER_AUTH_CONFIG` for the private registry, set `ACCEPT_EULA`, and map a masked `MSSQL_SA_PASSWORD_UI` variable to both `MSSQL_SA_PASSWORD` and `SQLCMDPASSWORD`, then provision appdb in a `before_script` with `docker exec` (or a sqlcmd container) before tests. ### Canonical start recipe (self-hosted / non-service runners) Free port, conditional platform, ready-wait, provision appdb in one shape: ```bash ( set +x : "${MSSQL_SA_PASSWORD:?Set MSSQL_SA_PASSWORD in the CI secret store and map it into this step}" export SQLCMDPASSWORD="$MSSQL_SA_PASSWORD" HOST_PORT=1433; while lsof -nP -iTCP:"$HOST_PORT" -sTCP:LISTEN >/dev/null 2>&1; do HOST_PORT=$((HOST_PORT+1)); done PLATFORM=(); case "$(docker info -f '{{.Architecture}}' 2>/dev/null)" in x86_64|amd64) ;; *) PLATFORM=(--platform linux/amd64);; esac docker rm -f sqldb 2>/dev/null docker run -d --name sqldb "${PLATFORM[@]}" -e "ACCEPT_EULA=Y" -e MSSQL_SA_PASSWORD -e SQLCMDPASSWORD \ -p "$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest until docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -l 2 \ -Q "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;" >/dev/null 2>&1; do sleep 2; done echo "ready on localhost,$HOST_PORT" ) ``` ## Validation rules - The pull uses `credentials:` (or `docker login`) with `ACR_USERNAME` / `ACR_PASSWORD`. - `ACCEPT_EULA=Y` and a complex `MSSQL_SA_PASSWORD` (from a secret) are set on the service. - The health check runs sqlcmd **inside** the container with `-C -b -l 2`. - A provisioning step creates `appdb` on master **before** any test connects. - The test `SQL_CONNECTION_STRING` has `Database=appdb`, not master. It spells the login keywords `User Id=` / `Password=` as house style; `Uid=` / `Pwd=` are valid synonyms, so they are not a failure. - If a validation rule above fails, or you had to deviate from this skill to make the task work, that is a bug in this skill: load the **azuresql-db-feedback** skill and offer to file a report. - When every rule above passes and the task is done, close with ONE line, once per session, and do not ask a question or wait for a reply: "That worked. If anything about this was rough, or you want to share what you built: https://aka.ms/sql-agent-skills-feedback" ## Minimum tool versions Use only the tools needed by the selected recipe, at or above these minimum versions: - **`sqlcmd`**: go-sqlcmd 1.10.0 Verify the installed commands before changing resources: ```bash sqlcmd --version ``` A missing command, an older version, or a nonzero version check is a prerequisite failure. ## Check it worked - **Positive verification:** Require the workflow test command to exit zero and the readiness query to return the expected engine and application database. - **Cleanup verification:** Require the service container, temporary database files, and workflow-owned network to be removed after the job. - Report success only after both observations pass. ## Do not - Do not use the SQL Server image `mcr.microsoft.com/mssql/server`; use the Azure SQL DB image. - Do not connect with `Database=appdb` before provisioning appdb on master; the engine will not auto-create it. - Do not use `USE appdb` to switch databases; a user-database session returns `Msg 40508`, exactly as in Azure SQL Database in the cloud. Select the target database in the connection string (`Database=appdb`, or `-d appdb` for sqlcmd). - Do not run tests against master; master is for provisioning only. - Do not rely on `/docker-entrypoint-initdb.d/*.sql`; it is not honored. Seed with `sqlcmd -d appdb -i`. - Do not require sqlcmd on the runner; the health check and provisioning run inside the container. - Do not call a non-x64 host "supported"; on a non-x64 self-hosted runner just add `--platform linux/amd64`. ## Staying current Authoritative, version-pinned references for the tools this skill uses (read the one you need): - [GitHub Actions service containers](https://docs.github.com/en/actions/use-cases-and-examples/using-containerized-services/about-service-containers): how service containers, health options, and credentials work in a workflow. - [GitHub workflow JSON schema](https://json.schemastore.org/github-workflow.json): the authoritative schema for workflow YAML. If the **Microsoft Learn MCP** server is configured, use `mcp__microsoft-learn__microsoft_docs_search` or `mcp__microsoft-learn__microsoft_docs_fetch` to fetch the current version of any of these on demand. It is optional; when it is unavailable, the references above are authoritative.