MS SQL Server MCP Server

A secure, read-only Model Context Protocol (MCP) server for Microsoft SQL Server with built-in performance monitoring and lock detection.

📦 npm: @piyapat/mssql-mcp-server

Quick Start with npx

You can run this MCP server directly without installation using npx:

npx @piyapat/mssql-mcp-server

Installation

Option 1: Use with npx (Recommended for Testing)

# Run directly with environment variables
MSSQL_SERVER=localhost \
MSSQL_DATABASE=mydb \
MSSQL_USER=readonly \
MSSQL_PASSWORD=password \
npx @piyapat/mssql-mcp-server

Option 2: Global Installation

# Install globally
npm install -g @piyapat/mssql-mcp-server

# Run
mssql-mcp-server

Option 3: Local Installation

# Clone and install
git clone https://github.com/PiyapatRag/mssql-mcp-server.git
cd mssql-mcp-server
npm install
npm run build

# Run
npm start

Configuration

Step 1: Create a .env file (Recommended)

Credentials live in a .env file — not hard-coded in the MCP client's JSON config:

cp .env.example .env
# then edit .env with your credentials

The server looks for a .env file in this order (first found wins):

1. The path in MSSQL_ENV_FILE (explicit override)
2. .env in the current working directory
3. .env in the project root (next to package.json)

Variables already set in the MCP client's "env" block always take precedence over the .env file, and .env is git-ignored.

Step 2: Point Claude Desktop at the server

Edit your Claude Desktop config:

Windows: %APPDATA%\Claude\claude_desktop_config.json

macOS/Linux: ~/Library/Application Support/Claude/claude_desktop_config.json

With a .env in the project root, no credentials are needed in the JSON at all:

{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["C:\\path\\to\\mssql-mcp-server\\build\\index.js"]
    }
  }
}

If the .env lives somewhere else, pass only its path:

{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["C:\\path\\to\\mssql-mcp-server\\build\\index.js"],
      "env": {
        "MSSQL_ENV_FILE": "C:\\secure\\location\\mssql.env"
      }
    }
  }
}

Setting variables directly in the "env" block still works (and overrides the .env file) — useful for npx setups or running several servers against different databases.

Environment Variables

Variable	Description	Default	Required
MSSQL_SERVER	SQL Server hostname or IP	localhost	Yes
MSSQL_DATABASE	Database name	-	Yes
MSSQL_USER	SQL Server username (or domain user when MSSQL_DOMAIN is set)	-	Yes
MSSQL_PASSWORD	Password	-	Yes
MSSQL_DOMAIN	Windows/NTLM domain. When set, Windows Authentication is used instead of SQL authentication	-	No
MSSQL_PORT	SQL Server port	1433	No
MSSQL_ENCRYPT	Use encryption (true/false)	false	No
MSSQL_TRUST_CERT	Trust server certificate (true/false)	false	No
MSSQL_READ_ONLY	true = read-only allow-list validation. false = write mode: INSERT/UPDATE/DELETE/DDL allowed, but server-level dangerous statements stay blocked	true	No
MSSQL_ALLOWED_PROCEDURES	Comma-separated whitelist of stored procedures EXEC may call in read-only mode, e.g. dbo.GetReport,dbo.GetCustomerSummary. Empty/unset disables EXEC entirely.	-	No
MSSQL_ENV_FILE	Explicit path to a .env file	-	No
MSSQL_REQUEST_TIMEOUT	Query timeout in ms	30000	No
MSSQL_POOL_MAX	Max pooled connections	10	No

Server Modes

Read-only mode (MSSQL_READ_ONLY=true, default)

mssql_query accepts only:

• A single SELECT / WITH...SELECT
• A multi-statement batch led by DECLARE, INSERT, or CREATE TABLE #... that writes only to session-local #temp tables / @table variables — e.g. INSERT INTO #t SELECT ... or CREATE TABLE #t (...); INSERT INTO #t ...; SELECT * FROM #t. CREATE INDEX ... ON #t, TRUNCATE/ALTER/DROP TABLE #t are also allowed inside such batches. Global ##temp tables are never allowed (they are visible to every session, so they count as persistent).
• EXEC of a whitelisted procedure whose definition does not write to a persistent table

Everything else is rejected: writes/DDL on persistent objects, dynamic SQL, EXEC inside batches (prevents bypassing the procedure whitelist), DBCC, and stacked statements after a SELECT/WITH/EXEC.

