SQL Server connection strings: ADO.NET, JDBC, ODBC and sqlserver:// URLs

The formats at a glance

Each driver family has its own syntax. The parts are the same: a server (host, port or instance), a database, a user and password, and encryption settings.

The two traps when moving between them: the port separator (a comma in ADO.NET, ODBC and sqlcmd -S; a colon in JDBC and URLs) and the encryption defaults, which differ from driver to driver (below).

ADO.NET

Server=tcp:<host>,<port>;Initial Catalog=<database>;User ID=<user>;Password=<password>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

keyword=value pairs separated by semicolons. Keywords aren’t case-sensitive, and most have synonyms:

The Azure portal’s string is this form, with Persist Security Info=False, MultipleActiveResultSets=False and Encrypt=True;TrustServerCertificate=False already set.

Quoting. A value that contains a semicolon, a single quote or a double quote goes in double quotes. If it contains a semicolon and a double quote, use single quotes. If it contains both kinds of quote, double the one you enclose it in wherever it appears inside:

Password="p;ss'word"
Password='p;ss"word'
Password="it's ""quoted"";"

Leading or trailing spaces in a value also need quotes.

ODBC

Driver={ODBC Driver 18 for SQL Server};Server=tcp:<host>,<port>;Database=<database>;UID=<user>;PWD=<password>;Encrypt=yes;TrustServerCertificate=no;

The same idea with ODBC’s own keywords: Server (or Address, Addr), Database, UID, PWD, Encrypt, TrustServerCertificate, HostnameInCertificate (Driver 18.0 and later), ApplicationIntent, MultiSubnetFailover, Trusted_Connection. Values are yes and no.

Encrypt in Driver 18 takes yes (or mandatory, the default), no (or optional) and strict. Driver 17 and earlier default to no, so a connection string that worked with Driver 17 can stop working after an upgrade: Driver 18 now encrypts and checks the certificate.

Quoting. Wrap a value in braces when it contains a semicolon, and double any closing brace inside it: the password Ab1;x}y=z9 is written PWD={Ab1;x}}y=z9}. We checked this with ODBC Driver 18.6 and unixODBC 2.3.9 against SQL Server 2022 (16.0.4295.3) in a temporary container: the braced form signed in, and the unbraced PWD=Ab1;x}y=z9 failed, because the driver read the password as Ab1 and the rest as a bad attribute:

[28000][Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'seo_odbc'.
[01S00][Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute

JDBC

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

jdbc:sqlserver://<host>:1433;databaseName=<database>;user=<user>;password=<password>;encrypt=true;trustServerCertificate=false

The host and port come after //; everything else is a property=value after a semicolon.

Quoting. Put a value in braces to include semicolons, spaces or quotes: password={p;ss}. From driver 8.4, a value can contain braces too, with each closing brace doubled: password={pass";{}}word} is the password pass";{}word.

URL forms

sqlserver:// (Go’s go-mssqldb)

sqlserver://<user>:<password>@<host>:<port>?database=<database>&encrypt=true
sqlserver://<user>:<password>@<host>/<instance>?database=<database>

A real URL: the instance is the path, everything else is a query parameter, and special characters in the user name or password are percent-encoded (my%7Bpass is my{pass). The parameters include database, encrypt, TrustServerCertificate, hostNameInCertificate, ApplicationIntent and connection timeout (connection+timeout=30). Two defaults differ from Microsoft’s other drivers: encrypt defaults to false (only the login is encrypted), and TrustServerCertificate defaults to true when encrypt isn’t given. encrypt=disable turns encryption off entirely. go-mssqldb also accepts ADO.NET strings and, prefixed with odbc:, ODBC strings.

Prisma

sqlserver://<host>:<port>;database=<database>;user=<user>;password=<password>;encrypt=true

Not a URL after the host: options follow semicolons. encrypt defaults to true and trustServerCertificate to false; the database defaults to master and the schema to dbo. A named instance goes in the host: sqlserver://mycomputer\sql2019;database=sample;…. To use : \ = ; / [ ] { } in a value, wrap it in braces: password={Pass:Word;}.

mssql:// (Node’s mssql, version 6 and earlier)

mssql://<user>:<password>@<host>:1433/<database>?encrypt=true
mssql://<user>:<password>@<host>/<instance>/<database>?encrypt=true

The mssql package for Node documented these URLs up to version 6. Its current documentation shows ADO.NET-style strings instead (Server=localhost,1433;Database=…;User Id=…;Password=…;Encrypt=true). It encrypts by default, and from version 7 trustServerCertificate defaults to false.

Named instances

A named instance (HOST\SQLEXPRESS) listens on a port that the SQL Server Browser service looks up over UDP port 1434. Give the instance name and the client asks Browser; give the port and it doesn’t need to. See named instances for setting up the server side.

HOST:14330 with a colon isn’t a port to sqlcmd or ODBC. sqlcmd 18.6 rejected -S host.docker.internal:14399:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SQL Server Network Interfaces: Connection string is not valid [87]. .
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to host.docker.internal:14399. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

The same server as host.docker.internal,14399 connected.

Encrypt and TrustServerCertificate

Defaults: ADO.NET (Microsoft.Data.SqlClient 4.0+) True; ODBC Driver 18 yes; JDBC 10.2+ true; Prisma true; Node mssql true; go-mssqldb false. The ODBC-based sqlcmd 18 checks the certificate by default; Microsoft’s Go-based sqlcmd checks it only with -N.

What ODBC Driver 18.6 did against SQL Server 2022 (16.0.4295.3) in a temporary container, whose certificate is self-signed (it generates one at start):

(no Encrypt)                                  SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]
Encrypt=optional                              connected
Encrypt=mandatory;TrustServerCertificate=yes  connected
Encrypt=strict;TrustServerCertificate=yes     TCP Provider: Error code 0x2746

The last one failed on the server’s side: strict encryption needs a configured certificate, and the server logged A valid TLS certificate is not configured to accept strict (TDS 8.0 and above) connections. With strict, TrustServerCertificate is ignored.

Use TrustServerCertificate=True only where you control the network, such as a container on your Mac. Against a server with a real certificate, keep it False; if you connect through a name the certificate doesn’t contain, set HostNameInCertificate rather than turning checks off. More in the certificate chain isn’t trusted.

ApplicationIntent and MultiSubnetFailover

ApplicationIntent=ReadOnly tells an availability group listener that you only read, so it can route you to a readable secondary replica. It isn’t a read-only switch. On a server that isn’t in an availability group nothing changes: with sqlcmd -K ReadOnly against SQL Server 2022, we created a table and inserted a row (inside a transaction we rolled back), and the database still reported READ_WRITE.

MultiSubnetFailover=True makes the driver try all of a listener’s IP addresses at once, for availability groups and failover cluster instances that span subnets. Microsoft says to always set it for those.

Special characters

More in special characters in passwords.

In Inlet

Paste a connection string into a new connection and Inlet fills in the form. It reads sqlserver:// and mssql:// URLs, JDBC (jdbc:sqlserver://…;databaseName=…) and ADO.NET strings like the one the Azure portal gives (Server=tcp:<server>.database.windows.net,1433;Initial Catalog=…;…), and ODBC strings. A named instance can be in the host (HOST\INSTANCE), in go-mssqldb’s path (sqlserver://HOST/INSTANCE?database=…) or in node-mssql’s (mssql://HOST/INSTANCE/database), and quoted values keep their semicolons ('…' and "…" in ADO.NET, {…} in ODBC and JDBC). It can also import connections from TablePlus. Inlet has its own SQL Server driver, so there’s no ODBC driver or FreeTDS to install.

In the form, encryption can be off (the sign-in is still encrypted when the server can), encrypted without checking the certificate, or verified (the certificate, and the host name), with strict encryption (TDS 8.0) as an option. Named instances (HOST\INSTANCE) are looked up through SQL Server Browser, read-only intent is there for availability group secondaries, and Azure SQL’s redirect routing is followed. Passwords go in the Keychain, or Inlet asks every time. Inlet signs in with SQL Server authentication; Microsoft Entra ID and Windows sign-in aren’t supported yet.