azuresql-db-local-to-cloud

name

azuresql-db-local-to-cloud

description

Proves that code built and tested against the local Azure SQL Database container runs unchanged against Azure SQL Database in the cloud, with only the connection string changing. Use when a user wants to develop locally then deploy to the cloud, asks "will this work in Azure", "same code local and cloud", "promote to Azure SQL", "swap the connection string", "dev/prod parity", "local to cloud", or is wiring SQL_CONNECTION_STRING for an app that must target both the container and a cloud server. Use this when an app uses local SA auth but needs Microsoft Entra auth in the cloud. Covers Node (mssql), .NET (Microsoft.Data.SqlClient), and Python (pyodbc). Reach for this skill before hand-editing app code to "make it work in Azure"; the rule is the code does not change, only the connection string does.

From the Azure SQL Database container to Azure SQL Database: same code, local to cloud

Build and test against the local container, then deploy the same application code to Azure SQL Database in the cloud. Only the connection string changes. Nothing else.

This works because the local container is the Azure SQL Database engine, not the SQL Server image. SELECT SERVERPROPERTY('EngineEdition') returns 5 and SERVERPROPERTY('Edition') returns 'SQL Azure', the same as the cloud. So the SQL surface your code depends on is the same in both places.

The one rule

Do not change application code between local and cloud. The application reads its connection string from a single environment variable, SQL_CONNECTION_STRING. Local development sets it to the container; cloud deployment sets it to the Azure SQL server. Same binaries, same queries, same schema.

If you find yourself editing queries, drivers, or schema to "make it work in Azure", stop: that is a bug. The only thing that legitimately differs is the connection string (and, with it, the auth method).

The single env var, two values

Standardize on SQL_CONNECTION_STRING. Use User Id= / Password= / Database= (never Uid= / Pwd=).

Local (container, SA auth):

Server=localhost,1433;Database=appdb;User Id=sa;Password=YourStr0ng_Passw0rd;TrustServerCertificate=true

Cloud (Azure SQL Database, Microsoft Entra auth):

Server=your-server.database.windows.net,1433;Database=appdb;Authentication=Active Directory Default;Encrypt=true

Note what stays identical: Database=appdb, the table names, the parameterized SQL. Only Server, the auth fields, and TLS posture move.

Local SA auth vs cloud Entra auth (why, not just how)

Local: the container is provisioned with one bootstrap login, sa, set via the MSSQL_SA_PASSWORD environment variable. There is no identity provider required in front of a container on your laptop, so password auth over a trusted self-signed cert (TrustServerCertificate=true) is the pragmatic local default.

Microsoft Entra ID authentication does work on the container (configure with the MSSQL_AAD_* variables and a certificate mount; see the azuresql-db-container skill, references/entra-auth.md). Use it when you want closer local-to-cloud parity. Most local-to-cloud flows still start with SQL auth locally and switch only the connection string for Entra in the cloud.

Cloud: Azure SQL Database sits behind Microsoft Entra ID. Instead of shipping a password, the app presents a token from its Entra identity (a managed identity in production, your developer sign-in locally against the cloud). The driver acquires the token; you never put a secret in the connection string. TLS is real and enforced, so use Encrypt=true and drop TrustServerCertificate.

The application code does not branch on this. The driver reads the Authentication= keyword (or its absence) from the connection string and does the right thing. That is the whole point: auth is configuration, not code.

Full walkthrough, token flow, and per-stack auth setup: see references/auth-local-vs-cloud.md.

Minimal load-bearing facts about the local container

Just enough to run the examples on a fresh container. For full detail see the azuresql-db-container skill.

• Image: sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest (x64, linux/amd64). Private preview registry: run docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io first 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). Registry and tag are provisional during Private Preview.
• On a non-x64 host, add --platform linux/amd64.
• Required env: ACCEPT_EULA=Y and a complex MSSQL_SA_PASSWORD (8+ chars, upper/lower/digit/symbol). Engine listens on 1433.
• The engine does not auto-create databases. You must CREATE DATABASE appdb on a master connection before connecting with Database=appdb. The master connection is for provisioning only; do real work on appdb.
• 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 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).

Start the container and provision appdb (canonical recipe)

Run this once. It picks a free host port, adds --platform only when needed, waits for real readiness, and provisions appdb inside the retry loop. The -b -l 2 makes a transient startup error (for example Msg 913) set the exit code so the loop retries instead of masking it.

# Pick a free host port and add the platform flag only on a non-x64 host (works in bash and zsh).
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=YourStr0ng_Passw0rd" \
  -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 -P "YourStr0ng_Passw0rd" -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"

Then point the app at it, using the HOST_PORT the loop settled on:

export SQL_CONNECTION_STRING="Server=localhost,$HOST_PORT;Database=appdb;User Id=sa;Password=YourStr0ng_Passw0rd;TrustServerCertificate=true"

