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.

Quick Start with npx

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

npx @your-org/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 @your-org/mssql-mcp-server

Option 2: Global Installation

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

# Run
mssql-mcp-server

Option 3: Local Installation

# Clone and install
git clone <repository-url>
cd mssql-mcp-server
npm install
npm run build

# Run
npm start

Configuration for Claude Desktop

Using npx (Recommended)

Edit your Claude Desktop config:

Windows: %APPDATA%\Claude\claude_desktop_config.json

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

{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["@your-org/mssql-mcp-server"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "YourDatabase",
        "MSSQL_USER": "readonly_user",
        "MSSQL_PASSWORD": "SecurePassword",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false"
      }
    }
  }
}

Using Local Installation

{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["C:\\path\\to\\mssql-mcp-server\\build\\index.js"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "YourDatabase",
        "MSSQL_USER": "readonly_user",
        "MSSQL_PASSWORD": "SecurePassword",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false"
      }
    }
  }
}

Using Global Installation

{
  "mcpServers": {
    "mssql": {
      "command": "mssql-mcp-server",
      "args": [],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_DATABASE": "YourDatabase",
        "MSSQL_USER": "readonly_user",
        "MSSQL_PASSWORD": "SecurePassword",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false"
      }
    }
  }
}

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 | - | Yes | | MSSQL_PASSWORD | SQL Server password | - | Yes | | 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 |

Key Features

Security First

• ✅ Read-only enforcement - Blocks INSERT, UPDATE, DELETE, DROP, and all write operations
• ✅ 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

1. query_database

Execute read-only SQL queries with automatic write protection.

Claude Examples:

"Show me the top 10 customers by order count"
"Find all orders placed in the last 7 days"
"What's the average order value by region?"

2. get_schema

Explore database schema, tables, and relationships.

Claude Examples:

"Show me the schema for the Orders table"
"What are all the foreign keys in the database?"
"List all tables with their row counts"

3. get_stored_procedures

View stored procedure definitions and parameters.

Claude Examples:

"Show me all stored procedures"
"What parameters does sp_GetCustomerOrders accept?"
"Display the definition of sp_ProcessPayment"

4. monitor_locks

Detect locks, blocking, and potential deadlocks.

Claude Examples:

"Are there any blocked sessions right now?"
"Show me all database locks"
"Check for deadlock situations"

5. monitor_usage

Track CPU, memory, and query performance.

Claude Examples:

"What's the current CPU and memory usage?"
"Show me the top 10 most expensive queries"
"Which databases are using the most memory?"

Security Setup

Create Read-Only SQL User (Recommended)

-- 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;

-- 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';

Publishing to npm (For Package Maintainers)

If you want to publish this package to npm:

1. Update package name in package.json:

{
  "name": "@your-org/mssql-mcp-server",
  // or
  "name": "mssql-mcp-server"
}

2. Login to npm:

npm login

3. Publish:

npm publish --access public

4. Users can then use it with:

npx @your-org/mssql-mcp-server
# or
npx mssql-mcp-server

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=@your-org/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

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