connect-to-azure-sql

Connect to Azure SQL Database

Opening one connection is easy. Two things go wrong: the first connection is expected to fail and the code has no retry, and a certificate error gets fixed by turning verification off.

Flags and defaults below were verified 2026-09-03 against sqlcmd 1.10.0 and User Secrets Manager 8.0.27 on this machine; cloud behaviour is from Microsoft Learn.

Connect once from the command line before writing any code

Settle the server, database, credential and certificate before an application is in the picture, so every later failure is a failure of the code. Cloud, SQL authentication, password out of the command line:

sqlcmd -S <server>.database.windows.net,1433 -d <database> -U <user> -P "$SQL_PASSWORD" \
  -N mandatory -l 30 -Q "SELECT DB_NAME() AS db, encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID"

Cloud, Microsoft Entra ID, with no password anywhere:

az login
sqlcmd -S <server>.database.windows.net,1433 -d <database> -G -N mandatory -l 30 \
  -Q "SELECT SUSER_NAME() AS login"

Local Azure SQL Database container. -C is the entire difference, there because the container presents a self-signed certificate.

sqlcmd -S 127.0.0.1,1433 -d <database> -U sa -P "$MSSQL_SA_PASSWORD" \
  -N mandatory -C -l 30 -Q "SELECT DB_NAME() AS db"

Each switch is a connection string keyword under another name, so a working command line converts straight into a working string:

Switch	Connection string keyword	Note
`-N mandatory`	`Encrypt=Mandatory`	`-N strict` is TDS 8.0, where trust cannot be bypassed
`-C`	`TrustServerCertificate=true`	Container only
`-F <name>`	`HostNameInCertificate`	The fix for a name mismatch
`-J <file>`	certificate pinning, PEM, DER or CER	Validates one certificate, not none
`-l 30`	`Connect Timeout`	Covers the retries inside it
`-G`	`Authentication=Active Directory Default`	No `-U`; add `-U` for interactive

-N is validated before any connection is attempted, so a typo is caught for free. Measured 2026-09-03 on 1.10.0: any other value exits 1 with Argument value has to be one of [m[andatory] yes 1 t[rue] disable o[ptional] no 0 f[alse] s[trict]].

Container and cloud: the same string with one exception

	Local container	Azure SQL Database
Server	`127.0.0.1,<mapped port>`	`<server>.database.windows.net,1433`
Encryption	on	on
Certificate	self-signed, so `-C` / `TrustServerCertificate=true`, or pin it with `-J`	validated, never `-C`
Authentication	SQL login. Microsoft Entra ID only if the container was started with an application registration certificate, a client id and a tenant id: below	SQL login or Entra ID
Retry for `40613`	no equivalent; nothing throttles or fails over locally	mandatory, below
Pool ceiling	no service tier, so no worker number to size against	size against workers

A clean local run is therefore no evidence the retry story works.

Microsoft Entra ID on the container is configured, not default

The image reads the certificate path, the application (client) id and the tenant id from three MSSQL_AAD_ environment variables set when the container is created. Learn documents that trio for SQL Server on Linux containers and does not document this image, so here it is measured, not cited:

docker inspect -f '{{range .Config.Env}}{{println .}}{{end}}' <container> | grep -c '^MSSQL_AAD_'

3 or more means Entra ID was configured. Measured 2026-09-03 on a container created without it: 0, exit 1. That state refuses the login, not the mode, so -G or Authentication=Active Directory Default returns 18456, Login failed for user '' (measured 2026-08-28 in connect-from-dotnet), naming neither Entra ID nor the missing configuration, and no connection string edit fixes it. Open entra-id-auth once the three are set and the identity needs a database user, or when an 18456 names a token-identified principal rather than an empty one.

What this skill does not own

Driver installation, per-language syntax and pool configuration are connect-from-dotnet, connect-from-python, connect-from-typescript-and-node. An error number needing a cause is diagnose-connection-errors, except 40613, which stays here. Resource pressure is diagnose-resource-pressure, and creating the server and its firewall rule is provision-azure-sql-db.

Retry is part of the first version, not the hardening pass

Azure SQL Database reconfigures under you. Microsoft's wording: most reconfiguration events complete in under 60 seconds, and applications should be built to expect these transient errors rather than surface them to users.

On General Purpose serverless, which is what the free offer runs on, Microsoft documents this:

If a serverless database is paused, the first connection attempt resumes the database and returns an error stating that the database is unavailable with error code 40613. Once the database resumes, retry the connection. Databases generally resume in less than one minute.

