tevinric/sse-mcp-text

MCP SQL Server with SSE Transport

A robust Model Context Protocol (MCP) server implementation for SQL Server databases with Server-Sent Events (SSE) transport, designed to work seamlessly with Large Language Models like GPT-4o.

🚀 Features

• SSE Transport: Modern Server-Sent Events transport for remote connections
• SQL Server Integration: Full support for Microsoft SQL Server databases
• Security First: Read-only queries with parameterized statements
• Schema Discovery: Automatic database schema exploration
• LLM Ready: Pre-built integration with OpenAI GPT models
• Docker Support: Complete containerization with Docker Compose
• Comprehensive Tooling: Multiple tools for database exploration and querying

📋 Prerequisites

• Python 3.11+
• SQL Server (local or remote)
• ODBC Driver 17 for SQL Server
• Docker (for containerized deployment)
• OpenAI API key (for LLM integration)

🛠 Installation

Option 1: Local Installation

1. Clone and Setup

   git clone <repository-url>
   cd mcp-sqlserver
   
   # Create virtual environment
   python -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate
   
   # Install dependencies
   pip install -r requirements.txt
   

2. Install SQL Server ODBC Driver

   Ubuntu/Debian:

   curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
   sudo apt-get update
   sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
   

   macOS:

   brew install microsoft/mssql-release/msodbcsql17
   

   Windows: Download and install from Microsoft's official site

Option 2: Docker Installation

# Clone repository
git clone <repository-url>
cd mcp-sqlserver

# Build and run with Docker Compose
docker-compose up --build

⚙ Configuration

Environment Variables

Create a .env file with your SQL Server configuration:

# SQL Server Connection
SQLSERVER_HOST=localhost
SQLSERVER_DATABASE=your_database
SQLSERVER_USERNAME=your_username
SQLSERVER_PASSWORD=your_password

# Optional Settings
SQLSERVER_DRIVER=ODBC Driver 17 for SQL Server
SQLSERVER_TRUST_CERT=true
SQLSERVER_ENCRYPT=true
SQLSERVER_TIMEOUT=30

# MCP Settings
MAX_QUERY_ROWS=100
PORT=8000

# LLM Integration
OPENAI_API_KEY=your_openai_api_key

Authentication Options

SQL Server Authentication:

SQLSERVER_USERNAME=sa
SQLSERVER_PASSWORD=YourPassword123

Windows Authentication:

# Leave username and password empty for Windows auth
SQLSERVER_HOST=localhost
SQLSERVER_DATABASE=master

🚀 Usage

1. Start the MCP Server

SSE Transport (Recommended):

# Local installation
uvicorn mcp_sqlserver:mcp.sse_app --host 0.0.0.0 --port 8000

# Or with Docker
docker-compose up mcp-sqlserver

STDIO Transport:

python mcp_sqlserver.py

2. Test Connection

# Test database connectivity
docker-compose run mcp-sqlserver test

# Or locally
python -c "
import asyncio
from mcp_sqlserver import DatabaseConfig, SQLServerConnection
# ... test code
"

3. Use with LLM

# Interactive session
python llm_client.py

# Run examples
python llm_client.py examples

🔧 MCP Tools & Resources

Tools

Resources

Prompts

🐳 Docker Deployment

Development Setup

# Start with SQL Server included
docker-compose --profile dev up

# This starts:
# - MCP Server on port 8000
# - SQL Server on port 1433

Production Setup

# Production deployment
docker-compose up -d

# Scale if needed
docker-compose up -d --scale mcp-sqlserver=3

Environment Configuration

# docker-compose.override.yml
version: '3.8'
services:
  mcp-sqlserver:
    environment:
      SQLSERVER_HOST: your-production-server.com
      SQLSERVER_DATABASE: production_db
      SQLSERVER_USERNAME: production_user
      SQLSERVER_PASSWORD: ${PROD_PASSWORD}

🤖 LLM Integration Examples

Basic Query

from llm_client import LLMAssistant

assistant = LLMAssistant(api_key="your-key")
await assistant.initialize()

response = await assistant.chat("What tables do we have?")
print(response)

Advanced Integration

# Custom tool integration
class CustomSQLAssistant(LLMAssistant):
    async def analyze_business_metrics(self, metric_type: str):
        query = f"""
        SELECT TOP 10 * FROM sales_data 
        WHERE metric_type = '{metric_type}'
        ORDER BY date_created DESC
        """
        return await self.mcp_client.execute_sql_tool(query)