To run against the cloud later, change only this variable; do not touch the app.

Proof: same code, two stacks

Each example assumes appdb already exists (provisioned by the recipe above) and reads SQL_CONNECTION_STRING from the environment. It creates a table if needed and runs a parameterized CRUD transaction. Run it once with the local string, then again with the cloud string: identical code, identical result.

Node (mssql)

// app.mjs  ->  node app.mjs  (ESM: use the .mjs extension, or set "type":"module" in package.json)
import sql from 'mssql';

const pool = await sql.connect(process.env.SQL_CONNECTION_STRING);
const tx = new sql.Transaction(pool);
await tx.begin();
try {
  const r = new sql.Request(tx);
  await r.query(`IF OBJECT_ID('dbo.todo') IS NULL
    CREATE TABLE dbo.todo (id INT IDENTITY PRIMARY KEY, title NVARCHAR(200), done BIT);`);
  // CREATE
  const ins = await new sql.Request(tx)
    .input('title', sql.NVarChar, 'ship local-to-cloud')
    .query('INSERT INTO dbo.todo(title, done) OUTPUT inserted.id VALUES (@title, 0);');
  const id = ins.recordset[0].id;
  // UPDATE
  await new sql.Request(tx)
    .input('id', sql.Int, id)
    .query('UPDATE dbo.todo SET done = 1 WHERE id = @id;');
  // READ
  const read = await  sql.(tx)
    .(, sql., id)
    .();
  .(read.[]);
   tx.();
}  (e) {  tx.();  e; }
 pool.();

.NET (Microsoft.Data.SqlClient)

// Program.cs  ->  dotnet run   (uses Microsoft.Data.SqlClient)
using Microsoft.Data.SqlClient;

var cs = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING");
using var conn = new SqlConnection(cs);
conn.Open();
using var tx = conn.BeginTransaction();
try {
    new SqlCommand(@"IF OBJECT_ID('dbo.todo') IS NULL
        CREATE TABLE dbo.todo (id INT IDENTITY PRIMARY KEY, title NVARCHAR(200), done BIT);",
        conn, tx).ExecuteNonQuery();
    // CREATE
    var ins = new SqlCommand(
        "INSERT INTO dbo.todo(title, done) OUTPUT inserted.id VALUES (@title, 0);", conn, tx);
    ins.Parameters.AddWithValue("@title", "ship local-to-cloud");
    int id = (int)ins.ExecuteScalar();
    // UPDATE
    var upd = new SqlCommand("UPDATE dbo.todo SET done = 1 WHERE id = @id;", conn, tx);
    upd.Parameters.AddWithValue("@id", id);
    upd.ExecuteNonQuery();
    // READ
    var sel = new SqlCommand("SELECT id, title, done FROM dbo.todo WHERE id = @id;", conn, tx);
    sel.Parameters.AddWithValue("@id", id);
    using var r = sel.ExecuteReader();
    while (r.Read()) Console.WriteLine($"{r["id"]} {r["title"]} {r["done"]}");
    tx.Commit();
} catch { tx.Rollback(); throw; }

Python (pyodbc) reference

A third stack and the deployment checklist live in references/auth-local-vs-cloud.md so this body stays an overview.

Validation rules

• appdb must exist before the app connects. Provision it on master first (the recipe does this). The engine never auto-creates it.
• The app reads exactly one connection variable, SQL_CONNECTION_STRING.
• Local string uses SA auth + TrustServerCertificate=true; cloud string uses Authentication=Active Directory Default + Encrypt=true.
• Use User Id= / Password= / Database=, not Uid= / Pwd=.
• Switching environments changes the connection string only. The diff to app code between local and cloud is zero lines.
• 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"

Do not

• Do not use the SQL Server image mcr.microsoft.com/mssql/server. If you were about to, stop and use the image above; this is the Azure SQL engine.
• Do not connect to Database=appdb before creating it on master.
• Do not use USE appdb to switch databases; set the database in the connection string (Database=appdb, or -d appdb for sqlcmd). 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).
• Do not put a password or secret in the cloud connection string; use Entra auth and let the driver fetch a token.
• Do not branch app logic on environment; keep auth in configuration.
• Do not call a non-x64 host "supported"; on a non-x64 host just add --platform linux/amd64.

References

• references/auth-local-vs-cloud.md: why local SA auth and cloud Microsoft Entra auth differ, the token flow, per-stack auth setup, the Python (pyodbc) example, and the deployment checklist. Read it when wiring the cloud connection string or promoting an app to Azure.

Staying current

Authoritative, version-pinned references for the tools this skill uses (read the one you need):

• Connect and query Azure SQL Database: per-language quickstarts, connection info, TLS, and drivers for the cloud service.
• SqlConnection connection string keywords: the full connection-string keyword table.

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.