SQL TShooter MCP

sql-tshooter is a local, read-only MCP server for SQL Server diagnostics. It is designed to run next to SQL Server and be launched by Codex as an stdio MCP server.

What It Does

The server exposes curated diagnostic tools instead of arbitrary SQL execution. The current tool set includes:

• get_server_info
• get_top_waits
• get_active_requests
• get_blocking_sessions
• get_blocking_details
• get_expensive_queries
• get_lock_summary
• get_database_sizes
• get_failed_jobs
• get_memory_status
• get_waiting_tasks
• get_disk_latency
• get_query_memory_grants
• get_tempdb_usage
• get_wait_stats_by_query
• get_plan_cache_summary
• get_table_scan_summary
• get_query_plan_summary

The project is read-only by design. It does not perform write operations, shell execution, or unrestricted SQL.

The newer performance-analysis tools stay summarized by default: bounded row counts, truncated query text, and a cached-plan summary instead of raw XML unless explicitly extended later.

Proof Of Concept

Example use case: we ran a simulated blocking incident where an open transaction in SSMS held locks on a row and the MCP tools were used to trace the issue.

Using the exposed telemetry, Codex correlated:

• active sessions
• blocking chains
• wait types
• locks
• active requests
• SQL text
• server health metrics

The result was a concise root-cause diagnosis: an uncommitted SSMS transaction was blocking application work.

Requirements

• Python 3.11+
• Microsoft ODBC Driver 18 for SQL Server
• Access to a SQL Server instance
• A read-only SQL login or Windows-authenticated principal with the required diagnostic permissions

SQL Server Permissions

For most tools, the practical permission model is:

1. Create a login.
2. Create a user in the target database.
3. Grant the baseline server diagnostic permission:
   • SQL Server 2019 and earlier: VIEW SERVER STATE
   • SQL Server 2022 and newer: VIEW SERVER PERFORMANCE STATE
4. Optionally grant SQLAgentReaderRole in msdb if you want get_failed_jobs to work.

Provisioning scripts are included here:

• create-readonly-login.sql
• create-readonly-windows-login.sql
• PERMISSIONS.md

Configuration

Set these environment variables before starting the server:

• SQL_TSHOOTER_HOST
• SQL_TSHOOTER_PORT default 1433
• SQL_TSHOOTER_DATABASE default master
• SQL_TSHOOTER_AUTH_MODE values sql or windows
• SQL_TSHOOTER_USERNAME required for sql auth
• SQL_TSHOOTER_PASSWORD required for sql auth
• SQL_TSHOOTER_DRIVER default ODBC Driver 18 for SQL Server
• SQL_TSHOOTER_ENCRYPT default true
• SQL_TSHOOTER_TRUST_SERVER_CERTIFICATE default false
• SQL_TSHOOTER_CONNECTION_TIMEOUT_SECONDS default 10
• SQL_TSHOOTER_QUERY_TIMEOUT_SECONDS default 30
• SQL_TSHOOTER_LOG_PATH optional log file override

See .env.example for a template.

Install

With uv:

uv sync --extra dev

With pip:

python -m pip install -e .[dev]

Bootstrap and Preflight

The supported Windows setup flow is:

powershell -ExecutionPolicy Bypass -File .\scripts\bootstrap-sql-tshooter.ps1

This script:

• installs dependencies
• requires a local .env
• runs startup preflight
• prints a Codex MCP config snippet

You can also run preflight directly:

python -m sql_tshooter.preflight

Startup preflight validates:

• environment configuration
• pyodbc availability
• configured ODBC driver presence
• SQL Server connectivity
• baseline server-state permissions

If a global prerequisite is missing, the server exits before exposing tools. Tool-specific limitations are reported as warnings and become sanitized runtime errors only for the affected tools.

Running the Server

In normal use, Codex launches the server. You do not typically start it manually first.

Codex should be configured to launch either:

• the console entrypoint sql-tshooter-mcp
• or a Python command that runs run_sql_tshooter_mcp.py

Manual local launch is still available:

sql-tshooter-mcp

or:

python -m sql_tshooter.server

Codex MCP Configuration

Codex should treat this as an stdio MCP server. In practice, that means Codex launches a local command and communicates with it over standard input and output.

A typical config.toml entry looks like this:

[mcp_servers.sql-tshooter]
command = 'C:\Path\To\python.exe'
args = ['C:\Path\To\sql-tshooter\scripts\run_sql_tshooter_mcp.py']

If sql-tshooter-mcp is on PATH, you can point Codex at that command instead.

You can also register the server from the CLI:

codex mcp add sql-tshooter -- python C:\Path\To\sql-tshooter\scripts\run_sql_tshooter_mcp.py

For a standard custom MCP server, no experimental MCP feature flags should be required.

Logs and Troubleshooting

By default, structured logs are written to logs\sql-tshooter.log. Set SQL_TSHOOTER_LOG_PATH to override the location.

If tools do not appear in Codex:

• confirm the mcp_servers.sql-tshooter entry exists or codex mcp list shows the server
• confirm the configured command launches successfully outside Codex
• confirm the environment variables are present
• check the log file for startup preflight failures

If SQL connectivity fails:

• verify SQL Server is reachable on the configured host and port
• verify the login or Windows principal works independently
• confirm the required baseline permission was granted

Development

Run tests with:

python -m pytest