SQL Server connection strings for every driver

A SQL Server connection string needs five things: the host and port, the database, a login, a password, and an encryption setting that matches the server's certificate. The port is where most people go wrong - .NET and ODBC put it after a comma (db.example.net,14330), while JDBC and URL-style strings use a colon. Encryption is where the rest go wrong: every current Microsoft driver encrypts by default and validates the server's certificate, so a server with a self-signed certificate needs TrustServerCertificate=True (or the driver's spelling of it) until you install a trusted one. This post gives a working string for each common driver, the keywords worth knowing, and the errors each mistake produces.

The parts every connection string has#

Whatever the driver, the same information goes in. Only the spelling changes.

Server also accepts Data Source, Address and Addr in .NET, and Database also accepts Initial Catalog. They are synonyms; older documentation uses the long forms.

The examples below use db.example.net, port 14330 and a login called app_user. Replace them with your own values. On RE:NODE, a SQL Server plan shows the host and port to use; the server comes with an sa login and a database created for you, and the application should connect with a login of its own rather than sa - SQL Server logins, users and roles has the script to create one.

Encryption defaults, driver by driver#

This table explains most "it worked with the old driver" reports:

When encryption is on and certificate validation is not switched off, the driver checks that the server's certificate chains to a trusted authority and that its name matches the host you connected to. SQL Server generates a self-signed certificate at startup when it has not been given one, so a fresh server fails that check. Your options:

1. Trust the certificate - TrustServerCertificate=True. The connection is still encrypted; the server's identity is not verified. This is the usual setting for a hosted server with a self-signed certificate.
2. Install a trusted certificate on the server, for a name you connect with, and leave validation on. The proper fix when someone on the network path is part of your threat model.
3. Turn encryption off - Encrypt=False. Do not do this over the internet. The login packet is still encrypted, but every query and every row afterwards travels in plain text.

If you choose the second option, the name you connect with must be one the certificate covers. Connecting by IP address to a server whose certificate says sql.example.com fails validation even though the certificate is otherwise perfect, with "The target principal name is incorrect" in .NET and a host name mismatch in Java. Connect by the name, or tell the driver which name to expect (HostNameInCertificate in .NET and ODBC, hostNameInCertificate in JDBC). The certificate must also be trusted by the machine the application runs on, not just by your laptop - a container image with a minimal CA bundle can reject a certificate your desktop accepts.

Microsoft.Data.SqlClient 5.0 and later also accept Encrypt=Strict, which uses TDS 8.0 and negotiates TLS before anything else. It requires SQL Server 2022 and always validates the certificate, so it is not for self-signed setups.

.NET: Microsoft.Data.SqlClient and EF Core#

Server=tcp:db.example.net,14330;Database=app;User ID=app_user;Password=...;Encrypt=True;TrustServerCertificate=True;Application Name=orders-api

The tcp: prefix forces TCP and is optional. Application Name shows up in sys.dm_exec_sessions and in Query Store, which makes "which app is running this query" a one-line answer. Other keywords worth knowing:

In ASP.NET Core, put the string in configuration as ConnectionStrings:Default and supply it in production with an environment variable called ConnectionStrings__Default. EF Core reads it with UseSqlServer(builder.Configuration.GetConnectionString("Default")). EF Core 7 and later use Microsoft.Data.SqlClient 5, so upgrading an app from EF Core 6 is often the moment the certificate error first appears.

Pooling deserves a sentence of its own. SqlClient keeps one pool per distinct connection string per process, so two strings that differ only in the order of their keywords, or in Application Name, create two pools. Build the string once and reuse it. A hundred connections per pool is far more than a small SQL Server needs; if several instances of an app share one Express server, lower Max Pool Size so their combined total stays sensible, and treat "timeout expired while obtaining a connection from the pool" as a sign of connections held too long rather than a pool too small. Connection pools and limits explains why.

Build connection strings in code with SqlConnectionStringBuilder rather than string concatenation; it escapes values correctly:

var csb = new SqlConnectionStringBuilder{    DataSource = "tcp:db.example.net,14330",    InitialCatalog = "app",    UserID = "app_user",    Password = Environment.GetEnvironmentVariable("DB_PASSWORD"),    Encrypt = SqlConnectionEncryptOption.Mandatory,    TrustServerCertificate = true};await using var conn = new SqlConnection(csb.ConnectionString);

If your code still has using System.Data.SqlClient;, change the package and the namespace to Microsoft.Data.SqlClient. The old package is deprecated and stopped receiving new features long ago. .NET with PostgreSQL, MySQL or SQL Server covers pooling and EF Core provider setup in more depth.

Java: the JDBC driver#

jdbc:sqlserver://db.example.net:14330;databaseName=app;user=app_user;password=...;encrypt=true;trustServerCertificate=true;applicationName=orders-service

JDBC is the one Microsoft driver that uses a colon before the port, because the URL format comes from Java conventions. A comma there is an error. Properties follow the host after semicolons. The Maven coordinates are com.microsoft.sqlserver:mssql-jdbc; pick the artifact variant that matches your Java version (the version string ends in .jre11, .jre17 and so on).

In Spring Boot:

spring.datasource.url=jdbc:sqlserver://db.example.net:14330;databaseName=app;encrypt=true;trustServerCertificate=truespring.datasource.username=app_userspring.datasource.password=${DB_PASSWORD}spring.datasource.hikari.maximum-pool-size=10

HikariCP, Spring Boot's default pool, uses 10 connections by default, which is a sensible size for a small server. Driver 10.2 changed encrypt to default to true, so a Spring app upgraded across that version starts failing with PKIX path building failed - the Java way of saying the certificate is not trusted.

ODBC: Driver 18, PHP and anything else#

ODBC is the common denominator: PHP's sqlsrv and pdo_sqlsrv extensions, Python's pyodbc, R, Excel and many reporting tools sit on top of it.

Driver={ODBC Driver 18 for SQL Server};Server=tcp:db.example.net,14330;Database=app;UID=app_user;PWD=...;Encrypt=yes;TrustServerCertificate=yes

The Driver value must match an installed driver name exactly, braces included. On Debian and Ubuntu, Microsoft's package is msodbcsql18, installed from Microsoft's package repository, and it asks you to accept its licence (ACCEPT_EULA=Y in scripts). Driver 18 encrypts by default; Driver 17 did not, which is why moving from 17 to 18 breaks connections that never mentioned encryption.

Values containing ; or starting with { go inside braces, with any } doubled: PWD={pa;ss}}word} for the password pa;ss}word.

