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
| Character | In a URL | Why it breaks |
|---|---|---|
| @ | %40 | Ends the user name and password: everything after the first unencoded @ is read as the host. |
| : | %3A | Separates user from password, and host from port. |
| / | %2F | Starts the database name. |
| ? | %3F | Starts the options. |
| # | %23 | Starts a fragment: the rest of the string is thrown away. |
| % | %25 | Starts an escape itself, so a literal % must be escaped too. |
| & | %26 | Separates options. |
| + | %2B | Some parsers (PyMongo among them) read + as a space. |
| space | %20 | Ends 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.
More on SQL Server
Other databases
PostgreSQL connection strings
postgresql://user:password@host:5432/database?sslmode=verify-full
MySQL connection strings
mysql://user:password@host:3306/database
MariaDB connection strings
jdbc:mariadb://host:3306/database?user=…&password=…&sslMode=verify-full
Oracle connection strings
host:1521/service_name
MongoDB connection strings
mongodb://user:password@host:27017/database?authSource=admin&tls=true
SQLite connection strings
sqlite:////var/data/app.db