The default auto-pause delay is 60 minutes, so a developer back from lunch gets 40613 on the first run and reads it as a wrong password or a dead server. Code that leaves retry for later fails the first time it is run.

The retry policy

Wait 5 seconds before the first retry. Shorter risks overwhelming the service.
Grow the delay exponentially, to a maximum of 60 seconds, and cap the attempts so a broken configuration eventually reports itself.
A transient error during a query is not retried on the same connection. Open a fresh connection, then retry the command, and confirm the transaction committed or rolled back first.

Transient, so retry

Number	What it is
`40613`	Database not available: resume of a paused database, reconfiguration, or the dedicated administrator connection already in use
`40197`	The service hit an error processing the request. Carries an embedded code worth logging
`40501`	The service is busy. Engine throttling
`40540`, `40143`	Reconfiguration and failover paths
`10928`, `10929`	A resource limit was reached. Resource ID 1 is workers, Resource ID 2 is sessions
`49918`, `49919`, `49920`	Not enough resources, or too many control-plane operations in flight
`233`, `64`, `20`, `10053`, `10054`, `10060`	Transport-level failures during or just after connect

4060 is on the transient list too, but usually means the database name is wrong or the login has no user there. Retry it a small fixed number of times, then report it.

Not transient, so do not retry

Number	Cause	Fix
`18456`	Login failed	Credential or user problem, not a service problem
`40615`	Client IP not allowed	A firewall rule is missing. See `provision-azure-sql-db`
`47073`	Public network access is disabled on the server	Connect through the private endpoint
`47072`	Login failed with invalid TLS version	The client negotiated below the server's minimum

One case looks transient and is not: a free-offer database whose monthly allowance is exhausted is inaccessible until the start of the next calendar month, so retry never succeeds.

Do not stack retry on retry without doing the arithmetic

Driver connection resiliency multiplies with application-level retry. The documented .NET example: application logic retrying 4 times over a driver configured for 3 connection retries is 12 attempts.

The .NET defaults an unconfigured string already has: ConnectRetryCount 1, ConnectRetryInterval 10 seconds, Connection Timeout 15 seconds, Command Timeout 30 seconds. Learn states two inequalities that hold whatever the language:

New connection: Connection Timeout >= ConnectRetryCount * ConnectRetryInterval, or the outer timeout cancels the last retry. At 3 retries of 10 seconds, a timeout of 29 never reaches the third.
Reconnect during command execution: Command Timeout > (ConnectRetryCount - 1) * ConnectRetryInterval, plus time for the command itself.

Pick one layer to own retry and turn the other off deliberately. Two Learn pages disagree about what ConnectRetryCount covers: the transient-errors article puts the initial Open() inside it, the SqlClient overview calls it idle resiliency only. Assume the narrower reading and write your own retry around the first connect.

Encryption is not a knob to turn off

Microsoft's recommendation for every connection to Azure SQL Database, in every driver: Encrypt = On, TrustServerCertificate = Off, plus HostNameInCertificate = <full hostname> when the client connects through another name such as a DNS alias. Together they make the driver verify the server's identity. TrustServerCertificate=true keeps the traffic encrypted but stops the client checking who it is talking to, the part that matters on a public endpoint. Encryption and identity are separate settings, and login credentials are encrypted whatever Encrypt says.

The rule for an agent: a certificate error is a name problem, not a trust problem. Fix the hostname or set the hostname-in-certificate option. Never disable verification, never set Encrypt=false; the one exception is the container's self-signed certificate above.

Two facts that change under the model's feet:

The defaults flipped. Microsoft.Data.SqlClient 4.0 changed Encrypt from false to true and ODBC Driver 18 made the same change, both declared breaking. Code that worked before an upgrade and fails after it is usually this, surfacing as SSL Provider: The certificate chain was issued by an authority that is not trusted.
TLS 1.0 and 1.1 are retired. The lowest minimum a server accepts is TLS 1.2, enforcing a specific minimum cannot be reverted, and enforcing 1.3 breaks clients whose driver or operating system does not support it.

Choosing a driver

Use the driver Microsoft publishes for the language. Anything else is a support problem later, and non-Microsoft drivers may not use TLS by default.

Language	Driver
C# and .NET	`Microsoft.Data.SqlClient`
Python	`mssql-python`
Node.js and TypeScript	the Node.js driver for SQL Server
Java	the Microsoft JDBC driver
Go	`go-mssqldb`
C and C++	ODBC Driver 18 for SQL Server
PHP	the Microsoft PHP drivers
Ruby	the Ruby driver for SQL Server

What the connection string carries

Must have: the fully qualified server name and port 1433, the user database and never master, encryption on with certificate trust off, and a connect timeout with room for the retries inside it.

