Connection strings for mssql-python

The mssql-python driver supports the following connection string keywords when connecting to SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric.

Connection string syntax

Connection strings use semicolon-separated key-value pairs:

keyword1=value1;keyword2=value2;...

Wrap values that contain special characters (semicolons, equals signs, or curly braces) in curly braces:

PWD={my;complex=password}

To include a literal closing brace in a value, use two closing braces (}}):

PWD={password}}with}}brace}

Basic connection examples

The following examples show how to connect using different authentication methods. For production applications, use Microsoft Entra authentication whenever possible. It eliminates passwords from your code and connection strings.

SQL Server with Microsoft Entra authentication (recommended)

This example uses ActiveDirectoryDefault, which tries multiple credential sources (Azure CLI, environment variables, managed identity) in order. No password is stored in code:

import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;Authentication=ActiveDirectoryDefault;Encrypt=yes;"
)

SQL Server with SQL authentication

Use SQL authentication only for local development against a SQL Server instance that you control. Credentials are embedded in the connection string, so keep them in environment variables or a .env file rather than in source code:

conn = mssql_python.connect(
    "Server=<server>;"
    "Database=<database>;"
    "UID=<login>;"
    "PWD=<password>;"
    "Encrypt=yes;"
)

Azure SQL with Microsoft Entra authentication

The connection string for Azure SQL Database is the same as SQL Server. ActiveDirectoryDefault works across local development, containers, and Azure-hosted environments without code changes:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes;"
)

Use keyword arguments

You can pass connection parameters as keyword arguments instead of or in addition to a connection string. Keyword arguments avoid the escaping pitfalls of connection string assembly. Passwords with special characters like @, ;, {, or } don't need curly-brace wrapping when passed as keyword arguments:

conn = mssql_python.connect(
    server="<server>.database.windows.net",
    database="<database>",
    authentication="ActiveDirectoryDefault",
    encrypt="yes"
)

Compare with connection string assembly, where a password containing @ must be wrapped:

# Connection string requires escaping
conn = mssql_python.connect("Server=srv;UID=user;PWD={p@ss;word};")

# Keyword arguments - no escaping needed
conn = mssql_python.connect(server="srv", uid="user", pwd="p@ss;word")

The driver merges keyword arguments into the connection string after normalization. If a keyword argument matches a parameter already in the connection string, the keyword argument takes precedence and overrides the connection string value:

# The keyword argument database="production" overrides Database=dev in the connection string
conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;Encrypt=yes;",
    database="production",
    authentication="ActiveDirectoryDefault"
)
# Connects to "production", not "dev"

The following example combines a connection string with keyword arguments:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;",
    authentication="ActiveDirectoryDefault",
    encrypt="yes"
)

Connection string keywords

Server and database

Specify the target SQL Server instance and database for the connection.

Authentication

Provide credentials for SQL authentication or specify a Microsoft Entra authentication mode. For passwordless options, see Microsoft Entra authentication modes.

Encryption and security

All connections use Encrypt=yes by default. For most applications, the default is sufficient. Use strict only when your SQL Server instance supports TDS 8.0 and you require TLS 1.3. Use TrustServerCertificate=yes only in development environments with self-signed certificates.

High availability and failover

These keywords cover Always On availability groups, Azure SQL targets, and idle connection resiliency. Set ApplicationIntent=ReadOnly to route read-heavy workloads (reports, analytics) to secondary replicas, reducing load on the primary. Set MultiSubnetFailover=yes when the target is Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, an availability group listener, or a failover cluster instance. When the server name resolves to more than one IP address, the driver connects to all of those addresses at the same time and uses the first one that answers. Without it, the driver tries the addresses one at a time. An address that doesn't answer stalls until the operating system's TCP connect timeout expires, which can exhaust the login timeout before the driver reaches an address that answers. When DNS resolves to a single address, the driver makes a single connection attempt, so the setting is safe to leave on.

MultiSubnetFailover=yes has the following limits. You can't use it over a protocol other than TCP, connecting to a SQL Server instance configured with more than 64 IP addresses fails, and you can't use it with database mirroring. Database mirroring is deprecated in all supported versions of SQL Server. Use Always On availability groups instead.

Performance and network

The defaults work for most applications. Increase PacketSize (up to 32767) for bulk data transfers. Configure KeepAlive if connections cross firewalls or load balancers that drop idle TCP sessions.

