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

🛠️ 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

ODBC Driver Not Found
- Ensure ODBC Driver 17 for SQL Server is installed
- Check driver name in configuration
Authentication Failed
- Verify username and password
- Check SQL Server authentication mode
- Ensure user has appropriate permissions
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

Fork the repository
Create a feature branch
Make your changes
Add tests for new functionality
Run the test suite
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: support@javiandev.com
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

Name		Name	Last commit message	Last commit date
Latest commit History 2 Commits
config		config
docs		docs
src		src
tests		tests
.gitignore		.gitignore
DEPLOYMENT.md		DEPLOYMENT.md
LICENSE		LICENSE
NPM_DEPLOYMENT_STATUS.md		NPM_DEPLOYMENT_STATUS.md
PROJECT_COMPLETION_SUMMARY.md		PROJECT_COMPLETION_SUMMARY.md
PROJECT_SUMMARY.md		PROJECT_SUMMARY.md
README.md		README.md
cli.py		cli.py
deploy.bat		deploy.bat
deploy.sh		deploy.sh
package.json		package.json
pyproject.toml		pyproject.toml
requirements.txt		requirements.txt
test_structure.py		test_structure.py

License

JavianDev/sqlserver-mcp-colossal

Folders and files

Latest commit

History

Repository files navigation

SQL Server MCP Colossal

🚀 Features

📋 Table of Contents

🛠️ Installation

Prerequisites

Install Dependencies

Install ODBC Driver

⚙️ Configuration

Method 1: Environment Variables (Recommended)

Method 2: Configuration File

Method 3: Runtime Configuration

🚀 Usage

Starting the Server

Claude Desktop Integration

🛠️ Available Tools

1. configure_sqlserver

2. execute_query

3. list_tables

4. describe_table

5. insert_data

6. update_data ⚠️ Requires Confirmation

7. delete_data ⚠️ Requires Confirmation

8. get_table_data

9. list_views

10. describe_view

11. get_view_data

12. list_stored_procedures

13. describe_stored_procedure

14. execute_stored_procedure

15. analyze_query_plan

🔒 Safety Features

Confirmation Requirements

Update Operations

Delete Operations

Safety Messages

📚 Examples

1. Configure Connection

2. List Tables

3. Describe Table Structure

4. Insert Data

5. Query Data

6. Update Data (with confirmation)

7. Delete Data (with confirmation)

8. Work with Views

9. Work with Stored Procedures

10. Analyze Query Performance

🔧 Development

Running Tests

Code Formatting

Building Package

🐛 Troubleshooting

Connection Issues

Common Errors

Performance Tips

🔐 Security Considerations

Credentials Management

Network Security

Database Security

Confirmation Safety

🤝 Contributing

Development Guidelines

📄 License

🆘 Support

📈 Changelog

v1.2.0

v1.1.0

v1.0.0

🔗 Related Documentation

About

Resources

License

Uh oh!

Stars

Watchers

1. `configure_sqlserver`

2. `execute_query`

3. `list_tables`

4. `describe_table`

5. `insert_data`

6. `update_data` ⚠️ Requires Confirmation

7. `delete_data` ⚠️ Requires Confirmation

8. `get_table_data`

9. `list_views`

10. `describe_view`

11. `get_view_data`

12. `list_stored_procedures`

13. `describe_stored_procedure`

14. `execute_stored_procedure`

15. `analyze_query_plan`

Packages