Must not have:

a password literal. The secret comes from a secret store or the environment at run time: User Secrets locally, a vault in production, never a checked-in file.

dotnet user-secrets init --project ./src/Api
dotnet user-secrets set "ConnectionStrings:Default" \
  "Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Default;Encrypt=Mandatory;Connect Timeout=60" \
  --project ./src/Api

TrustServerCertificate=true outside the local container, or Encrypt=false anywhere
Authentication=Active Directory Password. That mode is deprecated in the Microsoft SQL drivers, incompatible with mandatory Microsoft Entra multifactor authentication, and obsolete in Microsoft.Data.SqlClient 7.0. Use managed identity for Azure-hosted workloads, a service principal off Azure, and interactive when a human is present

Gotcha: the generated connection string is not finished

az sql db show-connection-string -c ado.net -s <server> -n <database>

On 2.89.1 the output needs correcting by hand. The ODBC template emits Driver={ODBC Driver 13 for SQL Server}, five major versions out of date. --auth-type ADPassword emits the deprecated Authentication="Active Directory Password". --client accepts only ado.net, jdbc, odbc, php, php_pdo and sqlcmd, so asking it for a Node.js, Python or Go template and paraphrasing the answer invents a string. And no template carries retry settings.

Size the pool against workers, not sessions

The number an agent reaches for is the session limit, 30,000 on every vCore size, never the binding constraint. What throttles a workload is concurrent workers:

Compute, standard-series	Concurrent workers
General Purpose provisioned	100 per vCore (200 at 2 vCores, 1000 at 10)
General Purpose serverless	75 per max vCore (75 at 1, 300 at 4)

And workers are not requests. Default MAXDOP for a new database is 8, the limit is per task, and each task takes a worker, so one parallel request consumes several and the ceiling arrives sooner than a count of connections suggests.

Set an explicit maximum pool size: unlimited turns a traffic spike into 10928. Sum the pools across every instance, background workers and scheduled jobs included, and keep the total under the worker limit with headroom. In serverless and short-lived compute create one pool per process and reuse it; one per request or per invocation exhausts outbound ports and causes connection storms. A database already failing with 10928 is diagnose-resource-pressure.

The network path, when it works from one place but not another

The connection policy decides the route and its default differs by where the client is. Outside Azure it is Proxy: everything goes through the gateway and the client needs outbound 1433 only. Inside Azure it is Redirect: after the gateway handshake the session moves to the node hosting the database, so the client needs outbound 1433 plus 11000 to 11999 to the region's Azure SQL Database addresses. That asymmetry is why an application works from a laptop and times out from a virtual machine behind a restrictive network security group. Redirect is recommended; the cost is the wider outbound range.

az sql server conn-policy show -g <resource-group> -n <server>
az sql server conn-policy update -g <resource-group> -n <server> --connection-type Redirect

--connection-type accepts Default, Proxy and Redirect.

Check it worked

Three checks, run against the target the application will use, with -C added only for the container.

1. The connection is encrypted and landed in the user database. The first command above asks the engine itself, so it settles encryption without trusting the client. Expected: db is the user database and not master, encrypt_option is TRUE, and the command succeeding with no -C is itself the proof that the certificate validated.

2. A string left on master cannot be corrected at run time, which is why the database belongs in the connection string:

USE master;

Expected: Msg 40508, USE statement is not supported to switch between databases.

3. No secret reached the repository.

git grep -nEi "(password|pwd)=[^;\"']" -- . ":!*.md"

Expected: no output, exit 1. Any hit is a secret in source, whatever else works.

A fourth check on serverless or free: leave the database idle past the auto-pause delay, run the application cold, and confirm the code survives 40613.

Read the source when

Fetch the Microsoft Learn article rather than recalling it when an error number needs a cause (then diagnose-connection-errors), when the retry delays matter to a design (the transient errors article), when a resource limit is close (the vCore resource limits reference, the only current source for worker counts), or when a TLS minimum is being set (the connectivity settings article).

Do not

Do not present retry as an optional later step. 40613 on a first connect is the documented resume path, not an outage.
Do not set TrustServerCertificate=true or Encrypt=false to clear a certificate error against the cloud, not even temporarily. That change survives to production.
Do not carry the container's -C into the cloud string. It is the one line that must not travel.
Do not use Authentication=Active Directory Password, or copy it out of generated output, and do not paste a generated ODBC connection string without correcting the driver name.
Do not size a pool against the 30,000 session limit, or create one per request or per invocation.
Do not apply any of this to Azure SQL Managed Instance or SQL database in Fabric. Different connectivity model, different limits.