Write mode (MSSQL_READ_ONLY=false)

INSERT / UPDATE / DELETE / DDL are permitted, but these are always blocked regardless of mode:

xp_cmdshell, xp_regwrite/xp_regdelete*, sp_configure, RECONFIGURE, SHUTDOWN, KILL, DROP DATABASE, ALTER DATABASE, RESTORE, CREATE/ALTER/DROP LOGIN/USER/CREDENTIAL/CERTIFICATE, ALTER SERVER, GRANT/DENY/REVOKE, OPENROWSET/OPENDATASOURCE.

⚠️ Use write mode only with a SQL login whose own permissions are equally limited — the database login remains the primary security boundary.

Key Features

Security First

• ✅ Two-layer read-only enforcement - A database read-only login (primary) plus an application-level allow-list (defense-in-depth). The app accepts SELECT / WITH...SELECT, DECLARE batches that write only to #temp tables / @table variables, and EXEC of whitelisted procedures whose definitions never write to a persistent table
• ✅ Parameterized queries - Built-in SQL injection protection
• ✅ SSL/TLS support - Encrypted connections for production
• ✅ Connection pooling - Optimized resource management

Performance Monitoring

• 📊 Real-time lock detection - Identify blocking and deadlock situations
• 📈 Resource usage tracking - CPU, memory, and query performance metrics
• 🔍 Top query analysis - Find resource-intensive queries
• ⚡ Session monitoring - Track active and blocked sessions

Database Exploration

• 🗂️ Schema introspection - Tables, columns, keys, and constraints
• 📝 Stored procedure analysis - View definitions and parameters
• 🔎 Intelligent querying - Natural language to SQL with Claude

Available Tools (19)

Core

Tool	Description
mssql_query	Execute SQL. Read-only validation by default; write mode via MSSQL_READ_ONLY=false (dangerous server-level statements always blocked). Pagination + JSON/Markdown output.
mssql_test_connection	Test connectivity; returns server/edition/version, database, login, and current mode.
mssql_list_databases	All databases with state, recovery model, compatibility level.
mssql_list_tables	Tables with row count and size (MB), optionally filtered by schema.
mssql_sample_data	Preview rows from a table (default 10, max 100) — no SQL needed, injection-safe.

Schema exploration

Tool	Description
mssql_get_schema	Columns, data types, PK/FK per table.
mssql_get_relationships	Foreign-key graph: from/to table+column, delete/update actions.
mssql_get_views	Views with full SQL definitions.
mssql_get_stored_procedures	Stored procedures with parameters and full definitions.
mssql_search_definitions	Search the source of all procs/views/functions/triggers for a text fragment — impact analysis for legacy systems.

Performance & storage

Tool	Description
mssql_analyze_indexes	Index usage stats (seeks/scans/updates) + optimizer-suggested missing indexes.
mssql_index_fragmentation	Fragmentation per index with REBUILD (≥ 30%) / REORGANIZE (5–30%) recommendations and ready-to-run ALTER INDEX statements (ONLINE = ON added automatically on editions that support it).
mssql_top_queries	Most expensive queries ranked by cpu, duration, reads, writes, memory (grant size), or executions — totals, averages, and SQL text.
mssql_performance_health	Health check: top wait stats (benign waits filtered), memory counters (PLE, grants pending, total vs target), workload counters, plus rule-based tuning recommendations.
mssql_analyze_storage	Largest tables by size + database file sizes.
mssql_monitor_usage	Sessions, CPU, buffer cache, top queries by CPU.

Locks, blocking & deadlocks

Supported on SQL Server 2019 (15.x), 2022 (16.x), and 2025 (17.x) — all editions including Express. Output includes the detected server version/edition and warns when running on an older version (best-effort). Requires VIEW SERVER STATE.

Version & edition compatibility:

	Express	Standard	Enterprise / Developer / Eval	Azure SQL MI	Azure SQL DB
Query / schema / storage tools	✅	✅	✅	✅	✅
mssql_monitor_locks / mssql_find_blocking	✅	✅	✅	✅	✅
mssql_get_deadlocks (system_health XE)	✅	✅	✅	✅	❌ (tool explains alternatives)
mssql_list_databases / mssql_monitor_usage	✅	✅	✅	✅	⚠️ limited scope

Rows/columns above apply to SQL Server 2019, 2022, and 2025. Older versions (2016/2017) mostly work but are reported as best-effort in the tool output. mssql_test_connection reports the detected edition class and its engine limits (e.g. Express: 10 GB/database, ~1.4 GB buffer pool, 4 cores).

