MCP SQL Server Schema Server

Production-ready MCP server for SQL Server schema discovery and data preview.

Requirements

• Node.js 18+
• SQL Server 2017+ (uses FOR JSON PATH, STRING_AGG)
• Read-only or least-privilege SQL login strongly recommended

Installation

npm install
npm run build

Configuration

Use a least-privilege read-only SQL login and configure the server with:

• MSSQL_CONNECTION_STRING
• MCP_SQL_READONLY_QUERY_ENABLED=false
• MCP_SQL_MAX_ROWS=100
• MCP_SQL_DEFAULT_LIMIT=25
• MCP_SQL_QUERY_TIMEOUT_MS=3000
• MCP_SQL_DESCRIBE_TIMEOUT_MS=5000
• MCP_SQL_INCLUDE_DEFINITIONS_DEFAULT=false
• MCP_SQL_AUDIT_LOG_ENABLED=true
• MCP_SQL_EXPLAIN_ENABLED=false
• MCP_SQL_RESOURCE_CACHE_TTL_SECONDS=300

Supported connection string examples:

SQL Server Authentication

Server=sql-prod-01;Database=MyDatabase;User Id=readonly_user;Password=StrongPass!123;Encrypt=true;TrustServerCertificate=true

Azure SQL Database

Server=tcp:myserver.database.windows.net,1433;Database=MyDatabase;User Id=readonly_user;Password=StrongPass!123;Encrypt=true;TrustServerCertificate=false

Named Instance

Server=MSSQL15.MSSQLSERVER\MSSQL;Database=MyDatabase;User Id=readonly_user;Password=StrongPass!123;Encrypt=true;TrustServerCertificate=true

Custom Port

Server=10.20.30.40,51433;Database=MyDatabase;User Id=readonly_user;Password=StrongPass!123;Encrypt=true;TrustServerCertificate=true

Copy and edit .env.example:

cp .env.example .env

Run

node dist/index.js

MCP Tools

• search_catalog
• search_objects
• describe_object
• get_data_preview
• run_readonly_query
• validate_sql
• search_columns
• find_relationships
• profile_column
• get_table_profile
• explain_query_summary
• list_schemas
• list_tables

All tool responses are minified JSON strings.

Recommended for Codex: Start with search_objects using detail_level: "names". Use search_columns when the user asks for business fields. Only request summary or full details after narrowing candidates. Use find_relationships before writing joins. Use get_table_profile and profile_column for compact data understanding. Use validate_sql before run_readonly_query. Never rely on inferred joins without showing confidence.

search_objects

Unified discovery across schema names, table/view/routine names, column names, index names, foreign key names, and descriptions.

{"database":"MyDatabase","query":"Order","object_types":["table","column"],"detail_level":"names","limit":20}

Responses include results, truncated, next_cursor, and warnings. summary adds cheap counts and descriptions when available. Routine and view definitions are not returned by this search tool.

get_data_preview

Prefer structured filters:

{"database":"MyDatabase","table":"myschema.ProductPacking","columns":["Id","Status"],"filters":[{"column":"Id","op":">","value":1000}],"order_by":[{"column":"Id","direction":"asc"}],"limit":5}

The tool validates the table and all selected/filter/order columns against metadata, parameterizes values, enforces the configured row cap, and returns column metadata plus rows. where_clause is deprecated and only accepts one simple legacy predicate for backward compatibility.

run_readonly_query

Disabled by default. Enable with MCP_SQL_READONLY_QUERY_ENABLED=true only when the SQL login is read-only.

{"database":"MyDatabase","sql":"SELECT TOP 5 Id FROM myschema.ProductPacking","max_rows":5}

The tool only accepts bounded SELECT/CTE statements, rejects write/admin tokens, comments, temp-table writes, linked-server four-part names, SELECT INTO, and multiple statements. Query attempts are audit logged without secrets or connection strings.

validate_sql

Validates read-only SQL without executing it. It reuses the read-only safety scanner and asks SQL Server for result-set metadata with sys.dm_exec_describe_first_result_set.

{"database":"MyDatabase","sql":"SELECT TOP 5 Id FROM myschema.ProductPacking"}

Unbounded but otherwise safe queries are reported as valid with a warning so Codex can fix the row limit before execution.

search_columns

Finds tables through column names or column descriptions:

{"database":"MyDatabase","query":"CustomerId","schemas":["myschema"],"limit":10}

Results include schema, table, column, data type, nullability, PK/FK role, referenced column when available, and match reason.

find_relationships

Finds bounded join paths using foreign keys first and optional inference second:

{"database":"MyDatabase","from":"myschema.ProductPacking","to":"myschema.Product","max_depth":2,"include_inferred":false}

Foreign-key joins are high confidence. Inferred joins are never high confidence and include a warning.

describe_object

describe_object now defaults to detail_level: "summary" and excludes routine definitions unless include_definition: true.

{"database":"MyDatabase","name":"myschema.ProductPacking","type":"table","detail_level":"summary","include":["columns","keys"],"max_items":50}

profile_column

Profiles one validated column with bounded top values:

{"database":"MyDatabase","table":"myschema.ProductPacking","column":"Status","top_values":5}

Large text/blob/xml/spatial columns are skipped by default with a warning.

get_table_profile

Returns a compact table overview without the full column list:

{"database":"MyDatabase","table":"myschema.ProductPacking"}

Includes row/column counts, primary key, foreign keys, index summary, likely enum/date/audit columns, and warnings.

explain_query_summary

Disabled by default. Enable with MCP_SQL_EXPLAIN_ENABLED=true.

{"database":"MyDatabase","sql":"SELECT TOP 5 Id FROM myschema.ProductPacking"}

Returns a compact estimated-plan summary only. Full XML plans are never returned by default.

MCP Resources and Prompts

Resources expose cacheable summary metadata only:

• db://{database}/schemas
• db://{database}/schema/{schema}
• db://{database}/table/{schema}.{table}
• db://{database}/view/{schema}.{view}
• db://{database}/routine/{schema}.{name}

Prompts:

• sql-investigate-object
• sql-generate-safe-query
• sql-debug-query-error
• sql-performance-review
• sql-impact-analysis

Recommended workflow:

1. search_objects
2. search_columns
3. describe_object
4. find_relationships
5. get_table_profile / profile_column
6. validate_sql
7. run_readonly_query
8. explain_query_summary

Codex MCP Configuration

[mcp_servers.sql_schema]
command = "node"
args = ["/absolute/path/to/mcp-sqlserver-schema/dist/index.js"]

[mcp_servers.sql_schema.env]
MSSQL_CONNECTION_STRING = "Server=myserver;Database=MyDB;User Id=reader;Password=pass;Encrypt=true"
NODE_ENV = "production"

Security Notes

• Blocks high-risk SQL patterns (DROP, ALTER, EXEC, comments, etc.)
• Blocks multi-statement user clauses (; count > 1)
• Sanitizes and bracket-quotes identifiers
• Validates preview table/column/filter/order identifiers against SQL Server metadata
• Parameterizes structured preview filter values
• Keeps run_readonly_query disabled unless explicitly enabled
• Requires bounded read-only queries with TOP or OFFSET/FETCH
• Keeps explain_query_summary disabled unless explicitly enabled
• Exposes only metadata summaries as cacheable MCP resources, never data previews/query results
• Returns sanitized errors only (no stack traces, query text, credentials)

Caching and Performance

• In-memory cache (node-cache) with TTL 600s, check period 120s
• Cache key pattern: {db}:{schema}:{object}:{operation}
• Slow query logging for queries >1000ms

Logging

Logs are JSON lines. By default they are written to:

• stderr
• ./mcp-runtime.log (project root)

You can control this with env vars:

• LOG_TO_FILE=true|false
• LOG_TO_STDERR=true|false
• LOG_FILE=./mcp-runtime.log (or absolute path)
• MCP_SQL_QUERY_TIMEOUT_MS=3000 default timeout for metadata/search/read-only queries
• MCP_SQL_DESCRIBE_TIMEOUT_MS=5000 timeout for describe_object
• MCP_SQL_MAX_ROWS=100 hard row/item cap for tools
• MCP_SQL_DEFAULT_LIMIT=25 default limit for paginated discovery
• MCP_SQL_AUDIT_LOG_ENABLED=true audit tool attempts
• MCP_SQL_EXPLAIN_ENABLED=false enables estimated-plan summaries when set true
• MCP_SQL_RESOURCE_CACHE_TTL_SECONDS=300 advertised resource cache TTL

Realtime log tail (PowerShell):

Get-Content .\mcp-runtime.log -Wait

• ERR_CONNECTION_FAILED: verify host, credentials, firewall, SQL auth mode.
• ERR_FORBIDDEN_KEYWORD: remove blocked SQL keywords/comments from legacy where_clause.
• ERR_TIMEOUT: reduce preview scope (limit, where_clause) or verify server load/indexing.
• No results: verify object/schema names and database context.