← Connection strings

SQL Server connection strings

SQL Server connection strings are key=value pairs separated by semicolons, in three dialects — ADO.NET, ODBC and JDBC — that disagree on key names, quoting and how the port is written. And since 2022 the drivers encrypt by default, which is behind most "certificate chain was issued by an authority that is not trusted" errors. Below is each form for the same database.

Build yours (SQL Server)

The parts of a SQL Server connection string

The shape

Server=tcp:host,1433;Database=…;User Id=…;Password=…;Encrypt=True;
Server=tcp:host,1433
A comma before the port, not a colon. host\INSTANCE for a named instance.
Database=
Initial Catalog= is the older name for the same thing.
Password=
Wrap in double quotes if it has a ; (ADO.NET), in {braces} for ODBC and JDBC.
Encrypt=True
The default in Microsoft.Data.SqlClient 4+ and ODBC Driver 18.

One SQL Server database, every form

User app_user, password s3cret-Pa55, database shop on db.example.com, tls, verify the certificate.

ADO.NET (Microsoft.Data.SqlClient)

C#, EF Core, PowerShell

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=s3cret-Pa55;Encrypt=True;TrustServerCertificate=False;

JDBC

Java (mssql-jdbc), DBeaver, IntelliJ, Spring

jdbc:sqlserver://db.example.com:1433;databaseName=shop;user=app_user;password=s3cret-Pa55;encrypt=true;trustServerCertificate=false

ODBC

pyodbc, R, Excel, anything on ODBC Driver 18

Driver={ODBC Driver 18 for SQL Server};Server=tcp:db.example.com,1433;Database=shop;Uid=app_user;Pwd=s3cret-Pa55;Encrypt=yes;TrustServerCertificate=no;

SQLAlchemy

Python with SQLAlchemy and pymssql · connected with on a real server

mssql+pymssql://app_user:s3cret-Pa55@db.example.com:1433/shop

pymssql takes TLS from FreeTDS settings, not the URL. With pyodbc use mssql+pyodbc:// and the ODBC Encrypt options.

Prisma

Node with Prisma

sqlserver://db.example.com:1433;database=shop;user=app_user;password=s3cret-Pa55;encrypt=true;trustServerCertificate=false

sqlcmd command

Shell · connected with on a real server

sqlcmd -S tcp:db.example.com,1433 -d shop -U app_user -N

sqlcmd asks for the password when -P is left out (or reads SQLCMDPASSWORD).

A password with special characters

The same database with the password p@ss/w#rd 1. URLs encode it, key=value forms quote it, and each form does it differently:

ADO.NET (Microsoft.Data.SqlClient)

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=p@ss/w#rd 1;Encrypt=True;TrustServerCertificate=False;

JDBC

jdbc:sqlserver://db.example.com:1433;databaseName=shop;user=app_user;password=p@ss/w#rd 1;encrypt=true;trustServerCertificate=false

ODBC

Driver={ODBC Driver 18 for SQL Server};Server=tcp:db.example.com,1433;Database=shop;Uid=app_user;Pwd=p@ss/w#rd 1;Encrypt=yes;TrustServerCertificate=no;

SQLAlchemy

mssql+pymssql://app_user:p%40ss%2Fw%23rd%201@db.example.com:1433/shop

CharacterIn a URLWhy it breaks
@%40Ends the user name and password: everything after the first unencoded @ is read as the host.
:%3ASeparates user from password, and host from port.
/%2FStarts the database name.
?%3FStarts the options.
#%23Starts a fragment: the rest of the string is thrown away.
%%25Starts an escape itself, so a literal % must be escaped too.
&%26Separates options.
+%2BSome parsers (PyMongo among them) read + as a space.
space%20Ends the string on a command line, and is trimmed by some parsers.

TLS: what each setting looks like

Shown in the ADO.NET (Microsoft.Data.SqlClient) form; the builder writes the matching setting for every other one.

No TLS

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=s3cret-Pa55;Encrypt=False;

TLS, don't check the certificate

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=s3cret-Pa55;Encrypt=True;TrustServerCertificate=True;

TLS, verify the certificate

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=s3cret-Pa55;Encrypt=True;TrustServerCertificate=False;

What catches people out on SQL Server

Encrypt is on by default now

Microsoft.Data.SqlClient 4.0, ODBC Driver 18, JDBC 10.2 and sqlcmd 18 all encrypt by default and check the certificate. A server with a self-signed certificate then fails until you install a trusted certificate or set TrustServerCertificate=True — which encrypts without checking who you are talking to.

The port goes after a comma

Server=host,1433 in ADO.NET and ODBC. host:1433 is read as a host name. JDBC is the odd one out: jdbc:sqlserver://host:1433.

Each dialect quotes differently

A ; in the password breaks the string. ADO.NET takes Password="pa;ss" (double quotes, "" for a quote inside); ODBC and JDBC take {pa;ss} (}} for a brace inside).

Named instances need the Browser service

host\SQLEXPRESS asks the SQL Server Browser service on UDP 1434 which port the instance is on. With a firewall in the way, give the port directly instead.

Windows authentication has no password

Integrated Security=True (ADO.NET), Trusted_Connection=yes (ODBC) or integratedSecurity=true (JDBC) log in as the Windows user running the program; leave User Id and Password out.

Connected. Now what?

Paste the same string into DBShifts to browse SQL Server with sensitive columns masked and every read logged, or to migrate it to another database with each table counted and checksummed afterwards.

Questions

What is the SQL Server connection string format?

ADO.NET: Server=tcp:host,1433;Database=db;User Id=user;Password=…;Encrypt=True;. JDBC: jdbc:sqlserver://host:1433;databaseName=db;user=…;password=…;encrypt=true. ODBC: Driver={ODBC Driver 18 for SQL Server};Server=tcp:host,1433;Database=db;Uid=…;Pwd=…;Encrypt=yes;.

How do I fix "The certificate chain was issued by an authority that is not trusted"?

The driver is encrypting and checking the server's certificate, and the server's is self-signed. Install a certificate from a CA your machine trusts, or — for development only — add TrustServerCertificate=True, which keeps encryption but skips the check.

How do I escape a semicolon in a SQL Server password?

ADO.NET: put the value in double quotes, Password="pa;ss". ODBC and JDBC: put it in braces, Pwd={pa;ss}, doubling any } inside.

What is the default SQL Server port?

1433 for the default instance. Named instances usually get a dynamic port, found through the Browser service on UDP 1434.