Claude Desktop Integration

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "sqlserver": {
      "command": "python",
      "args": ["/path/to/mcp_sqlserver.py"],
      "env": {
        "SQLSERVER_HOST": "localhost",
        "SQLSERVER_DATABASE": "your_db",
        "SQLSERVER_USERNAME": "your_user",
        "SQLSERVER_PASSWORD": "your_password"
      }
    }
  }
}

🔒 Security Considerations

Database Security

1. Principle of Least Privilege

   -- Create dedicated read-only user
   CREATE LOGIN mcp_readonly WITH PASSWORD = 'SecurePassword123!';
   USE your_database;
   CREATE USER mcp_readonly FOR LOGIN mcp_readonly;
   ALTER ROLE db_datareader ADD MEMBER mcp_readonly;
   

2. Query Restrictions

   • Only SELECT statements allowed
   • Parameterized queries prevent SQL injection
   • Result set limits prevent resource exhaustion

3. Connection Security

   • TLS encryption enabled by default
   • Certificate validation options
   • Connection timeouts configured

Network Security

# docker-compose.yml security additions
services:
  mcp-sqlserver:
    networks:
      - internal
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: '0.5'

networks:
  internal:
    driver: bridge
    internal: true

📊 Monitoring & Logging

Health Checks

# Check server health
curl http://localhost:8000/health

# Docker health check
docker-compose ps

Logging Configuration

# Enhanced logging
import structlog

logger = structlog.get_logger()
logger.info("Query executed", 
           query=query, 
           execution_time=elapsed,
           row_count=len(results))

Metrics Collection

# Prometheus metrics (optional)
from prometheus_client import Counter, Histogram

QUERY_COUNTER = Counter('mcp_queries_total', 'Total queries executed')
QUERY_DURATION = Histogram('mcp_query_duration_seconds', 'Query execution time')

🐛 Troubleshooting

Common Issues

1. ODBC Driver Not Found

   # Check available drivers
   odbcinst -q -d
   
   # Install driver
   sudo ACCEPT_EULA=Y apt-get install msodbcsql17
   

2. Connection Timeouts

   # Increase timeout
   export SQLSERVER_TIMEOUT=60
   

3. Permission Denied

   -- Grant necessary permissions
   USE your_database;
   GRANT SELECT ON SCHEMA::dbo TO mcp_readonly;
   

Debug Mode

# Enable debug logging
export LOG_LEVEL=DEBUG
python mcp_sqlserver.py

# Or with Docker
docker-compose run -e LOG_LEVEL=DEBUG mcp-sqlserver

Connection Testing

# Test SQL Server connectivity
sqlcmd -S localhost -U sa -P YourPassword123 -Q "SELECT @@VERSION"

# Test MCP server
curl -X POST http://localhost:8000/mcp \
  -H "Content-Type: application/json" \
  -d '{"method": "ping"}'

🧪 Development & Testing

Running Tests

# Install test dependencies
pip install pytest pytest-asyncio

# Run tests
pytest tests/

# With coverage
pytest --cov=mcp_sqlserver tests/

Development Setup

# Development dependencies
pip install -r requirements-dev.txt

# Pre-commit hooks
pre-commit install

# Code formatting
black mcp_sqlserver.py
flake8 mcp_sqlserver.py

📚 API Reference

MCP Protocol Messages

The server implements the full MCP specification:

• initialize - Server initialization
• list_tools - Available tools discovery
• call_tool - Tool execution
• list_resources - Resource discovery
• read_resource - Resource content retrieval
• list_prompts - Prompt templates
• get_prompt - Prompt retrieval

Custom Extensions

Additional capabilities beyond standard MCP:

• Real-time schema introspection
• Query performance metrics
• Result caching (planned)
• Custom SQL functions (planned)

🤝 Contributing

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Commit changes: git commit -m 'Add amazing feature'
4. Push to branch: git push origin feature/amazing-feature
5. Open a Pull Request

Development Guidelines

• Follow PEP 8 style guidelines
• Add comprehensive tests
• Update documentation
• Ensure Docker compatibility

📄 License

This project is licensed under the MIT License - see the file for details.

🙏 Acknowledgments

• Model Context Protocol by Anthropic
• MCP Python SDK
• FastMCP framework
• Microsoft SQL Server team

📞 Support

• 📧 Create an issue for bug reports
• 💬 Join discussions for questions
• 📖 Check the MCP documentation
• 🔍 Search existing issues before creating new ones

Made with ❤️ for the MCP community