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

Related MCP server: mssql-explorer-mcp

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

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

Schema exploration

Performance & storage

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:

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).

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