(opens in a new tab)

SQL Server Connection String Guide (MSSQL)

September 24, 2026 by Chat2DB Team

A SQL Server connection string looks simple: a server name, a database, some credentials. In practice, every driver spells the keywords a little differently, named instances and ports follow their own rules, and recent driver releases changed the encryption defaults in a way that broke many connection strings that had worked for years. If you have upgraded to Microsoft.Data.SqlClient 4.x or ODBC Driver 18 and suddenly see "The certificate chain was issued by an authority that is not trusted", you have met that change.

This guide walks through the mssql connection string formats you are most likely to need: ADO.NET with Microsoft.Data.SqlClient, ODBC Driver 18, the Microsoft JDBC driver, sqlcmd, and Python with pyodbc and SQLAlchemy. For each one it covers the server address syntax, the three authentication families (Windows, SQL Server logins, and Microsoft Entra ID), and the encryption settings, followed by a section on Azure SQL and a troubleshooting checklist.

Anatomy of a SQL Server connection string

Almost every SQL Server connection string is a list of key=value pairs separated by semicolons. Keywords are case-insensitive in ADO.NET and ODBC, and many have synonyms. The pieces you always need are:

• Where: the server host, optionally an instance name or port.
• What: the database to use after login.
• Who: the authentication method and credentials.
• How: transport options such as encryption, certificate validation, timeouts, and application name.

JDBC is the exception in shape: it uses a URL prefix (jdbc:sqlserver://host:port) followed by semicolon-separated properties. SQLAlchemy uses its own URL format and passes options through to the underlying driver.

Server address, ports and named instances

The default SQL Server instance listens on TCP port 1433. A named instance (for example SQLEXPRESS) usually listens on a dynamic port, and clients discover that port by asking the SQL Server Browser service on UDP port 1434. That leads to three common address styles:

myhost                  # default instance on port 1433
myhost\SQLEXPRESS       # named instance, port resolved through SQL Server Browser
myhost,14330            # explicit port (ADO.NET and ODBC use a comma, not a colon)
tcp:myhost,1433         # force the TCP protocol

A few rules worth remembering:

1. ADO.NET and ODBC separate host and port with a comma. myhost:1433 is not valid in those drivers.
2. If you give both an instance name and a port (myhost\SQLEXPRESS,50123), the port wins and the Browser lookup is skipped.
3. If the Browser service is stopped or UDP 1434 is blocked by a firewall, named instance connections fail with a "network-related or instance-specific error". Either start the Browser service, open UDP 1434, or configure the instance with a static port and connect with host,port.
4. For local development, . or localhost refers to the default instance on the same machine, and (localdb)\MSSQLLocalDB refers to SQL Server Express LocalDB.

Authentication options

SQL Server supports three families of authentication, and each driver exposes them with slightly different keywords:

• Windows authentication (integrated security): the client process's Windows or Kerberos identity is used. No password appears in the connection string. This only works when the server is joined to a domain or trusts the client's identity.
• SQL Server authentication: a login and password stored in SQL Server. The server must have "SQL Server and Windows Authentication mode" enabled.
• Microsoft Entra ID (formerly Azure Active Directory): token-based authentication used mainly with Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2022 connected to Azure Arc. Modes include interactive, managed identity, service principal, and a "default" chain that tries several credential sources.

ADO.NET with Microsoft.Data.SqlClient

Microsoft.Data.SqlClient is the current .NET data provider for SQL Server. The older System.Data.SqlClient still exists in .NET Framework but receives only limited updates, so new code should use the Microsoft package.

A SQL authentication connection string:

Server=tcp:sql01.corp.local,1433;Database=Sales;User ID=app_user;Password=S3cure!Pass;Encrypt=True;TrustServerCertificate=False;Connect Timeout=30;Application Name=SalesApi

A Windows authentication connection string:

Server=sql01.corp.local\PROD;Database=Sales;Integrated Security=True;Encrypt=True

Common keywords and what they do:

Rather than concatenating strings, build them with SqlConnectionStringBuilder. It escapes values correctly, which matters when a password contains a semicolon or a quote:

using Microsoft.Data.SqlClient;
 
var builder = new SqlConnectionStringBuilder
{
    DataSource = "tcp:sql01.corp.local,1433",
    InitialCatalog = "Sales",
    UserID = "app_user",
    Password = Environment.GetEnvironmentVariable("SQL_PASSWORD"),
    Encrypt = SqlConnectionEncryptOption.Mandatory,
    TrustServerCertificate = false,
    ApplicationName = "SalesApi"
};
 
await using var conn = new SqlConnection(builder.ConnectionString);
await conn.OpenAsync();
 
await using var cmd = new SqlCommand("SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME()", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)} | {reader.GetString(2)}");
}

