mssql-explorer-mcp

  

A read-only Model Context Protocol (MCP) server for exploring an on-premises, multi-instance Microsoft SQL Server estate from an AI client such as Claude Code. It is built for the environment most SQL Server actually runs in: on-prem, inside an Active Directory domain, reached with Windows authentication, often with one DBA, no platform team, and no read replica.

It is strictly read-only by construction. It surfaces structure, lineage, object definitions, profiling, estimated plans, DMV-based tuning signals, SQL Agent and SSIS metadata, and a guarded ad-hoc query runner. When a change is needed it authors the SQL for a human to run; it never executes a write.

Why this exists

Most writing about giving an AI agent database access assumes the cloud: managed identities, IAM tokens, a read replica, and a team to wire it up. On-prem Microsoft estates do not have those. They have Windows authentication, domain accounts, and the production server itself. This server is built for that reality, and it makes two things first-class that cloud-shaped tools skip:

• Read-only by construction, not by trust. The model is never relied on to "only read." Three independent walls enforce it (below).
• Identity decoupling. The account the agent runs as and the account the database connection authenticates as are separated, so one agent install can reach the estate as a different (recommended: a dedicated read-only) domain identity without running the agent itself as that account.

Tools

• Discovery: list_instances, list_databases, set_default_target
• Ad-hoc: run_query (gated, bounded), explain_query (estimated plan, no execution)
• Structure: list_tables, describe_table, get_definition, trace_lineage
• Profiling: profile_table, profile_column
• Tuning and ops: find_slow_queries, index_health, list_jobs, list_ssis_packages, list_ssis_executions

Every call passes the read-only gate and the deny-by-default access resolver, and is written to an append-only JSONL audit log (query and metadata only, never result rows).

Read-only by construction: the three walls

1. The read-only parser gate. Every statement is parsed (with a pinned sqlglot) and rejected unless it is a read. Writes, DDL, and EXEC never reach the server. The parser version is pinned because the gate's guarantees depend on its parse trees, so an upgrade is a security event (see tests/test_gate.py).
2. The deny-by-default registry. An instance, database, or schema is reachable only if it is explicitly allowed in instances.yaml, with object-level deny carve-outs and system databases hidden. Nothing is exposed unless it is blessed.
3. The append-only audit log. Every call records the query and metadata (never result rows) under the session identity, partitioned by day.

Identity: connect as a different account than the agent

Windows authentication binds to the process token, so a connection authenticates as whatever account owns the process. That has two consequences on-prem that the cloud playbook never addresses: an agent installed under one account spawns a server that inherits that account's identity, and you do not want the agent itself running as a privileged account, because any prompt injection through it would inherit that power.

This server can run as a loopback HTTP daemon under a chosen domain identity, with the AI client (running as an ordinary, unprivileged account) connecting to it over 127.0.0.1 with a bearer token. The daemon holds the connecting identity, the agent stays unprivileged, and the agent's identity is irrelevant to SQL. See docs/adr/0005-loopback-daemon-bridge.md for the rationale and docs/broker-setup.md for step-by-step setup.

Recommended deployment: point the daemon at a dedicated read-only account (a read-only SQL Login, or a least-privilege read-only AD account). Then the database itself enforces read-only and the parser gate is defense in depth rather than the only wall. Running the daemon as a write-capable privileged account is supported as a transitional bridge, but then the parser gate is the only thing between the agent and a write, so do it only with eyes open. See SECURITY.md.

Authentication modes

The server connects as exactly one identity, fixed at launch. Three modes are supported:

| Mode | How | Platform | Needs the daemon? | |---|---|---|---| | SQL Login | username and password defined in the instance; password from the OS secret store | Linux, macOS, Windows | No | | Microsoft Entra ID (Azure AD) | an ODBC Authentication= flow: service principal, managed identity, password, default, or integrated | Linux, macOS, Windows | No | | Windows Integrated Authentication | the process's own Windows identity, no password | Windows only | Yes, when the connection account differs from the client account (the daemon, above) |

For Entra, set MSSQL_EXPLORER_ENTRA_AUTH to the method (for example ActiveDirectoryServicePrincipal or ActiveDirectoryManagedIdentity) and, where the method needs a principal, MSSQL_EXPLORER_ENTRA_UID (the UPN, application id, or user-assigned client id). Service-principal and password methods read their secret from the OS secret store; managed identity and the default credential chain need none. Tag Entra-reachable instances realm: entra in the registry. See docs/adr/0007-entra-authentication.md.

Install

This is a uv-managed project; plain pip works too.

# Core install and the portable unit suite (no ODBC driver needed):
uv sync
uv run pytest -m "not integration"

Connecting to a real SQL Server needs pyodbc, which compiles against a system ODBC stack (unixODBC plus Microsoft ODBC Driver 18 for SQL Server). It is kept in an optional extra so the core install stays portable:

# Requires unixODBC + ODBC Driver 18 already installed.
uv sync --extra mssql

Configure

Copy instances.example.yaml to instances.yaml (gitignored) and describe only what should be reachable. The registry is deny-by-default: anything not listed is refused.

instances:
  - label: PRD-FIN                      # human name shown in tools
    host: sql-finance.corp.example      # DNS alias (connection target)
    realm: integrated                   # "integrated" (Windows auth), "entra", or a SQL Login name
    databases:
      - name: Finance
        allow_schemas: [reporting, ref] # allow at schema granularity
        deny_objects: [reporting.vEmployeeSSN]  # carve-outs within an allowed schema
      # databases not listed are denied

Point your MCP client at the server with mcp.example.json (stdio) or mcp.http.example.json (the loopback daemon). For day-to-day usage and how to read each kind of error, see docs/usage.md.

Example session

A typical exchange in an MCP client, once a target is configured:

You:   Set the default target to instance PRD-FIN, database Finance.
       -> set_default_target(instance="PRD-FIN", database="Finance")

You:   Which indexes on the largest tables are going unused?
       -> index_health(...) reads sys.indexes and sys.dm_db_index_usage_stats,
          returns a ranked list of low-use indexes

You:   Show me the definition of the vSalesSummary view.
       -> get_definition(...) returns it, because its schema is allowed

You:   Update Customers to set status = 'X'.
       -> rejected by the read-only gate before anything reaches the server

The value (fast, junior-friendly estate exploration) and the guardrails (allowlist plus read-only gate) are both visible in that last pair: the read passes, the write never leaves the building.

Security

Read SECURITY.md before pointing this at anything real. In short: prefer a dedicated read-only account so the database enforces read-only; keep the registry tight; the loopback daemon must stay loopback-only and token-gated; and the parser version is pinned for a reason. Stand it up against one low-risk, non-production instance first, with the DBA in the loop, then widen.

Status

Built test-first. The unit suite runs on any platform with no database or ODBC driver; live behaviour is covered by an integration suite that needs a Dockerized SQL Server. The cross-platform core (SQL Login and Microsoft Entra ID auth) runs on Linux, macOS, and Windows; the Windows Integrated Authentication path and the loopback daemon are Windows-specific. This is exploration tooling, not a governed data API, and it is not a substitute for your own security review.

Development

uv sync
uv run pytest -m "not integration"   # portable unit suite

The Docker fixture and the command to run the live integration smoke test are documented in tests/integration/README.md.

License

Apache-2.0. See LICENSE.