npm package: @piyapat/mssql-mcp-server
Description: Secure, read-only MCP server for Microsoft SQL Server with performance monitoring
Latest version: 1.0.1

# 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`:

```bash
npx @your-org/mssql-mcp-server
```

## Installation

### Option 1: Use with npx (Recommended for Testing)

```bash
# 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

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

# Run
mssql-mcp-server
```

### Option 3: Local Installation

```bash
# 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`

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

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

```json
{
  "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)

```sql
-- 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](package.json):
```json
{
  "name": "@your-org/mssql-mcp-server",
  // or
  "name": "mssql-mcp-server"
}
```

2. Login to npm:
```bash
npm login
```

3. Publish:
```bash
npm publish --access public
```

4. Users can then use it with:
```bash
npx @your-org/mssql-mcp-server
# or
npx mssql-mcp-server
```

## Development

### Build
```bash
npm run build
```

### Watch Mode
```bash
npm run dev
```

### Testing Locally
```bash
# 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:
```bash
node --version
```

2. Clear npm cache:
```bash
npm cache clean --force
```

3. Try with full package name:
```bash
npx --package=@your-org/mssql-mcp-server mssql-mcp-server
```

### Connection Errors

**Error: Login failed for user**
```sql
-- 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

```sql
-- 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](https://github.com/modelcontextprotocol/sdk) - MCP SDK
- [mssql](https://github.com/tediousjs/node-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**