Reserved keywords

Microsoft Entra authentication modes

The Authentication keyword supports the following values. Choose the mode that matches your deployment:

For reproducible Docker, devcontainer, and CI environment setup, see Container and local development. That article centralizes Python runtime selection and shows how to use digest-pinned images in shared environments.

Example: DefaultAzureCredential

ActiveDirectoryDefault maps to the Azure Identity DefaultAzureCredential chain. It tries the Azure CLI token first during local development, then managed identity when deployed to Azure:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes;"
)

Example: Device code flow

Use device code flow when running in environments without a browser, such as SSH sessions or Docker containers. The driver displays a URL and a code to enter on a separate device:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDeviceCode;"
    "Encrypt=yes;"
)
# Follow the prompt to authenticate at https://microsoft.com/devicelogin

Example: Service principal

Service principal authentication uses a registered application identity with a client ID and secret. Use this approach for CI/CD pipelines and background services that run without user interaction:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryServicePrincipal;"
    "UID=<client-id>;"
    "PWD=<client-secret>;"
    "Encrypt=yes;"
)

To register the application and grant it database access, see Microsoft Entra service principals with Azure SQL. For the full setup in mssql-python, see Service principal authentication.

Connection timeout

Set the authentication timeout with the timeout parameter. Use a timeout to prevent your application from hanging indefinitely when the server is unreachable:

# 30-second authentication timeout
conn = mssql_python.connect(connection_string, timeout=30)

Connection.timeout is a separate setting that bounds each statement rather than the authentication attempt. For more information, see Connection timeout.

conn.timeout = 60

If the target is Azure SQL Database serverless with auto-pause enabled, use at least 60. An auto-paused database resumes on the first connection attempt, and a shorter timeout expires before the resume completes. The attempt can also fail with error 40613 while the database resumes, so the application must retry. For more information, see Auto-pause and auto-resume.

Autocommit mode

By default, autocommit is False, which requires explicit commit() calls. Enable autocommit for DDL statements or read-only queries that don't need transaction control:

# Via parameter
conn = mssql_python.connect(connection_string, autocommit=True)

# Or after connection
conn.setautocommit(True)

Credential objects

Instead of naming an authentication mode in the connection string, you can hand the driver a credential object with the token_provider parameter. This parameter accepts any object with a get_token(scope) method, including every credential in the azure-identity package:

import mssql_python
from azure.identity import DefaultAzureCredential

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Encrypt=yes",
    token_provider=DefaultAzureCredential(),
)

Don't combine token_provider with the Authentication keyword in the same connection. The driver raises InterfaceError when both are present. For more information, see Microsoft Entra authentication.

Connection attributes

Set ODBC connection attributes before the connection is established by using attrs_before:

import mssql_python

conn = mssql_python.connect(
    connection_string,
    attrs_before={
        mssql_python.SQL_ATTR_LOGIN_TIMEOUT: 30,
        mssql_python.SQL_ATTR_CONNECTION_TIMEOUT: 60,
    }
)

Programmatic connection string building

To prevent connection string injection, don't use string concatenation or f-strings with user input. Use keyword arguments or environment variables instead. For more construction patterns including JSON/YAML config files, Azure Key Vault, and a builder class, see Build connection strings programmatically.

import os

conn = mssql_python.connect(
    server=os.environ["DB_SERVER"],
    database=os.environ["DB_NAME"],
    authentication=os.environ.get("DB_AUTH", "ActiveDirectoryDefault"),
    encrypt="yes"
)

Connection string validation

The driver validates connection strings and raises ConnectionStringParseError for unknown or misspelled keywords:

try:
    conn = mssql_python.connect("Servr=localhost;")  # Typo
except mssql_python.ConnectionStringParseError as e:
    print(f"Invalid connection string: {e}")
    # Output: Unknown keyword 'Servr'

Keywords from other drivers

Validation runs before the driver opens a connection, so a keyword that other SQL Server drivers accept fails immediately here. Connection strings ported from ADO.NET, ODBC, or pyodbc usually need these substitutions:

For APP and Driver, the driver reports a reserved keyword error rather than an unknown keyword error, because it controls both values.

Related content

• Build connection strings programmatically
• Install mssql-python
• Microsoft Entra authentication with mssql-python
• Connection pooling with mssql-python
• Manage connections with mssql-python