Skip to main content

Connect a Microsoft SQL Server datasource

Goal​

You'll add a Microsoft SQL Server (or Azure SQL Database) instance to DQ Cloud, confirm the connection, and discover 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 DQ 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​

DQ Cloud's worker runs Great Expectations 1.x checkpoints against the customer 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 discover 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. Navigate to Datasources → New.

  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". DQ 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.

  7. Click "Discover assets". DQ 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 selected.

Verify​

  • The datasource shows up under Datasources with a green health badge.
  • Discovered assets appear under the datasource's detail page.
  • You can now author a rule suite against one of those assets — see Write a rule suite without code.

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.
  • Discover results are cached in Redis for 15 minutes. If you create a new table and don't see it, click Refresh in the modal (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.