SQL Server MCP Colossal

A comprehensive Model Context Protocol (MCP) server for SQL Server database operations. This server provides tools for connecting to SQL Server databases and performing CRUD operations through Claude Desktop and other MCP clients.

🚀 Features

• 🔌 Easy Configuration: Simple setup with server, database, and authentication details
• 🔍 Database Exploration: List tables, views, describe structures, and browse data
• 📊 CRUD Operations: Create, Read, Update, and Delete data with parameterized queries
• 🔒 Safety Features: Confirmation prompts for destructive operations (UPDATE/DELETE)
• 👁️ Views Support: Complete view management and data access
• ⚙️ Stored Procedures: Full support for stored procedure operations
• 🔍 Query Analysis: Execution plan analysis with optimization recommendations
• 🛡️ Security: Support for encrypted connections and certificate validation
• ⚡ Performance: Async operations with connection pooling
• 🔧 Flexible: Support for multiple ODBC drivers and custom configurations
• ✅ Type Safety: Comprehensive Pydantic validation for all inputs

📋 Table of Contents

• Installation
• Configuration
• Usage
• Available Tools
• Safety Features
• Examples
• Development
• Troubleshooting
• Security
• Contributing

🛠️ Installation

Prerequisites

• Python 3.10 or higher
• SQL Server with ODBC Driver 17 (or compatible driver)
• Claude Desktop (for testing) or other MCP client

Install Dependencies

# Install Python dependencies
pip install -r requirements.txt

# Or install in development mode
pip install -e .

Install ODBC Driver

Windows:

# Download and install Microsoft ODBC Driver 17 for SQL Server
# https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Linux (Ubuntu/Debian):

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

macOS:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

⚙️ Configuration

Method 1: Environment Variables (Recommended)

Create a .env file in the config directory:

SQLSERVER_HOST=your-server-hostname
SQLSERVER_DATABASE=your-database-name
SQLSERVER_USERNAME=your-username
SQLSERVER_PASSWORD=your-password
SQLSERVER_PORT=1433
SQLSERVER_DRIVER={ODBC Driver 17 for SQL Server}
SQLSERVER_TRUST_CERT=true
SQLSERVER_ENCRYPT=true

Method 2: Configuration File

Create config/sqlserver_config.json:

{
  "server": "your-server-hostname",
  "database": "your-database-name",
  "username": "your-username",
  "password": "your-password",
  "port": 1433,
  "driver": "{ODBC Driver 17 for SQL Server}",
  "trust_server_certificate": true,
  "encrypt": true
}

Method 3: Runtime Configuration

Use the configure_sqlserver tool to set up the connection dynamically.

🚀 Usage

Starting the Server

# Start the MCP server
python src/server.py

# Or using the npm script
npm start

Claude Desktop Integration

Add the following to your Claude Desktop configuration file:

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

{
  "mcpServers": {
    "sqlserver-colossal": {
      "command": "python",
      "args": [
        "C:\\path\\to\\sqlserver-mcp-colossal\\src\\server.py"
      ],
      "env": {
        "SQLSERVER_HOST": "your-server",
        "SQLSERVER_DATABASE": "your-database",
        "SQLSERVER_USERNAME": "your-username",
        "SQLSERVER_PASSWORD": "your-password"
      }
    }
  }
}

🛠️ Available Tools

1. configure_sqlserver

Configure SQL Server connection parameters.

Parameters:

• server: SQL Server hostname or IP address
• database: Database name
• username: Username for authentication
• password: Password for authentication
• port: SQL Server port (default: 1433)
• driver: ODBC driver (default: ODBC Driver 17 for SQL Server)
• trust_server_certificate: Trust server certificate (default: True)
• encrypt: Use encryption (default: True)

2. execute_query

Execute any SQL query and return results.

Parameters:

• query: SQL query to execute
• params: Optional JSON string of parameters for parameterized queries

3. list_tables

List all tables in the current database.

4. describe_table

Get detailed information about a table structure.

Parameters:

• table_name: Name of the table to describe
• schema_name: Schema name (default: dbo)

5. insert_data

Insert data into a table.

Parameters:

• table_name: Name of the table to insert into
• data: JSON string containing the data to insert (key-value pairs)
• schema_name: Schema name (default: dbo)

6. update_data ⚠️ Requires Confirmation

Update data in a table. This operation requires explicit confirmation.

Parameters:

• table_name: Name of the table to update
• data: JSON string containing the data to update (key-value pairs)
• where_clause: WHERE clause for the update (without the WHERE keyword)
• schema_name: Schema name (default: dbo)
• confirm: Must be set to true to proceed with the update

7. delete_data ⚠️ Requires Confirmation

Delete data from a table. This operation requires explicit confirmation.

Parameters:

• table_name: Name of the table to delete from
• where_clause: WHERE clause for the delete (without the WHERE keyword)
• schema_name: Schema name (default: dbo)
• confirm: Must be set to true to proceed with the deletion

8. get_table_data

Get data from a table with optional limit.

Parameters:

• table_name: Name of the table to query
• limit: Maximum number of rows to return (default: 100)
• schema_name: Schema name (default: dbo)

9. list_views

List all views in the current database.

Parameters:

• schema_name: Schema name (default: dbo)

10. describe_view

Get detailed information about a view structure.

Parameters:

• view_name: Name of the view to describe
• schema_name: Schema name (default: dbo)

11. get_view_data

Get data from a view with optional limit.

Parameters:

• view_name: Name of the view to query
• limit: Maximum number of rows to return (default: 100)
• schema_name: Schema name (default: dbo)

12. list_stored_procedures

List all stored procedures in the current database.

Parameters:

• schema_name: Schema name (default: dbo)

13. describe_stored_procedure

Get detailed information about a stored procedure.

Parameters:

• procedure_name: Name of the stored procedure to describe
• schema_name: Schema name (default: dbo)

14. execute_stored_procedure

Execute a stored procedure with parameters.

Parameters:

• procedure_name: Name of the stored procedure to execute
• parameters: JSON string with parameter names and values
• schema_name: Schema name (default: dbo)

15. analyze_query_plan

Analyze query execution plan and provide optimization recommendations.

Parameters:

• query: SQL query to analyze
• params: Optional JSON string of parameters for parameterized queries

🔒 Safety Features

Confirmation Requirements

For safety, the following operations require explicit confirmation:

Update Operations

# This will fail without confirmation
update_data(
    table_name="users",
    data='{"status": "inactive"}',
    where_clause="last_login < '2023-01-01'"
)

# This will succeed with confirmation
update_data(
    table_name="users",
    data='{"status": "inactive"}',
    where_clause="last_login < '2023-01-01'",
    confirm=True
)

Delete Operations

# This will fail without confirmation
delete_data(
    table_name="temp_data",
    where_clause="created_date < '2023-01-01'"
)

# This will succeed with confirmation
delete_data(
    table_name="temp_data",
    where_clause="created_date < '2023-01-01'",
    confirm=True
)

Safety Messages

When confirmation is required, you'll see messages like:

⚠️ WARNING: This operation will modify data in the database.
Table: users
Operation: UPDATE
WHERE clause: last_login < '2023-01-01'
Rows affected: Estimated 1,250 rows

To proceed, add confirm=True to your request.

📚 Examples

1. Configure Connection

Configure SQL Server connection:
- Server: localhost
- Database: AdventureWorks
- Username: sa
- Password: YourPassword123

2. List Tables

List all tables in the database

3. Describe Table Structure

Describe the structure of the 'Products' table

4. Insert Data

Insert new product data:
- Table: Products
- Data: {"Name": "New Product", "Price": 29.99, "Category": "Electronics"}

5. Query Data

Get all products with price greater than $50

6. Update Data (with confirmation)

Update product price:
- Table: Products
- Data: {"Price": 39.99}
- Where: ProductID = 1
- Confirm: true

7. Delete Data (with confirmation)

Delete discontinued products:
- Table: Products
- Where: Category = 'Discontinued'
- Confirm: true

