Skip to main content

Connect a Microsoft SQL Server datasource

Goal​

You'll add a Microsoft SQL Server (or Azure SQL Database) instance to Measured Cloud, confirm the connection, and add its tables so you can author rules against them. This is the same UX as the other warehouse connectors (FEATURE-5 shipped the initial wiring; the auth-mode switch documented below landed as part of #84).

Other warehouses on the same code path: Postgres, Snowflake / BigQuery.

Prereqs​

  • A running Measured Cloud (see Self-host).
  • The worker (and the on-prem agent, if you use agent mode) image built from Dockerfile.worker / Dockerfile.agent on this branch. The Dockerfiles install the Microsoft ODBC Driver 18 (msodbcsql18), the unixodbc driver manager, and the pyodbc Python wheel as part of the build — no extra runtime setup needed.
  • A SQL Server / Azure SQL endpoint you can reach from the worker host.
  • One of:
    • SQL auth — a database user with SELECT on the schema you want to validate, plus its password.
    • Azure AD service principal — an Azure AD application registration with a client id, a client secret, and the tenant id, plus a Contained Database User mapped to that service principal with SELECT on the target schema. (Microsoft's CREATE USER [<sp-name>] FROM EXTERNAL PROVIDER step.)

If your SQL Server is on a private network (10.0.0.0/8, 192.168.0.0/16), opt-in by adding the CIDR to DATASOURCE_NETWORK_ALLOWLIST — see Configuration. The SSRF guard rejects private targets by default.

Driver choice — why mssql+pyodbc, not mssql+aioodbc​

Measured Cloud's worker runs your rules with Great Expectations 1.x against the database. GX 1.x's fluent add_sql_server datasource builds a synchronous SQLAlchemy Engine under the hood — there is no async equivalent in the GX surface today. Layering aioodbc on top of pyodbc doesn't change that: GX still drives the underlying connection synchronously, so the async wrapper is inert and only adds another wheel.

The API's connection-test and table-listing endpoints take a different code path — they use the ADBC MSSQL driver directly so a "ping" doesn't have to spin up a SQLAlchemy engine — but ADBC's MSSQL driver is also synchronous and runs behind asyncio.to_thread. Net: the supported stack is unixodbc + Microsoft ODBC Driver 18 + pyodbc (worker) plus the ADBC MSSQL wheel (API). The SQLAlchemy URL the platform builds is mssql+pyodbc://....

Azure AD interactive auth (the browser-popup OAuth flow) is out of scope in this revision — both the worker and the on-prem agent run unattended so there's nothing to drive the popup. The auth-mode dispatcher rejects auth_mode="azure_interactive" with a clear error so an operator who picks it from a future UI gets a useful message rather than a 30-second ODBC timeout. Use Azure AD service principal instead.

Steps​

  1. Log in to the UI as a workspace editor or higher.

  2. Go to Data Assets → Add source.

  3. Pick the type: Microsoft SQL Server.

  4. Fill in the form:

    • Name — e.g. prod-sales-sql.
    • Host — the SQL Server hostname (e.g. sql.example.database.windows.net for Azure SQL).
    • Port — usually 1433.
    • Database — the database name (Azure SQL: the contained DB name).
    • ODBC driver — defaults to ODBC Driver 18 for SQL Server; override only if your container ships a different version (e.g. ODBC Driver 17 for SQL Server on Ubuntu 20.04).
    • Auth mode — choose one:
      • SQL auth — username + password.
      • Azure AD service principal — client id, client secret, tenant id.

  5. Click "Test connection". Measured Cloud opens an ADBC MSSQL connection, runs SELECT @@VERSION, and reports either a green check with the round-trip latency or a red X with the error.

    • Common error: "Login failed for user" — credentials are wrong, or the AD service principal isn't mapped to a contained DB user with SELECT on the target schema.
    • Common error: "SSL Provider: certificate verify failed" — your SQL Server certificate isn't in the OS trust store. Either fix the certificate or, for dev only, set trust_server_certificate=yes in the persisted config (the form does not yet expose this; edit via the SDK or the agent registration payload).
    • Common error: "Datasource address rejected by network allowlist" — the target resolved to a private IP and isn't in DATASOURCE_NETWORK_ALLOWLIST. Add the CIDR and restart the API.

  6. Save the datasource. The window moves on to step 2, Add tables. You can also add tables later from the source's Manage source window (click the source's name on Data Assets).

  7. Add tables. Measured Cloud queries INFORMATION_SCHEMA.TABLES on the active database, filters out the sys and INFORMATION_SCHEMA system namespaces, and lists every table and view. Tick the ones you want and click Add N tables.

Verify​

  • The source shows up on Data Assets as a group header with a green connection badge.
  • The tables you added appear as data assets under that source, and read Added in its Manage source window.
  • You can now add rules to one of those assets — see Add rules to a data asset.

On-wire config shape (for SDK / agent registration)​

The UI hides the config shape; the SDK and agent-registration endpoints emit it directly. For reference:

SQL auth:

{
  "type": "mssql",
  "config_encrypted": {
    "auth_mode": "sql",
    "host": "db.example.com",
    "port": "1433",
    "database": "sales",
    "user": "appuser",
    "password": "...",
    "driver": "ODBC Driver 18 for SQL Server",
    "encrypt": "yes",
    "trust_server_certificate": "no"
  }
}

Azure AD service principal:

{
  "type": "mssql",
  "config_encrypted": {
    "auth_mode": "azure_sp",
    "host": "sql.example.database.windows.net",
    "port": "1433",
    "database": "sales",
    "client_id": "11111111-2222-3333-4444-555555555555",
    "client_secret": "...",
    "tenant_id": "tenant-guid",
    "driver": "ODBC Driver 18 for SQL Server",
    "encrypt": "yes"
  }
}

Legacy datasources that persist a pre-built connection_string (the shape the UI's buildMssqlDsn emits today for SQL auth) keep working unchanged — the worker hands the URL straight through.

Caveats​

  • The PARITY-1 test endpoint times out after 5 seconds by default. ODBC's LoginTimeout is honoured by the underlying driver; bump DATASOURCE_TEST_TIMEOUT_SECONDS if your warehouse takes longer to acknowledge a connection.
  • The table list is cached for 15 minutes. If you create a new table and don't see it, click Refresh tables in the toolbar (or wait).
  • Connection details (host, port, database, credentials) are AES-256-GCM-encrypted at rest. The plaintext exists only inside the worker process during a job.
  • Azure AD interactive auth and Windows Auth / Kerberos integrated auth are out of scope; use Azure AD service principal for the modern enterprise case.
  • Always Encrypted / TDE column protection is also out of scope — separate ticket if anyone asks.