LuisHRF/mcp_sqlserver

MCP SQL Server Integration

Project Overview

This Model Context Protocol (MCP) provides a secure, read-only SQL Server integration for Claude Desktop, enabling advanced database querying and exploration capabilities. The MCP serves as a bridge between Claude's natural language interface and SQL Server databases, allowing intelligent, context-aware database interactions.

Features

The MCP provides the following key tools for database interaction:

• execute_query: Run read-only SQL queries against the database
• list_tables: Retrieve a list of available tables in the database
• describe_table: Get detailed schema information for a specific table
• get_connection_info: Retrieve high-level connection metadata

Technology Stack

• Language: Python 3.9+
• Key Libraries:
  • MCP SDK
  • pyodbc
  • pydantic
• Database Driver: ODBC Driver 18 for SQL Server
• Supported SQL Server Version: SQL Server 2022

Installation

Prerequisites

1. Python 3.9 or higher
2. ODBC Driver 18 for SQL Server
3. Virtual environment recommended

Setup Steps

1. Clone the repository:

   git clone <repository-url>
   cd mcp_mssql
   

2. Create a virtual environment:

   python -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate
   

3. Install dependencies:

   pip install .
   

Configuration

Environment Variables

Create a .env file in the project root with the following configuration:

MSSQL_CONNECTION_STRING="Driver={ODBC Driver 18 for SQL Server};Server=your_server\instance;Database=your_database;UID=username;PWD=password;Encrypt=yes;TrustServerCertificate=yes;Application Intent=ReadOnly;"

Example with Windows Authentication:

MSSQL_CONNECTION_STRING="Driver={ODBC Driver 18 for SQL Server};Server=your_server\instance;Database=your_database;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes;Application Intent=ReadOnly;"

Connection String Options

• Use Windows Authentication (Trusted_Connection)
• Specify specific credentials if needed
• Ensure minimal read-only permissions

Claude Desktop Integration

Setup Configuration

1. Create or edit your Claude Desktop configuration file:

   • Windows: %APPDATA%\Claude\claude_desktop_config.json
   • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
   • Linux: ~/.config/Claude/claude_desktop_config.json

2. Add the MCP server configuration:

   {
     "mcpServers": {
       "mcp-mssql": {
         "command": "python",
         "args": ["-m", "mcp_mssql.server"],
         "cwd": "/path/to/your/mcp_mssql",
         "env": {
           "PYTHONPATH": "/path/to/your/mcp_mssql/src",
           "MSSQL_CONNECTION_STRING": "your_connection_string_here"
         }
       }
     }
   }
   

3. Restart Claude Desktop

4. Begin querying using natural language

Usage Examples

Querying Tables

• "List all tables in the database"
• "Show me the schema for the customers table"
• "Retrieve the top 10 rows from the sales table"

Supported Database Schemas

• dbo: Default schema
• fact: Fact tables for analytical queries
• err: Error and audit logging
• meta: Metadata and system information
• dim: Dimensional data for reporting

(These are some examples from my particular case and are included in the schema examples within server.py, in case they need to be changed)

Security Considerations

• Read-Only Access: Strictly prevents write operations
• Connection Security:
  • Uses minimal-privilege database accounts
  • Encrypts connection strings
  • No direct data modification capabilities

Limitations

• Read-only access only
• Cannot modify database schema
• No support for stored procedure execution
• Limited to SELECT queries
• Maximum result set size may be restricted

Prohibitions

Strictly prohibited operations:

• INSERT, UPDATE, DELETE statements
• Data Definition Language (DDL) commands
• Executing stored procedures
• Accessing system or sensitive tables
• Running complex or resource-intensive queries

Troubleshooting

Common Issues

1. Connection Failures

   • Verify connection string
   • Check network connectivity
   • Ensure ODBC driver is installed

2. Query Limitations

   • Simplify complex queries
   • Break down large result set requests
   • Avoid cross-database joins

3. Permission Problems

   • Confirm read-only access
   • Validate database user permissions

Logging

Enable verbose logging in the configuration for detailed diagnostics.

Contributing

Development Setup

1. Fork the repository
2. Create a virtual environment
3. Install development dependencies:

   pip install -e .[dev]
   

Running Tests

# Run validation tests
python validate_mcp_for_claude.py

Code Style

• Follow PEP 8 guidelines
• Use type hints
• Write comprehensive docstrings

Version

Current version: 0.1.0

Support

For issues or feature requests, please file an issue on the project repository.