Tool	Description
mssql_monitor_locks	Raw view of current locks and waits per session.
mssql_find_blocking	Blocking chains (victim ← blocker), lead-blocker identification incl. idle sessions holding open transactions, with SQL text of both sides.
mssql_get_deadlocks	Recent deadlock events from the built-in system_health Extended Events session: victims, involved queries, and full deadlock-graph XML. source: "ring_buffer" (fast, recent) or "file" (further back).

Claude Examples:

"Show me the top 10 customers by order count"
"Which tables are the largest, and which indexes are unused?"
"Which sessions are blocked right now, and who is the root blocker?"
"Were there any deadlocks last night, and which query caused them?"
"Find every stored procedure that references the CustomerOrders table"

Security Setup

Read-only is enforced in two layers. The database login is the primary guard — even a write statement that reached the server cannot execute. The application-level allow-list (classifyQuery in src/index.ts) is defense-in-depth: it accepts only read-only entry points and rejects everything else (writes, DDL, dynamic SQL, stacked queries). Because it allow-lists the leading keyword rather than blocking words, columns or aliases named Create, Update, CreatedDate, etc. are not blocked.

Create Read-Only SQL User (Recommended)

A ready-to-run script is provided at scripts/create-readonly-login.sql — edit the placeholders and run it as a sysadmin. It applies the least-privilege setup below:

-- 1. Create login
CREATE LOGIN mcp_readonly WITH PASSWORD = 'SecurePassword123!';

-- 2. Switch to your database
USE YourDatabase;

-- 3. Create user
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;

-- 4. Grant read permissions
ALTER ROLE db_datareader ADD MEMBER mcp_readonly;

-- 4b. Explicitly DENY writes (defense-in-depth)
ALTER ROLE db_denydatawriter ADD MEMBER mcp_readonly;

-- 5. Grant monitoring permissions
GRANT VIEW SERVER STATE TO mcp_readonly;
GRANT VIEW DATABASE STATE TO mcp_readonly;
GRANT VIEW DEFINITION TO mcp_readonly;

-- 6. Verify permissions
SELECT
    dp.name AS DatabaseUser,
    dp.type_desc,
    r.name AS RoleName
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE dp.name = 'mcp_readonly';

Development

Build

npm run build

Watch Mode

npm run dev

Testing Locally

# Set environment variables
export MSSQL_SERVER=localhost
export MSSQL_DATABASE=testdb
export MSSQL_USER=sa
export MSSQL_PASSWORD=password

# Run
npm start

Troubleshooting

"command not found" Error with npx

If you get an error running with npx:

1. Ensure Node.js 18+ is installed:

node --version

2. Clear npm cache:

npm cache clean --force

3. Try with full package name:

npx --package=@piyapat/mssql-mcp-server mssql-mcp-server

Connection Errors

Error: Login failed for user

-- Check authentication mode (must be Mixed Mode)
USE master;
GO
EXEC xp_instance_regread
  N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'LoginMode';
GO
-- Should return 2 for Mixed Mode

Error: Cannot connect to server

• Verify SQL Server Browser service is running
• Check firewall allows port 1433
• Ensure TCP/IP protocol is enabled in SQL Server Configuration Manager

Permission Errors

-- Grant additional permissions if needed
USE YourDatabase;
GRANT EXECUTE TO mcp_readonly;  -- If you need to call stored procedures
GRANT SHOWPLAN TO mcp_readonly; -- For execution plans

Best Practices

1. Always use read-only accounts in production
2. Enable SSL encryption for network security
3. Monitor regularly - Set up regular monitoring checks
4. Limit result sets - Use maxRows to prevent memory issues
5. Index optimization - Monitor slow queries and add indexes
6. Regular maintenance - Keep statistics updated
7. Audit access - Track who queries what

Contributing

Contributions are welcome — see CONTRIBUTING.md for development setup, pull-request guidelines, and the release process. Version history is tracked in CHANGELOG.md.

License

MIT License - Feel free to use and modify for your needs.

Built With

• @modelcontextprotocol/sdk - MCP SDK
• mssql - SQL Server client for Node.js

Support

For issues:

1. Check the Troubleshooting section
2. Review SQL Server error logs
3. Verify Claude Desktop logs
4. Check database permissions

---

Built for Claude Desktop • Security First • Performance Focused