Skip to content
BranchesTags

Repository files navigation

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_DEFINITION_CHUNK_SIZE=8000
  • MCP_SQL_MAX_CELL_CHARS=24000
  • MCP_SQL_PROC_EXECUTION_ENABLED=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
  • get_definition_chunk
  • get_proc_definition_compact
  • execute_readonly_proc
  • trace_param_usage
  • safe_sample_by_id
  • why_row_not_returned
  • compare_repo_sql_to_db

All tool responses are minified JSON strings.

The database argument is optional for every tool. When omitted, the server uses the Database / Initial Catalog from MSSQL_CONNECTION_STRING; pass database only to override that default. Object inputs may use either schema.object or an unqualified name such as usp_SalesProductReport; unqualified names are resolved across schemas and ambiguous matches return candidate full names.

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.

{"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

Use this first when inspecting an unfamiliar table. Omit columns during discovery so the response includes exploration-grade column metadata plus sample rows:

{"table":"ProductPacking","limit":5}

The response includes columns with name, type, nullable, key_role, and FK references when present; top-level foreign_keys; table_row_count_estimate; sample rows; and row_count for the returned sample size. Use returned FK references to inspect related lookup/config tables with another preview call.

Prefer structured filters when narrowing rows:

{"table":"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, and enforces the configured row cap. 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.

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

{"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:

{"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:

{"from":"ProductPacking","to":"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.

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

get_definition_chunk

Returns a paged chunk of a large view/procedure/function definition without forcing the full body into one tool response.

{"name":"usp_Report","type":"procedure","offset":0,"length":8000}

Use this instead of describe_object include_definition=true for large routines.

get_proc_definition_compact

Returns compact stored procedure analysis: parameters, referenced tables, @ParamList key reads, assignments, JOIN/WHERE predicates, and dynamic SQL fragments.

{"name":"usp_Report","verbosity":"brief"}

Dynamic SQL reconstruction is best-effort and includes confidence/warnings when partial.

execute_readonly_proc

Executes a stored procedure through mssql.Request.execute with scalar params and TVPs. This tool is disabled unless MCP_SQL_PROC_EXECUTION_ENABLED=true.

{
  "procedure": "salesdoc.usp_Report",
  "tvps": {
    "ParamList": {
      "typeName": "dbo.ParamList",
      "rows": [{ "keyName": "SalesDocCategoryTypeCode", "keyValue": "4,3" }]
    }
  },
  "max_rows": 50,
  "verbosity": "brief"
}

This does not loosen run_readonly_query; ad-hoc EXEC, DECLARE, writes, comments, and multi-statement SQL remain blocked there.

why_row_not_returned

Best-effort RCA helper for “why did this proc not return this entity?” It checks row existence, extracts relevant routine predicates, evaluates simple row predicates, and returns a concise final reason with confidence.

{
  "procedure": "salesdoc.usp_GeneralDocsSelectableForFieldService",
  "requestParamList": [
    { "keyName": "SalesDocCategoryTypeCode", "keyValue": "4,3" }
  ],
  "target": {
    "table": "salesdoc.SalesInvoiceHeader",
    "idColumn": "Id",
    "id": 1385708
  }
}

compare_repo_sql_to_db

Compares a local SQL file inside the MCP workspace with the live SQL Server definition and reports behavioral-looking differences around predicates, joins, parameters, and execution fragments.

{"object_name":"usp_Report","object_type":"procedure","local_file_path":"DBScripts/salesdoc/usp_Report.sql"}

profile_column

Profiles one validated column with bounded top values:

{"table":"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:

{"table":"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.

{"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 stored procedure execution disabled unless explicitly enabled
  • Runs stored procedures only through the dedicated procedure tool, not ad-hoc SQL text
  • 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)

Future Improvements

  • Error explanation would be useful, but should be based on a curated error catalog, searchable validation-message tables, or a generated offline index. Avoid broad live/repo scans for error text because they are slow and low-confidence.

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_DEFINITION_CHUNK_SIZE=8000 default definition page size
  • MCP_SQL_MAX_CELL_CHARS=24000 maximum single large text/cell budget
  • MCP_SQL_PROC_EXECUTION_ENABLED=false gates execute_readonly_proc
  • 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 names; if an unqualified name is ambiguous, pass the returned schema.object candidate. Pass database only when overriding the connection-string database.

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

1 star

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages