Microsoft SQL Server connection

Connect a task to an external Microsoft SQL Server (Azure SQL, on-prem instance, or a Docker mcr.microsoft.com/mssql/server container) over a managed Leoflow Connection.

URI shape

mssql://<login>:<password>@<host>:<port>/<database>

The control plane builds this URI from the Connection’s fields. Reserved characters in the password are percent-escaped (the @ in a strong SQL Server password becomes %40); the user-side Python must un-escape on the way out. See the gotcha below.

Fields the UI asks for

The pymssql gotcha

pymssql.connect() accepts kwargs (server=, port=, user=, password=, database=), not a URI string. The user task must parse the URI itself:

import os
from urllib.parse import unquote, urlparse
import pymssql

url = urlparse(os.environ["AIRFLOW_CONN_MSSQL_TARGET"])
conn = pymssql.connect(
    server=url.hostname,
    port=url.port or 1433,
    user=url.username or "",
    password=unquote(url.password or ""),
    database=(url.path or "").lstrip("/"),
)

The unquote is the critical step: the URI builder percent-escapes reserved characters (@ → %40); without unquote, pymssql would see Etl%401234 instead of Etl@1234 and authentication would fail.

Alternative drivers

• pyodbc: the most common production driver. Build a connection string from the parsed URL:

  cnxn_str = (
      f"DRIVER={{ODBC Driver 18 for SQL Server}};"
      f"SERVER={url.hostname},{url.port or 1433};"
      f"DATABASE={(url.path or '').lstrip('/')};"
      f"UID={url.username};PWD={unquote(url.password)};"
      "Encrypt=yes;TrustServerCertificate=no"
  )
  cnxn = pyodbc.connect(cnxn_str)
  

  Requires the ODBC driver installed in the image (apt install msodbcsql18).
• SQLAlchemy accepts the URL with the right dialect: create_engine("mssql+pyodbc://...") for pyodbc, or create_engine("mssql+pymssql://...") for pymssql. SQLAlchemy handles the un-escape automatically.

Example DAG

examples/mssql_load reads AIRFLOW_CONN_MSSQL_TARGET, opens a pymssql connection, and writes 20 rows. The example’s README walks through Docker spin-up, Connection setup (with a password containing @), and verification.

Lite vs Pro caveats

• Lite (subprocess) runs the task on the host. The Connection’s host resolves against the host’s name lookup.
• Lite (k3d) runs the task in a pod. Use host.k3d.internal to reach a host-bound port.
• Pro (Kubernetes) runs the task in a pod. The target SQL Server must be reachable via cluster DNS, an Azure-managed PrivateLink, or external DNS the pod’s network policy permits.
• Azure SQL specifics: the host is <server>.database.windows.net, port is always 1433, and Extra should set {"encrypt":"true","TrustServerCertificate":"false"} to force TLS with certificate verification. Azure AD logins look like user@tenant; that single @ is enough to exercise the percent-escape path.

Security notes

• TLS in transit: SQL Server 2017+ requires TLS by default. Set Encrypt=yes;TrustServerCertificate=no in pyodbc / pymssql kwargs.
• MSSQL_SA_PASSWORD strength: SQL Server’s strong-password policy requires 8+ characters, at least 3 of: uppercase, lowercase, digits, special. Trivially-weak passwords (password, etl) are refused at container start.
• SSPI / Kerberos: not supported by pymssql cleanly; use pyodbc with the appropriate driver if you need it.

Troubleshooting

Related

• ADR 0019 — secret encryption at rest.
• ADR 0021 — agent secret delivery.
• #71 — MSSQL connector umbrella.
• #138 — chain-of-custody contract test (covers mssql).