PHP, with PDO:

$pdo = new PDO(    "sqlsrv:Server=db.example.net,14330;Database=app;TrustServerCertificate=1",    "app_user",    getenv("DB_PASSWORD"),    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

Python: pyodbc, SQLAlchemy and Django#

pyodbc takes the ODBC string directly:

import osimport pyodbcconn = pyodbc.connect(    "Driver={ODBC Driver 18 for SQL Server};"    "Server=tcp:db.example.net,14330;Database=app;"    f"UID=app_user;PWD={os.environ['DB_PASSWORD']};"    "Encrypt=yes;TrustServerCertificate=yes")

SQLAlchemy uses a URL, so the port goes after a colon there and the dialect converts it to the comma form for ODBC. Driver name and options go in the query string, URL-encoded:

from sqlalchemy import create_enginefrom sqlalchemy.engine import URLurl = URL.create(    "mssql+pyodbc",    username="app_user",    password=os.environ["DB_PASSWORD"],    host="db.example.net",    port=14330,    database="app",    query={"driver": "ODBC Driver 18 for SQL Server", "TrustServerCertificate": "yes"},)engine = create_engine(url, pool_size=5, max_overflow=5, pool_pre_ping=True)

URL.create escapes special characters in the password for you, which hand-written URLs often get wrong. pool_pre_ping=True checks a pooled connection before use, so a connection closed by the network overnight does not fail the first request of the morning.

Django uses the mssql-django backend, which also sits on pyodbc:

DATABASES = {    "default": {        "ENGINE": "mssql",        "NAME": "app",        "USER": "app_user",        "PASSWORD": os.environ["DB_PASSWORD"],        "HOST": "db.example.net",        "PORT": "14330",        "OPTIONS": {            "driver": "ODBC Driver 18 for SQL Server",            "extra_params": "TrustServerCertificate=yes",        },    }}

There is also pymssql, which talks to SQL Server through the FreeTDS library rather than Microsoft's ODBC driver. It needs no separate driver installation, which makes it attractive in slim containers, but its encryption and TLS behaviour depends on how FreeTDS was built and configured, so check that the connection is actually encrypted before relying on it over the internet. For most projects, pyodbc with Driver 18 is the better-supported path because it uses the same driver Microsoft maintains for every other language.

Whichever library you use, keep the pool small. A Python web app running four worker processes, each with a SQLAlchemy pool of five plus five overflow, can open forty connections on its own. That is fine for one app on its own server and too many when three apps share a small Express instance.

Node.js: mssql and tedious#

The mssql package is the usual choice; it wraps the pure-JavaScript tedious driver and adds pooling.

import sql from "mssql";const pool = await sql.connect({  server: "db.example.net",  port: 14330,  database: "app",  user: "app_user",  password: process.env.DB_PASSWORD,  options: { encrypt: true, trustServerCertificate: true },  pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },});const result = await pool.request()  .input("id", sql.Int, 42)  .query("SELECT name FROM dbo.Customers WHERE id = @id");

