--- name: connect-from-python description: "Use when connecting Python to Azure SQL Database: mssql-python or pyodbc, the ODBC driver, 'data source name not found', driver missing in a container image, Entra tokens. For SQLAlchemy, use `sqlalchemy-azure-sql`." --- # Connect from Python The Python answer changed. There are now two supported drivers, and the newer one removes the step that causes most Python connection failures: installing an ODBC driver into the operating system. This skill owns driver choice, installation, connection string shape, pooling and Entra ID for Python. Encryption doctrine, retry and the first-connect error on a paused database are the same in every language and live in `connect-to-azure-sql`. SQLAlchemy is `sqlalchemy-azure-sql`. ## Choose the driver first | | `mssql-python` 1.13.0 | `pyodbc` 5.3.0 | |---|---|---| | Who ships it | Microsoft, first-party | Community, long-standing | | Python required | 3.10 or later | 3.9 or later | | Operating system install | **None.** `mssql-python-odbc` carries the driver binaries | `msodbcsql18` and a driver manager, per distribution | | Entra `ActiveDirectoryDefault` | Yes, and `azure-identity` comes with it | No, see below | **Use `mssql-python` for new code.** Skipping the operating system install removes a class of container and CI failures. **Keep `pyodbc` when something above it already requires it**, most often an ORM dialect such as SQLAlchemy or Django. Use `sqlalchemy-azure-sql` for SQLAlchemy-specific configuration. Do not rewrite a data access layer just to change driver. ```bash pip install mssql-python # first-party path pip install pyodbc # incumbent path, plus the driver install below ``` On Python 3.9 the first line fails with `No matching distribution found`, which reads like a network problem and is not one. ## Installing the ODBC driver, for the pyodbc path only The package is **`msodbcsql18`**, the connection string names `ODBC Driver 18 for SQL Server`, and the two have to agree exactly. `pip install pyodbc` succeeds without it and the failure arrives at the first connect, which is why the first check below exists. Three things break an unattended build: the licence prompt, Alpine having no Microsoft package repository, and slim Debian omitting `libgssapi-krb5-2`. Open [references/odbc-driver-install-on-linux.md](references/odbc-driver-install-on-linux.md) before you write a Dockerfile or CI step that installs the driver. ## Connection strings Both take ODBC-style keywords. `mssql_python.connect()` takes the same string with `Driver` left out, because the driver is inside the package. `pyodbc` needs it named, and the name is the one the install registered: ```python import pyodbc conn = pyodbc.connect( "Driver={ODBC Driver 18 for SQL Server};" f"Server=tcp:{server},1433;" f"Database={database};" "Encrypt=yes;" ) ``` Read the server, database and any credential from the environment or a secret store, never a literal in source, and bind every value in a query rather than formatting it into the text. ## Pooling **`mssql-python` pools by default**, `max_size` 100 and `idle_timeout` 600 seconds. ```python import mssql_python # Call this ONCE, before the first connect. Afterwards it has no effect. mssql_python.pooling(max_size=25, idle_timeout=300) ``` A `pooling()` call after any connect is silently ignored, which looks exactly like a setting that did not take. The pool key is the connection string byte for byte, so one extra keyword or a different capitalisation on one call site produces a second independent pool: build it once as a constant. Under managed identity, device code or a `token_provider` the identity joins the key. There is no `ClearPool`, no pool statistics and no minimum size. **`pyodbc` does not pool in Python.** Pooling happens in the ODBC layer, `pyodbc.pooling` reads `True` out of the box, and it is switched off by assigning `False` **before any connection is made**. Its context manager is not `mssql-python`'s: `Connection.__exit__` commits, or rolls back on an exception, and **does not close**, so `with pyodbc.connect(...)` alone never returns the connection. Call `close()` in a `finally`. Never enable `pyodbc.pooling` and a pool above it at once. Pool sizing, and why the binding limit is workers rather than sessions, is `connect-to-azure-sql`. ## Microsoft Entra ID ### With mssql-python Set the `Authentication` keyword: `ActiveDirectoryDefault` locally, because it picks up a CLI sign-in, and `ActiveDirectoryMSI` for a managed identity in production. ```python conn = mssql_python.connect( f"Server=tcp:{server},1433;Database={database};" "Authentication=ActiveDirectoryMSI;" f"UID={client_id};" # only for a user-assigned managed identity "Encrypt=yes;" ) ``` `ActiveDirectoryDefault` walks a chain of providers on the first connection, failures first, so it costs seconds production has no reason to pay. Name the credential type directly there. Where the credential is an `azure-identity` object, `token_provider=` takes it whole and refreshes tokens for pooled connections. ### With pyodbc **The ODBC driver has no `ActiveDirectoryDefault`.** It is not a slower path, it is a rejected string, and the driver says so before it opens a socket: ```bash python3 -c " import pyodbc try: pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,1;' 'Authentication=ActiveDirectoryDefault;Encrypt=yes;', timeout=2) except pyodbc.Error as e: print(e.args[0], e.args[1]) " ``` It prints `08001`, `Invalid value specified for connection string attribute 'Authentication'`. Swap in `ActiveDirectoryMsi` and the same command reaches `HYT00`, login timeout, because that value is accepted. The keyword takes `SqlPassword`, `ActiveDirectoryIntegrated`, `ActiveDirectoryInteractive`, `ActiveDirectoryMsi`, `ActiveDirectoryServicePrincipal` and the deprecated `ActiveDirectoryPassword`, and nothing else. To get the same behaviour, acquire the token in Python and hand it to the driver: ```python import struct from azure.identity import DefaultAzureCredential import pyodbc # msodbcsql.h: SQL_COPT_SS_BASE_EX (1240) + 16 SQL_COPT_SS_ACCESS_TOKEN = 1256 token = DefaultAzureCredential().get_token("https://database.windows.net/.default").token packed = token.encode("utf-16-le") token_struct = struct.pack(f"' : file not found`. `IM002`, `Data source name not found and no default driver specified`, is a string that named no driver at all. Installing `msodbcsql18` answers the first and nothing about the second. **Two: encrypted, as the principal you meant, against the database you meant.** Save this as `check_connection.py`: ```python import os, sys, pyodbc CONN = ( "Driver={ODBC Driver 18 for SQL Server};" f"Server=tcp:{os.environ['SQL_SERVER']},1433;Database={os.environ['SQL_DATABASE']};" f"UID={os.environ['SQL_USER']};PWD={os.environ['SQL_PASSWORD']};" "Encrypt=yes;TrustServerCertificate=no;" ) conn = pyodbc.connect(CONN, timeout=15) try: db, login, user, enc = conn.cursor().execute( "SELECT DB_NAME(), SUSER_SNAME(), USER_NAME()," " (SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID)" ).fetchone() finally: conn.close() print(f"database={db} login={login} user={user} encrypted={enc}") sys.exit(0 if (enc == "TRUE" and db == os.environ["SQL_DATABASE"] and login == os.environ["SQL_EXPECT_LOGIN"]) else 1) ``` ```bash export SQL_SERVER=.database.windows.net SQL_DATABASE= \ SQL_USER= SQL_PASSWORD= SQL_EXPECT_LOGIN= python3 check_connection.py; echo "exit $?" ``` Expect exit `0` and a line ending `encrypted=TRUE`. Read the fields, not only the exit code: - `encrypted=FALSE` is driver 17, whose `Encrypt` default is `no`. Driver 18 defaults to `yes`. - `login` is who authenticated. A credential chain that picked up your own sign-in instead of the managed identity shows up here and nowhere else. - `user` is the database principal that login mapped to. Anything but the one you granted means the grant landed in a different database. - `TrustServerCertificate=yes` still prints `TRUE`: it keeps encryption and drops validation. Azure SQL Database needs no such exemption; the container's self-signed certificate does, and `connect-to-azure-sql` owns which. - A permissions error from `sys.dm_exec_connections` is `VIEW DATABASE STATE`, not encryption: on Basic, S0, S1 and elastic pool databases only an administrator can read it. ## Do not - Do not assume `pyodbc` is the only Python driver. That was true and is not. - Do not add an operating system ODBC driver install to a `mssql-python` deployment. - Do not put `Authentication=ActiveDirectoryDefault` in a `pyodbc` string, or an access token alongside `UID`, `PWD` or `Authentication`. - Do not use `ActiveDirectoryPassword`, or `ActiveDirectoryDefault` in production. - Do not build SQL text with string formatting. Bind parameters. - Do not write retry loops here. Transient-fault handling is one policy for every language, in `connect-to-azure-sql`. ## References - [Microsoft Python driver for SQL Server](https://learn.microsoft.com/sql/connect/python/mssql-python/python-sql-driver-mssql-python): read it before writing new Python data access. - [Microsoft Entra authentication with mssql-python](https://learn.microsoft.com/sql/connect/python/mssql-python/entra-authentication): read it when choosing a mode, for the authoritative list and `token_provider`. - [Using Microsoft Entra ID with the ODBC driver](https://learn.microsoft.com/sql/connect/odbc/using-azure-active-directory): read it before claiming the driver supports a mode. Carries the access token structure. - [DSN and connection string keywords](https://learn.microsoft.com/sql/connect/odbc/dsn-connection-string-attribute): read it before changing an encryption or certificate keyword. `Encrypt` defaults to `yes` from version 18. - `connect-to-azure-sql`: encryption doctrine, retry, pool sizing, and the first-connect error on a paused database.