mcp-server-mssqlserver by liliangshan - MCP Server

MCP Server for MSSQL Server

A comprehensive Model Context Protocol (MCP) server for MSSQL Server with advanced DDL support, granular permission control, comprehensive operation logging, and enterprise-grade connection management.

🚀 Key Features

Core Capabilities

Full SQL Support: Complete DDL and DML operation support with SQL injection protection
Advanced Permission Control: Granular control over DDL, DROP, DELETE operations via environment variables
Comprehensive Logging: Complete request/response logging, SQL operation tracking, and connection pool monitoring
Enterprise Connection Management: Automatic connection pool creation, health monitoring, and failover recovery
MCP Protocol Compliance: Full Model Context Protocol implementation with tool discovery and execution
Multi-Editor Support: Seamless integration with Cursor, VS Code, and other MCP-compatible editors

Advanced Features

Schema Auto-Prefixing: Automatic schema handling for multi-tenant environments
Connection Pool Optimization: Intelligent connection reuse and performance tuning
Error Handling: Comprehensive error logging and graceful failure recovery
Security Features: SQL injection prevention, parameterized query support, and operation validation

📦 Installation

NPM Installation

Global Installation (Recommended)

npm install -g @liangshanli/mcp-server-mssqlserver

Local Installation

npm install @liangshanli/mcp-server-mssqlserver

Manual Installation

git clone https://github.com/liliangshan/mcp-server-mssqlserver.git
cd mcp-server-mssqlserver
npm install

⚙️ Configuration

Environment Variables

Database Connection Settings

Environment Variable	Default	Description	Required
`MSSQL_SERVER`	`localhost`	MSSQL Server host address	✅
`MSSQL_PORT`	`1433`	MSSQL Server port number	✅
`MSSQL_USER`	`sa`	Database username	✅
`MSSQL_PASSWORD`	``	Database password	✅
`MSSQL_DATABASE`	``	Target database name	✅
`MSSQL_SCHEMA`	`dbo`	Default schema name	✅
`MSSQL_ENCRYPT`	`false`	Enable encryption	❌
`MSSQL_TRUST_SERVER_CERTIFICATE`	`true`	Trust server certificate	❌

Permission Control Settings

Environment Variable	Default	Description	Security Level
`ALLOW_DDL`	`false`	Allow CREATE, ALTER, DROP operations	🔴 High Risk
`ALLOW_DROP`	`false`	Allow DROP TABLE operations	🔴 High Risk
`ALLOW_DELETE`	`false`	Allow DELETE operations	🟡 Medium Risk

Logging Configuration

Environment Variable	Default	Description
`MCP_LOG_DIR`	`./logs`	Log directory path
`MCP_LOG_FILE`	`mcp-mssqlserver.log`	Log filename

Security Configuration Examples

Development Environment (High Permissions)

export ALLOW_DDL=true
export ALLOW_DROP=true
export ALLOW_DELETE=true

Testing Environment (Medium Permissions)

export ALLOW_DDL=true
export ALLOW_DROP=false
export ALLOW_DELETE=true

Production Environment (Restricted Permissions)

export ALLOW_DDL=false
export ALLOW_DROP=false
export ALLOW_DELETE=false

🚀 Usage

Starting the Server

Method 1: CLI Startup

npm start

Method 2: Direct Server Execution

npm run server

Method 3: Managed Mode

npm run start-managed

Method 4: Development Mode (with debugging)

npm run dev

Complete Environment Setup Example

# Database Connection
export MSSQL_SERVER=your-mssql-server.com
export MSSQL_PORT=1433
export MSSQL_USER=your-username
export MSSQL_PASSWORD=your-secure-password
export MSSQL_DATABASE=your-database
export MSSQL_SCHEMA=your-schema

# Security Permissions
export ALLOW_DDL=true
export ALLOW_DROP=false
export ALLOW_DELETE=true

# Logging Configuration
export MCP_LOG_DIR=/var/log/mcp
export MCP_LOG_FILE=mssqlserver.log

# Start Server
npm start

🛠️ Available Tools

1. sql_query

Execute SQL queries with full DDL and DML support.

Parameters:

sql (string, required): SQL statement to execute

Features:

Automatic schema prefixing
Permission validation
Comprehensive error handling
SQL injection protection

Example:

{
  "name": "sql_query",
  "arguments": {
    "sql": "SELECT * FROM users WHERE id = 1"
  }
}

Supported Operations:

✅ SELECT queries
✅ INSERT operations
✅ UPDATE operations
✅ DELETE operations (if permitted)
✅ CREATE TABLE (if DDL permitted)
✅ ALTER TABLE (if DDL permitted)
✅ DROP TABLE (if DROP permitted)

2. get_database_info

Retrieve comprehensive database information and configuration details.

Parameters: None

Returns:

Database list
Table list for current database
Current configuration
Permission status
Connection information

Example:

{
  "name": "get_database_info",
  "arguments": {}
}

3. get_operation_logs

Access detailed operation logs for monitoring and debugging.

Parameters:

limit (number, optional): Number of log entries to return (default: 50)
offset (number, optional): Starting position for pagination (default: 0)

Log Information:

Request/response details
SQL execution results
Error messages and stack traces
Connection pool status
Performance metrics

Example:

{
  "name": "get_operation_logs",
  "arguments": {
    "limit": 100,
    "offset": 0
  }
}

4. check_permissions

Verify current permission configuration and access rights.

Parameters: None

Returns:

Current permission settings
Environment variable status
Configuration validation
Security recommendations

Example:

{
  "name": "check_permissions",
  "arguments": {}
}

📊 Logging and Monitoring