The host and port are separate properties here, so the comma-versus-colon question does not arise. The pool settings shown are the package's defaults. Use .input() parameters for every value from a user; string-building SQL is how injection happens in any language. mssql also accepts an ADO.NET-style connection string - sql.connect("Server=db.example.net,14330;Database=app;...") - which is convenient when the same string is shared with a .NET service.

Keeping connection strings out of your code#

A connection string with a password in it is a credential. Wherever it lives, someone can read it.

• Environment variables are the baseline. Every runtime above reads them, and on a hosting panel they are set on the server rather than committed to the repository. Environment variables and secrets covers the patterns.
• Never commit them. Not in appsettings.json, not in application.properties, not in .env. Add the files that hold local secrets to .gitignore before the first commit, because removing a password from Git history is far harder than never adding it.
• One login per application, with only the permissions it needs, so one leaked string exposes one app's data and not the server.
• Rotate after a leak. Change the password with ALTER LOGIN, update the environment variable, restart. A pool keeps existing connections open until they are recycled, so restart rather than waiting.

Test a new connection string before you put it in an application, so you are debugging one thing at a time. sqlcmd takes the same pieces on the command line - sqlcmd -S tcp:db.example.net,14330 -U app_user -d app -C connects, prompts for the password, and -C trusts the server certificate - and a successful SELECT DB_NAME(); proves the host, port, credentials and database are right. If that works and the application does not, the problem is in how the application builds or reads its string, usually an environment variable that is not set where you think it is.

Database security checklist lists the rest of what belongs around a database exposed to the internet.

Troubleshooting by error message#

`The certificate chain was issued by an authority that is not trusted` (.NET, ODBC) or `PKIX path building failed` (Java) - encryption is on, the certificate is self-signed. Add the trust setting for your driver.

`A network-related or instance-specific error occurred` or `Login timeout expired` - the driver did not reach the server. Check the port separator (comma for .NET and ODBC, colon for JDBC), the port number, and whether the port is reachable from where the app runs.

`Login failed for user 'app_user'` (error 18456) - wrong password, wrong login name, or the login has no access to the database named in the string. Connect with the same credentials in SSMS to separate a code problem from a credential problem; connecting with SSMS walks through that.

`Cannot open database "app" requested by the login. The login failed.` - the login exists but has no user in that database, or the database name is wrong. Create the user in the database for that login.

`Data source name not found and no default driver specified` (ODBC) - the Driver name does not match an installed driver. List them with odbcinst -q -d on Linux.

`Keyword not supported: 'port'` (.NET) - SqlClient has no Port keyword. Put the port after a comma in Server.

FAQ#

Why does SQL Server use a comma before the port?

It is the syntax SQL Server's client libraries have always used, and it carried over to every Microsoft driver except JDBC. A colon is read as part of the host name. URL-based libraries such as SQLAlchemy accept a colon and convert it for you.

Is TrustServerCertificate=True safe?

It keeps the connection encrypted and skips verifying the server's identity. That protects against passive eavesdropping, not against an active attacker on the network path who presents their own certificate. For a hosted server with a self-signed certificate it is the practical choice; a trusted certificate with validation is stronger.

What is the default port for SQL Server?

1433 over TCP. Hosted servers frequently use another port, so always use the one you were given, written after the comma.

Do I need ODBC Driver 18 or is 17 fine?

Driver 17 still works, but 18 is current and receives the fixes. When you upgrade, add TrustServerCertificate=yes or a trusted certificate, because 18 encrypts by default and 17 did not.

Can I use Windows authentication with a hosted SQL Server?

Not normally. Windows authentication needs the client and server in a domain that trusts each other. A hosted server, especially one on Linux, uses SQL authentication with a login and password.