The query at the end is a handy smoke test: it confirms which server, which database, and which login you actually ended up with.

For Microsoft Entra authentication, set the Authentication keyword and omit the password where the mode does not need one:

Server=tcp:myserver.database.windows.net,1433;Database=Sales;Authentication=Active Directory Default;Encrypt=True

Active Directory Default tries a chain of credential sources (environment variables, managed identity, Visual Studio, Azure CLI, and others), which makes the same string work on a developer laptop and in an Azure-hosted service. Other values include Active Directory Interactive, Active Directory Managed Identity, and Active Directory Service Principal.

ODBC Driver 18 for SQL Server

ODBC is what many non-.NET tools use under the hood, including pyodbc, PHP's sqlsrv extension, and a large number of BI tools. The driver name must match exactly what is registered on the machine, including the braces:

Driver={ODBC Driver 18 for SQL Server};Server=tcp:sql01.corp.local,1433;Database=Sales;Uid=app_user;Pwd=S3cure!Pass;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

For Windows authentication, replace Uid and Pwd with Trusted_Connection=yes. On Linux and macOS, integrated authentication requires a working Kerberos setup (a valid ticket from kinit).

To see which drivers are installed:

# Linux / macOS (unixODBC)
odbcinst -q -d
 
# Windows PowerShell
Get-OdbcDriver | Select-Object Name, Platform

If you only see ODBC Driver 17 for SQL Server, either install version 18 or change the Driver value. Version 17 defaults to no encryption, while version 18 defaults to Encrypt=yes, so the same string can behave differently between the two.

Entra ID authentication in ODBC uses the Authentication keyword with values such as ActiveDirectoryInteractive, ActiveDirectoryMsi, ActiveDirectoryServicePrincipal, and ActiveDirectoryIntegrated. Note that the ODBC spellings have no spaces, unlike SqlClient.

JDBC with the Microsoft JDBC Driver

The Microsoft JDBC Driver for SQL Server (mssql-jdbc) uses a URL:

jdbc:sqlserver://sql01.corp.local:1433;databaseName=Sales;user=app_user;password=S3cure!Pass;encrypt=true;trustServerCertificate=false;loginTimeout=30;applicationName=SalesApi

Here the port is separated by a colon, which trips up people who copy an ADO.NET server value. Named instances can be written two ways:

jdbc:sqlserver://sql01.corp.local\PROD;databaseName=Sales;...
jdbc:sqlserver://sql01.corp.local;instanceName=PROD;databaseName=Sales;...

In Java string literals the backslash must be escaped ("sql01\\PROD"), so the instanceName property is often easier to read.

Windows authentication uses integratedSecurity=true. On Windows with the default native scheme, the driver needs the mssql-jdbc_auth DLL matching the driver version on the java.library.path. Alternatively, authenticationScheme=JavaKerberos uses pure-Java Kerberos and works on Linux too.

Starting with JDBC driver 10.2, encrypt defaults to true, matching the change in the other drivers. For Entra ID, use the authentication property, for example authentication=ActiveDirectoryMSI for a managed identity or authentication=ActiveDirectoryServicePrincipal together with the application ID as user and the secret as password.

A minimal Java check:

import java.sql.*;
 
public class Ping {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:sqlserver://sql01.corp.local:1433;databaseName=Sales;"
                   + "encrypt=true;trustServerCertificate=false;loginTimeout=30";
        try (Connection c = DriverManager.getConnection(url, "app_user", System.getenv("SQL_PASSWORD"));
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("SELECT @@VERSION")) {
            rs.next();
            System.out.println(rs.getString(1));
        }
    }
}

sqlcmd from the command line

sqlcmd does not take a connection string; it takes flags. Two flavors exist: the ODBC-based sqlcmd shipped in the mssql-tools18 package, and the newer Go-based sqlcmd (go-sqlcmd). The common flags are the same:

# SQL authentication, explicit port
sqlcmd -S tcp:sql01.corp.local,1433 -d Sales -U app_user -P 'S3cure!Pass' -Q "SELECT @@SERVERNAME;"
 
# Windows / Kerberos authentication
sqlcmd -S sql01.corp.local\PROD -d Sales -E -Q "SELECT SUSER_SNAME();"
 
# Trust a self-signed certificate (development only)
sqlcmd -S localhost -U sa -P 'YourStrong!Passw0rd' -C -Q "SELECT 1;"

• -S is the server, in the same host,port or host\instance form as ODBC.
• -E requests trusted (integrated) authentication.
• -C trusts the server certificate, the equivalent of TrustServerCertificate=yes.
• -N controls the encryption mode.

Because the mssql-tools18 build uses ODBC Driver 18, it encrypts by default. Connecting to a fresh Docker container that uses a self-signed certificate therefore fails until you add -C. Avoid passing -P on shared machines, since the password becomes visible in the process list; set the SQLCMDPASSWORD environment variable instead.

Python: pyodbc and SQLAlchemy

pyodbc

pyodbc passes the string straight to the ODBC driver, so everything from the ODBC section applies:

import os
import pyodbc
 
conn_str = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=tcp:sql01.corp.local,1433;"
    "Database=Sales;"
    "Uid=app_user;"
    f"Pwd={os.environ['SQL_PASSWORD']};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;"
)
 
with pyodbc.connect(conn_str) as conn:
    row = conn.cursor().execute("SELECT @@SERVERNAME, DB_NAME()").fetchone()
    print(row)

If a password contains a semicolon or starts with a brace, wrap the value in braces and double any closing brace inside it, for example Pwd={pa;ss}}word}.

SQLAlchemy

SQLAlchemy uses the mssql+pyodbc dialect. The safest way to avoid URL-encoding mistakes is URL.create:

import os
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL
 
url = URL.create(
    "mssql+pyodbc",
    username="app_user",
    password=os.environ["SQL_PASSWORD"],
    host="sql01.corp.local",
    port=1433,
    database="Sales",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "Encrypt": "yes",
        "TrustServerCertificate": "no",
    },
)
 
engine = create_engine(url, pool_pre_ping=True)
with engine.connect() as conn:
    print(conn.execute(text("SELECT SUSER_SNAME()")).scalar())

If you already have a working ODBC string, you can pass it through unchanged with odbc_connect:

import urllib.parse
from sqlalchemy import create_engine
 
odbc = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:sql01.corp.local,1433;Database=Sales;Trusted_Connection=yes;Encrypt=yes;"
engine = create_engine("mssql+pyodbc:///?odbc_connect=" + urllib.parse.quote_plus(odbc))

This is often the fastest way to move a string that works in pyodbc into SQLAlchemy without rewriting it.

Encrypt, TrustServerCertificate and the certificate chain error

What changed

For a long time the SQL Server drivers defaulted to Encrypt=false. Login packets were still protected, but the rest of the session was sent in clear text unless the server forced encryption. Microsoft changed that default:

• Microsoft.Data.SqlClient 4.0 changed the default of Encrypt to true.
• ODBC Driver 18 defaults to Encrypt=yes.
• JDBC driver 10.2 defaults to encrypt=true.

With encryption on and TrustServerCertificate off, the client must validate the server's TLS certificate: it must chain to a trusted root, and the host name you connect with must match the certificate. Many on-premises servers use the self-signed certificate that SQL Server generates at startup, which no client trusts. The result is this error:

A connection was successfully established with the server, but then an error occurred
during the login process. (provider: SSL Provider, error: 0 - The certificate chain was
issued by an authority that is not trusted.)

How to fix it properly

Work through these options in order of preference:

1. Install a real certificate on SQL Server. Issue a certificate from your internal CA or a public CA whose subject or SAN contains the exact name clients use (for example sql01.corp.local). Assign it in SQL Server Configuration Manager under the instance's Protocols properties, give the service account read access to the private key, and restart the service. Clients that trust your CA will now connect with Encrypt=True;TrustServerCertificate=False.
2. Connect using the name on the certificate. If the certificate is issued for sql01.corp.local but you connect to an IP address or a short host name, validation fails. SqlClient 5.0+ and JDBC also let you set HostNameInCertificate (hostNameInCertificate in JDBC) when the names legitimately differ, such as when connecting through an alias or listener.
3. Trust the existing certificate on the client. For a self-signed certificate you control, export it and import it into the client's trusted root store (Windows certificate store, the OS CA bundle on Linux, or the Java truststore for JDBC).
4. Set TrustServerCertificate=True only for development. It keeps the traffic encrypted but removes protection against man-in-the-middle attacks, because any certificate is accepted.

Setting Encrypt=False also makes the error disappear, but if the server has "Force Encryption" enabled the connection is still encrypted and still needs a trusted certificate, and on the open network you lose encryption entirely.

Encrypt=Strict and TDS 8.0

SQL Server 2022 introduced TDS 8.0, where the TLS handshake happens before any TDS traffic, similar to HTTPS. Clients opt in with Encrypt=Strict (SqlClient 5.0 and later) or Encrypt=strict in newer ODBC 18 releases. In strict mode, TrustServerCertificate is ignored, so the certificate must genuinely validate.

Connecting to Azure SQL Database

Azure SQL Database connection strings follow the same rules with a few fixed points:

Server=tcp:myserver.database.windows.net,1433;Database=Sales;User ID=app_user;Password=S3cure!Pass;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30

• The server name is always yourserver.database.windows.net and the port is 1433.
• Azure SQL presents a certificate from a public CA, so TrustServerCertificate=False works out of the box. There is no reason to set it to true.
• Always specify Database. Logging in to master and then switching with USE is not supported in Azure SQL Database.
• The server-level firewall must allow the client IP, or the client must reach it through a private endpoint. A blocked IP produces an error that includes the client address, which is useful when adding the rule.
• Traditional Windows integrated authentication against an on-premises domain is not available. Use SQL authentication or Microsoft Entra ID; the Active Directory Default and managed identity modes are the usual choice for apps hosted in Azure because they avoid storing a password.

Troubleshooting checklist

When a connection string fails, check these in order:

1. Can you reach the port? Test TCP connectivity first, for example nc -vz sql01.corp.local 1433 on Linux or Test-NetConnection sql01.corp.local -Port 1433 in PowerShell. No connection here means firewall, DNS, or a disabled TCP/IP protocol, not a driver problem.
2. Is it a named instance? Confirm SQL Server Browser is running and UDP 1434 is open, or switch to an explicit port.
3. Is the login enabled for the mode you use? Error 18456 means the server was reached but the login failed. The state number in the SQL Server error log explains why (wrong password, disabled login, SQL authentication not enabled, or no access to the requested database).
4. Did the driver version change? An upgrade to SqlClient 4+, ODBC 18, or JDBC 10.2+ with no string changes is the classic cause of the certificate chain error.
5. Are special characters escaped? Use a builder class or braces rather than hand-concatenated strings.

If you want to generate a correctly formatted string for each driver without memorizing the keyword differences, the free SQL Server connection string builder (opens in a new tab) produces ADO.NET, ODBC, JDBC and SQLAlchemy formats from the same inputs. Once connected, a GUI client such as Chat2DB (opens in a new tab) is a convenient way to save the connection, confirm the login and database with the smoke-test queries above, and start exploring the schema.

FAQ

What is the default SQL Server port?

The default instance listens on TCP 1433. Named instances usually use a dynamic port that clients discover through SQL Server Browser on UDP 1434, unless you assign a static port.

Why does my connection string work with ODBC Driver 17 but not 18?

ODBC Driver 18 encrypts by default and validates the server certificate. If the server uses a self-signed certificate, install a trusted certificate or, for development only, add TrustServerCertificate=yes.

Is TrustServerCertificate=True safe?

Traffic is still encrypted, but the client accepts any certificate, so an attacker in the network path could impersonate the server. Use it only for local development and test environments.

How do I connect to a named instance with JDBC?

Use jdbc:sqlserver://host;instanceName=NAME;databaseName=db, or specify the port directly with jdbc:sqlserver://host:port, which skips the Browser lookup.

Can I use Windows authentication with Azure SQL Database?

Not classic on-premises Windows authentication. Use Microsoft Entra ID authentication (interactive, managed identity, service principal, or the default credential chain) or a SQL login.