Log Categories

1. Request/Response Logs

All MCP method calls
Parameter validation
Response generation
Error handling

2. SQL Operation Logs

SQL statement execution
Query results
Performance metrics
Error details

3. Connection Pool Logs

Pool creation/destruction
Health check results
Connection failures
Recovery attempts

4. System Event Logs

Server startup/shutdown
Configuration changes
Permission updates
Security events

Log File Location

Default: ./logs/mcp-mssqlserver.log
Customizable: Via MCP_LOG_DIR and MCP_LOG_FILE environment variables

Log Format

2025-01-15T10:30:00.000Z | method_name | {"param1": "value1"} | SUCCESS | RESPONSE: {"result": "data"}
2025-01-15T10:30:01.000Z | SQL: SELECT * FROM users | SUCCESS

🔒 Security Features

Permission Control

Granular Access Control: Fine-grained permission management for different operation types
Environment-Based Security: Secure configuration through environment variables
Operation Validation: Real-time permission checking before SQL execution
Audit Logging: Complete audit trail of all operations

SQL Injection Protection

Parameterized Queries: Support for prepared statements
Input Validation: Comprehensive input sanitization
Error Masking: Secure error message handling
Query Logging: Complete SQL operation tracking

Connection Security

Encrypted Connections: Support for SSL/TLS encryption
Certificate Validation: Configurable certificate trust settings
Connection Pooling: Secure connection management
Timeout Protection: Configurable connection and request timeouts

🎯 Editor Integration

Cursor Editor Configuration

Create .cursor/mcp.json in your project root:

{
  "mcpServers": {
    "mssqlserver": {
      "command": "npx",
      "args": ["@liangshanli/mcp-server-mssqlserver"],
      "env": {
        "MSSQL_SERVER": "your_host",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "your_user",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_SCHEMA": "your_schema",
        "MSSQL_ENCRYPT": "false",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "true",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

VS Code Configuration

Install VS Code MCP extension
Create .vscode/settings.json:

{
  "mcp.servers": {
    "mssqlserver": {
      "command": "npx",
      "args": ["@liangshanli/mcp-server-mssqlserver"],
      "env": {
        "MSSQL_SERVER": "your_host",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "your_user",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_SCHEMA": "your_schema",
        "MSSQL_ENCRYPT": "false",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "true",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

Direct MCP Server Usage

Server communicates via stdin/stdout with MCP clients:

{"jsonrpc": "2.0", "id": 1, "method": "initialize", "params": {"protocolVersion": "2025-06-18"}}

🔧 Connection Pool Management

Automatic Features

Auto-Creation: Connection pool automatically created on notifications/initialized
Health Monitoring: 5-minute interval health checks
Auto-Recovery: Automatic pool recreation on failure
Performance Optimization: Intelligent connection reuse
Graceful Shutdown: Proper connection cleanup on server termination

Configuration Options

Pool Size: Configurable minimum and maximum connections
Timeout Settings: Customizable connection and request timeouts
Retry Logic: Configurable retry attempts for failed connections
Load Balancing: Intelligent connection distribution

🚨 Troubleshooting

Common Issues

1. Connection Failures

Symptoms:

"Connection refused" errors
Timeout errors
Authentication failures

Solutions:

Verify MSSQL Server is running
Check firewall settings
Validate connection parameters
Confirm network connectivity

2. Permission Errors

Symptoms:

"Operation not allowed" errors
"Access denied" messages
Permission validation failures

Solutions:

Check environment variable configuration
Verify database user permissions
Confirm ALLOW_* settings
Review security configuration

3. Logging Issues

Symptoms:

Missing log files
Incomplete log entries
Permission denied errors

Solutions:

Check log directory permissions
Verify disk space availability
Confirm log file path validity
Review file system permissions

Debug Mode

Enable detailed debugging information:

npm run dev

Debug Information:

Detailed SQL execution logs
Connection pool status
Permission validation details
Error stack traces
Performance metrics

🏗️ Development

Project Structure

mcp-server-mssqlserver/
├── bin/
│   └── cli.js              # CLI startup script
├── src/
│   └── server-final.js     # Main server implementation
├── start-server.js         # Managed startup script
├── package.json            # Project configuration
├── README.md              # English documentation
├── README.zh-CN.md        # Chinese documentation
└── SCHEMA_USAGE.md        # Schema usage guide

Building and Testing

# Install dependencies
npm install

# Run basic tests
npm test

# Run comprehensive tests
npm run test-full

# Start development server
npm run dev

# Build for production
npm run build

Development Features

Hot Reloading: Automatic server restart on code changes
Debug Logging: Comprehensive debugging information
Error Handling: Detailed error reporting and stack traces
Performance Monitoring: Real-time performance metrics

📄 License

MIT License - See LICENSE file for details

🤝 Contributing

We welcome contributions! Please see our contributing guidelines:

Fork the repository
Create a feature branch
Make your changes
Add tests if applicable
Submit a pull request

Contribution Areas

Bug Fixes: Report and fix bugs
Feature Development: Add new capabilities
Documentation: Improve documentation
Testing: Add test coverage
Performance: Optimize performance

🔗 Related Links

Model Context Protocol - Official MCP specification
MSSQL Node.js Driver - Database driver documentation
MCP Specification - Complete protocol documentation
GitHub Repository - Source code and issues

📞 Support

Getting Help

GitHub Issues: Report bugs and request features
Documentation: Comprehensive guides and examples
Examples: Working examples and use cases
Community: Join our developer community

Version Compatibility

Node.js: 18.x or higher
MSSQL Server: 2012 or higher
MCP Protocol: 2025-06-18 or compatible versions