8. Work with Views

List all views in the database
Describe the structure of the 'CustomerOrders' view
Show me the first 20 rows from the 'CustomerOrders' view

9. Work with Stored Procedures

List all stored procedures in the database
Describe the 'GetCustomerOrders' stored procedure
Execute the 'GetCustomerOrders' procedure with parameters: {"CustomerID": 123}

10. Analyze Query Performance

Analyze the execution plan for: SELECT * FROM Orders WHERE CustomerID = 123
Get optimization recommendations for: SELECT o.*, c.Name FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID

🔧 Development

Running Tests

# Run all tests
pytest tests/

# Run with coverage
pytest --cov=src tests/

# Run specific test file
pytest tests/test_models.py -v

Code Formatting

# Format code
black src/ tests/

# Sort imports
isort src/ tests/

# Type checking
mypy src/

Building Package

# Build Python package
python -m build

# Install in development mode
pip install -e .

🐛 Troubleshooting

Connection Issues

1. ODBC Driver Not Found

   • Ensure ODBC Driver 17 for SQL Server is installed
   • Check driver name in configuration

2. Authentication Failed

   • Verify username and password
   • Check SQL Server authentication mode
   • Ensure user has appropriate permissions

3. Network Issues

   • Verify server hostname/IP and port
   • Check firewall settings
   • Test network connectivity

Common Errors

• "SQL Server not configured": Run configure_sqlserver first
• "Invalid JSON format": Ensure data parameters are valid JSON
• "Table not found": Check table name and schema
• "Permission denied": Verify user has appropriate database permissions
• "Confirmation required": Add confirm=True for UPDATE/DELETE operations

Performance Tips

• Use LIMIT/TOP clauses for large result sets
• Index frequently queried columns
• Use parameterized queries for better performance
• Consider connection pooling for high-volume operations
• Use execution plan analysis to optimize slow queries

🔐 Security Considerations

Credentials Management

• Store sensitive credentials in environment variables or secure configuration files
• Never commit passwords to version control
• Rotate passwords regularly

Network Security

• Use encrypted connections in production environments
• Implement proper certificate validation
• Follow principle of least privilege for database users
• Regularly rotate passwords and access keys

Database Security

• Use encrypted connections (encrypt=true)
• Configure proper firewall rules
• Use VPN for remote connections
• Follow principle of least privilege
• Use dedicated service accounts
• Enable SQL Server audit logging

Confirmation Safety

• Always review WHERE clauses before confirming UPDATE/DELETE operations
• Test queries on non-production data first
• Use transactions for complex operations
• Backup data before major changes

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Run the test suite
6. Submit a pull request

Development Guidelines

• Follow PEP 8 style guidelines
• Add type hints to all functions
• Write comprehensive tests
• Update documentation for new features
• Use meaningful commit messages

📄 License

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

🆘 Support

For support and questions:

• Create an issue on GitHub
• Contact: [email protected]
• Documentation: MCP Documentation

📈 Changelog

v1.2.0

• Added comprehensive safety features with confirmation requirements for UPDATE/DELETE operations
• Enhanced Pydantic models with latest field_validator syntax
• Added detailed safety documentation and best practices
• Improved error handling and validation
• Updated all dependencies to latest versions
• Enhanced documentation with comprehensive examples

v1.1.0

• Added views support (list_views, describe_view, get_view_data)
• Added stored procedures support (list_stored_procedures, describe_stored_procedure, execute_stored_procedure)
• Added query execution plan analysis (analyze_query_plan)
• Added comprehensive Pydantic validation for all inputs
• Added confirmation requirements for UPDATE/DELETE operations
• Enhanced error handling and type safety
• Updated documentation with comprehensive examples

v1.0.0

• Initial release
• Basic CRUD operations
• SQL Server connectivity
• Claude Desktop integration
• Configuration management

🔗 Related Documentation

• Quick Start Guide
• Execution Plan Analysis Guide
• Deployment Guide
• Model Context Protocol Documentation