.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 11 October 2026
A connection string that worked for four years stops working after a dependency bump, and the exception reads "The certificate chain was issued by an authority that is not trusted". Nothing changed on the server. What changed is the driver default: Microsoft.Data.SqlClient at 4.0, the JDBC driver at 10.2, ODBC Driver 18 and sqlcmd once it started shipping in mssql-tools18. Encryption went from opt-in to on by default, and the self-signed certificate nobody had ever looked at became a hard failure.
What follows is the MSSQL connection string for each driver people really use, and what each one now expects about encryption.
Anatomy of an MSSQL connection string
The string is a list of semicolon-separated key/value pairs. Keys are case-insensitive and most of them have aliases. Data Source, Server, Address, Addr and Network Address all name the server. Initial Catalog and Database both name the database. User ID, UID and User all name the login. The aliases exist for compatibility with older providers and none of them behaves differently from the others. Pick one spelling per codebase. Mine is Server and Database, since the ODBC strings below use the same two words.
The server value is where the real variation lives:
Server=sql01 default instance, 1433 assumed
Server=sql01,14330 explicit port, comma not colon
Server=tcp:sql01,1433 force the TCP network library
Server=sql01\SQLEXPRESS named instance, resolved via SQL Server Browser
Server=sql01\SQLEXPRESS,1435 named instance with the port written out
Server=.\SQLEXPRESS named instance on this machineThe port the instance is really listening on takes one query to confirm, and I check it before arguing about anything else in the string. Writing that port into the server value is also the standard escape from error 26, since a named port means SQL Server Browser never gets consulted at all. One more thing about the separator in front of the number: it's a comma in every Microsoft client except JDBC, which wants a colon. That single character has cost me more time than the rest of the syntax put together.
Generated passwords run into a quoting rule that most strings never touch. A value containing a semicolon, a single quote or a double quote has to be wrapped in double quotation marks. If it contains both a semicolon and a double quote, wrap it in single quotes instead. A password ending in a semicolon inside an unquoted string parses as the end of the value, and the error you get says nothing about quoting.
.NET with Microsoft.Data.SqlClient
Server=tcp:sql01,1433;Database=mydb;User Id=myuser;Password=mypassword;Encrypt=True;TrustServerCertificate=False;SqlConnectionStringBuilder is the sane way to compose one of these at runtime. It escapes values for you and throws on a bad keyword at composition time, which moves a whole class of bug out of production and into a unit test. The full keyword set, including the ones nobody uses, sits in the ADO.NET connection string reference.
Encrypt defaults to true since version 4.0
The 4.0 release notes put it in one line: "Changed Encrypt connection string property to be true by default." Read the 4.0.0 release notes on GitHub and you'll see it sitting under breaking changes, which is exactly what it was. Against a server using the certificate SQL Server generates for itself, the client now validates that certificate, fails to build a chain to a trusted root and throws.
Two fixes. On a SQL Server container on your own laptop, TrustServerCertificate=True is the honest answer: traffic stays encrypted, validation is skipped and nothing on that machine matters. Anywhere else, install a certificate from a CA the client trusts and leave TrustServerCertificate=False. Skipping validation leaves the connection open to anyone who can get between the client and the server, which on a shared network is a real risk and not a theoretical one.
Encrypt=Strict on 5.0 and later
Server=tcp:sql01,1433;Database=mydb;User Id=myuser;Password=mypassword;Encrypt=Strict;Strict means TDS 8.0, where the TLS handshake happens first and protocol negotiation follows it. Under Strict, TrustServerCertificate is ignored. The certificate gets validated every time, so the lab escape hatch stops working the moment somebody switches the mode, which produces a support ticket reading "we made the connection more secure and now it doesn't connect".
System.Data.SqlClient is the old one
If your project still references System.Data.SqlClient, move it. That package is the legacy provider carried along inside .NET Framework, and new driver work goes into Microsoft.Data.SqlClient. The class names are identical, so migration is usually a namespace change in a handful of files. Budget time for one thing: the old provider defaulted Encrypt to false, so the switch is where the certificate error above lands on you.
MSSQL JDBC driver and URL format
The documented URL shape:
jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]A real one:
jdbc:sqlserver://sql01:1433;databaseName=mydb;user=myuser;password=mypassword;encrypt=true;trustServerCertificate=false;loginTimeout=30;Note the colon before the port. JDBC is the odd one out here, and moving between a .NET and a Java service in the same afternoon is how you end up typing sql01,1433 into a JDBC URL and getting a resolution error for a host that doesn't exist. Property names are camelCase too, so it's databaseName and never Database.
The current driver is 13.6.0, released in September 2026 and supported on Java 8, 11, 17, 21, 25 and 26. It ships as two jars, mssql-jdbc-13.6.0.jre8.jar for Java 8 and mssql-jdbc-13.6.0.jre11.jar for Java 11 and later, so match the jar to your runtime. The jre11 jar on a Java 8 runtime fails with an exception that mentions nothing about SQL Server. Every other property is documented on Microsoft's page for setting the JDBC connection properties.
Encryption follows the same story with different numbers. In driver 9.4 and earlier encrypt defaulted to false. In 10.2 it became true, described in the docs as a breaking change. From 11.2.0 the property became a string so it can take strict for TDS 8.0. A Java service untouched since 2021 breaks on its first driver upgrade.
MSSQL ODBC driver 18 versus 17
Driver={ODBC Driver 18 for SQL Server};Server=tcp:sql01,1433;Database=mydb;UID=myuser;PWD=mypassword;Encrypt=yes;TrustServerCertificate=no;Driver 18 flipped Encrypt from no to yes and added strict, where TrustServerCertificate is ignored and the certificate is always checked. As far as the string itself goes, nothing else about 17 and 18 differs. Full attribute table in the DSN and connection string attribute reference.
This is also why sqlcmd changed under your feet. The mssql-tools18 build defaults -N to m for mandatory, and the sqlcmd documentation flags it plainly: "This default is a breaking change from SQL Server 2022 (16.x) and earlier versions, where the default is -No." Against a stock instance with its self-signed certificate you add -C to trust it or -No to make encryption optional:
sqlcmd -S sql01,1433 -U myuser -C -Q "SELECT @@SERVERNAME"Python and Node.js
pyodbc
import os, pyodbc
conn = pyodbc.connect(
"Driver={ODBC Driver 18 for SQL Server};"
"Server=tcp:sql01,1433;"
"Database=mydb;"
f"UID={os.environ['MSSQL_USER']};"
f"PWD={os.environ['MSSQL_PASSWORD']};"
"Encrypt=yes;TrustServerCertificate=no;"
)
print(conn.execute("SELECT @@VERSION").fetchone()[0])pyodbc inherits everything from the MSSQL ODBC driver underneath, so every rule in the section above applies unchanged. On Ubuntu the driver name string has to match what's registered. odbcinst -q -d prints the registered names, so you can copy the exact spelling. Guessing between "ODBC Driver 18 for SQL Server" and the 17 form is how twenty minutes disappear.
node-mssql and tedious
const sql = require('mssql');
const config = {
server: 'sql01',
port: 1433,
database: 'mydb',
user: process.env.MSSQL_USER,
password: process.env.MSSQL_PASSWORD,
options: { encrypt: true, trustServerCertificate: false },
pool: { max: 10, min: 0, idleTimeoutMillis: 30000 }
};
const pool = await sql.connect(config);Set encrypt and trustServerCertificate explicitly here. Both defaults have moved between major versions of the driver.
Pool and timeout parameters worth setting
In ADO.NET, Connect Timeout defaults to 15 seconds. That's plenty on a local network and thin over a link between two regions, especially with TLS negotiation in play, so 30 is a reasonable value for anything crossing a WAN. Max Pool Size defaults to 100 per distinct connection string and Min Pool Size defaults to 0. Two connection strings differing by a single character produce two separate pools, so building strings by concatenation in a loop is a fine way to exhaust a server's connection limit while every pool looks half empty. A connection leak I chased for a morning turned out to be a health-check endpoint that opened a connection, ran SELECT 1 and never disposed it. Blank application name on every one of them.
Server=tcp:sql01,1433;Database=mydb;User Id=myuser;Password=mypassword;Encrypt=True;Connect Timeout=30;Max Pool Size=200;Application Name=billing-api;Set Application Name and it shows up in sys.dm_exec_sessions and in Extended Events. The same view carries the client interface version, which pairs with the version the server itself reports when you want to know if an old driver is still running somewhere in production. Where four services share a database, the application name is how you tell which one opened the session that's blocking everything.
