MSSQL MCP Server

A Model Context Protocol (MCP) server that provides comprehensive access to Microsoft SQL Server databases. This enhanced server enables Language Models to inspect database schemas, execute queries, manage database objects, and perform advanced database operations through a standardized interface.

🚀 Enhanced Features

Complete Database Schema Traversal

23 comprehensive database management tools (expanded from 5 basic operations)
Full database object hierarchy exploration - tables, views, stored procedures, indexes, schemas
Advanced database object management - create, modify, delete operations
Intelligent resource access - all tables and views available as MCP resources
Large content handling - retrieves complete stored procedures (1400+ lines) without truncation

Core Capabilities

Database Connection: Connect to MSSQL Server instances with flexible authentication
Schema Inspection: Complete database object exploration and management
Query Execution: Execute SELECT, INSERT, UPDATE, DELETE, and DDL queries
Stored Procedure Management: Create, modify, execute, and manage stored procedures
View Management: Create, modify, delete, and describe views
Index Management: Create, delete, and analyze indexes
Resource Access: Browse table and view data as MCP resources
Security: Read-only and write operations are properly separated and validated

⚠️ Important Usage Guidelines for Engineering Teams

Database Limitation

🔴 CRITICAL: Limit to ONE database per MCP server instance

This enhanced MCP server creates 23 tools per database
Cursor has a 40-tool limit across all MCP servers
Using multiple database instances will exceed Cursor's tool limit
For multiple databases, use separate MCP server instances in different projects

Large Content Limitations

⚠️ IMPORTANT: File operations not supported within chat context

Large stored procedures (1400+ lines) can be retrieved and viewed in chat
However, saving large content to files via MCP tools is not reliable due to token limits
For bulk data extraction: Use standalone Python scripts with direct database connections
Recommended approach: Copy-paste smaller procedures from chat, use external scripts for large ones

Tool Distribution

Core Tools: 5 (read_query, write_query, list_tables, describe_table, create_table)
Stored Procedures: 6 tools (create, modify, delete, list, describe, execute, get_parameters)
Views: 5 tools (create, modify, delete, list, describe)
Indexes: 4 tools (create, delete, list, describe)
Schema Management: 2 tools (list_schemas, list_all_objects)
Total: 23 tools + enhanced write_query supporting all database object operations

Installation

Prerequisites

Python 3.10 or higher
ODBC Driver 17 for SQL Server
Access to an MSSQL Server instance

Quick Setup

Clone or create the project directory:

mkdir mcp-sqlserver && cd mcp-sqlserver

Run the installation script:
```
chmod +x install.sh
./install.sh
```

Configure your database connection:

cp env.example .env
# Edit .env with your database details

Manual Installation

Create virtual environment:

python3 -m venv venv
source venv/bin/activate

Install dependencies:
```
pip install -r requirements.txt
```

Install ODBC Driver (macOS):

brew tap microsoft/mssql-release
brew install msodbcsql17 mssql-tools

Configuration

Create a .env file with your database configuration:

MSSQL_DRIVER={ODBC Driver 17 for SQL Server}
MSSQL_SERVER=your-server-address
MSSQL_DATABASE=your-database-name
MSSQL_USER=your-username
MSSQL_PASSWORD=your-password
MSSQL_PORT=1433
TrustServerCertificate=yes

Configuration Options

MSSQL_SERVER: Server hostname or IP address (required)
MSSQL_DATABASE: Database name to connect to (required)
MSSQL_USER: Username for authentication
MSSQL_PASSWORD: Password for authentication
MSSQL_PORT: Port number (default: 1433)
MSSQL_DRIVER: ODBC driver name (default: {ODBC Driver 17 for SQL Server})
TrustServerCertificate: Trust server certificate (default: yes)
Trusted_Connection: Use Windows authentication (default: no)

Usage

Understanding MCP Servers

MCP (Model Context Protocol) servers are designed to work with AI assistants and language models. They communicate via stdin/stdout using JSON-RPC protocol, not as traditional web services.

Running the Server

For AI Assistant Integration:

python3 src/server.py

The server will start and wait for MCP protocol messages on stdin. This is how AI assistants like Claude Desktop or other MCP clients will communicate with it.

For Testing and Development:

Test database connection:
```
python3 test_connection.py
```
Check server status:
```
./status.sh
```

View available tables:

# The server provides tools that can be called by MCP clients
# Direct testing requires an MCP client or testing framework

Available Tools (23 Total)

The enhanced server provides comprehensive database management tools:

Core Database Operations (5 tools)

read_query - Execute SELECT queries to read data
write_query - Execute INSERT, UPDATE, DELETE, and DDL queries
list_tables - List all tables in the database
describe_table - Get schema information for a specific table
create_table - Create new tables

Stored Procedure Management (6 tools)

create_procedure - Create new stored procedures
modify_procedure - Modify existing stored procedures
delete_procedure - Delete stored procedures
list_procedures - List all stored procedures with metadata
describe_procedure - Get complete procedure definitions
execute_procedure - Execute procedures with parameters
get_procedure_parameters - Get detailed parameter information

View Management (5 tools)

create_view - Create new views
modify_view - Modify existing views
delete_view - Delete views
list_views - List all views in the database
describe_view - Get view definitions and schema

Index Management (4 tools)

create_index - Create new indexes
delete_index - Delete indexes
list_indexes - List all indexes (optionally by table)
describe_index - Get detailed index information

Schema Exploration (2 tools)

list_schemas - List all schemas in the database
list_all_objects - List all database objects organized by schema

Available Resources

Both tables and views are exposed as MCP resources with URIs like:

mssql://table_name/data - Access table data in CSV format
mssql://view_name/data - Access view data in CSV format

Resources provide the first 100 rows of data in CSV format for quick data exploration.

Database Schema Traversal Examples

1. Explore Database Structure

# Start with schemas
list_schemas

# Get all objects in a specific schema
list_all_objects(schema_name: "dbo")

# Or get all objects across all schemas
list_all_objects()

2. Table Exploration

# List all tables
list_tables

# Get detailed table information
describe_table(table_name: "YourTableName")

# Access table data as MCP resource
# URI: mssql://YourTableName/data

3. View Management

# List all views
list_views

# Get view definition
describe_view(view_name: "YourViewName")

# Create a new view
create_view(view_script: "CREATE VIEW MyView AS SELECT * FROM MyTable WHERE Active = 1")

# Access view data as MCP resource
# URI: mssql://YourViewName/data

4. Stored Procedure Operations

# List all procedures
list_procedures

# Get complete procedure definition (handles large procedures like wmPostPurchase)
describe_procedure(procedure_name: "YourProcedureName")

# Save large procedures to file for analysis
write_file(file_path: "procedure_name.sql", content: "procedure_definition")

# Get parameter details
get_procedure_parameters(procedure_name: "YourProcedureName")

# Execute procedure
execute_procedure(procedure_name: "YourProcedureName", parameters: ["param1", "param2"])

5. Index Management

# List all indexes
list_indexes()

# List indexes for specific table
list_indexes(table_name: "YourTableName")

# Get index details
describe_index(index_name: "IX_YourIndex", table_name: "YourTableName")

# Create new index
create_index(index_script: "CREATE INDEX IX_NewIndex ON MyTable (Column1, Column2)")

Stored Procedure Management Examples

Create a Simple Stored Procedure

CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS TotalEmployees FROM Employees
END

Create a Stored Procedure with Parameters

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT,
    @MinSalary DECIMAL(10,2) = 0
AS
BEGIN
    SELECT 
        EmployeeId,
        FirstName,
        LastName,
        Salary,
        DepartmentId
    FROM Employees 
    WHERE DepartmentId = @DepartmentId 
    AND Salary >= @MinSalary
    ORDER BY LastName, FirstName
END

Create a Stored Procedure with Output Parameters

CREATE PROCEDURE GetDepartmentStats
    @DepartmentId INT,
    @EmployeeCount INT OUTPUT,
    @AverageSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT 
        @EmployeeCount = COUNT(*),
        @AverageSalary = AVG(Salary)
    FROM Employees 
    WHERE DepartmentId = @DepartmentId
END

Modify an Existing Stored Procedure

ALTER PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT,
    @MinSalary DECIMAL(10,2) = 0,
    @MaxSalary DECIMAL(10,2) = 999999.99
AS
BEGIN
    SELECT 
        EmployeeId,
        FirstName,
        LastName,
        Salary,
        DepartmentId,
        HireDate
    FROM Employees 
    WHERE DepartmentId = @DepartmentId 
    AND Salary BETWEEN @MinSalary AND @MaxSalary
    ORDER BY Salary DESC, LastName, FirstName
END

Large Content Handling

How It Works

The server efficiently handles large database objects like stored procedures:

Direct Retrieval: Fetches complete content directly from SQL Server
No Truncation: Returns full procedure definitions regardless of size
Chat Display: Large procedures can be viewed in full within the chat interface
Memory Efficient: Processes content through database connection streams

Usage Examples

# Describe a large procedure (gets complete definition)
describe_procedure(procedure_name: "wmPostPurchase")

# Works with procedures of any size (tested with 1400+ line procedures)
# Content is displayed in chat for viewing and copy-paste operations

Limitations for File Operations

⚠️ Important: While large procedures can be retrieved and displayed in chat, saving them to files via MCP tools is not reliable due to inference token limits. For bulk data extraction:

Small procedures: Copy-paste from chat interface
Large procedures: Use standalone Python scripts with direct database connections
Bulk operations: Create dedicated extraction scripts outside the MCP context

Integration with AI Assistants

Claude Desktop

Add this server to your Claude Desktop configuration:

{
  "mcpServers": {
    "mssql": {
      "command": "python3",
      "args": ["/path/to/mcp-sqlserver/src/server.py"],
      "cwd": "/path/to/mcp-sqlserver",
      "env": {
        "MSSQL_SERVER": "your-server",
        "MSSQL_DATABASE": "your-database",
        "MSSQL_USER": "your-username",
        "MSSQL_PASSWORD": "your-password"
      }
    }
  }
}

Other MCP Clients

The server follows the standard MCP protocol and should work with any compliant MCP client.

Development

Project Structure

mcp-sqlserver/
├── src/
│   └── server.py          # Main MCP server implementation with chunking system
├── tests/
│   └── test_server.py     # Unit tests
├── requirements.txt       # Python dependencies
├── .env                   # Database configuration (create from env.example)
├── env.example           # Configuration template
├── install.sh            # Installation script
├── start.sh              # Server startup script (for development)
├── stop.sh               # Server shutdown script
├── status.sh             # Server status script
└── README.md             # This file

Testing

Run the test suite:

python -m pytest tests/

Test database connection:

python3 test_connection.py

Logging

The server uses Python's logging module. Set the log level by modifying the logging.basicConfig() call in src/server.py.

Security Considerations

Authentication: Always use strong passwords and secure authentication
Network: Ensure your database server is properly secured
Permissions: Grant only necessary database permissions to the user account
SSL/TLS: Use encrypted connections when possible
Query Validation: The server validates query types and prevents unauthorized operations
DDL Operations: Create/modify/delete operations for database objects are properly validated
Stored Procedure Execution: Parameters are safely handled to prevent injection attacks
Large Content Handling: Large procedures are retrieved efficiently without truncation
File Operations: Write operations are validated and sandboxed
Read-First Approach: Exploration tools are read-only by default for production safety

Troubleshooting

Common Issues

Connection Failed: Check your database server address, credentials, and network connectivity
ODBC Driver Not Found: Install Microsoft ODBC Driver 17 for SQL Server
Permission Denied: Ensure the database user has appropriate permissions
Port Issues: Verify the correct port number and firewall settings
Large Content Issues: Large procedures display in chat but cannot be saved to files via MCP tools
Memory Issues: Large content is streamed efficiently from the database

Debug Mode

Enable debug logging by setting the log level to DEBUG in src/server.py:

logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

Large Content Troubleshooting

If you encounter issues with large content:

Copy-paste approach: Use chat interface to view and copy large procedures
External scripts: Create standalone Python scripts for bulk data extraction
Check memory: Large procedures are handled efficiently by the database connection
Verify permissions: Ensure database user can access procedure definitions
Test with smaller procedures: Verify basic functionality first

Getting Help

Check the server logs for detailed error messages
Verify your .env configuration
Test the database connection independently
Ensure all dependencies are installed correctly
For large content issues, use copy-paste from chat or create external extraction scripts

Recent Enhancements

Large Content Handling (Latest)

Verified complete retrieval of large stored procedures without truncation
Successfully tested with procedures like wmPostPurchase (1400+ lines, 57KB)
Large procedures display fully in chat interface for viewing and copy-paste
Efficient memory handling through database connection streaming
Note: File operations via MCP tools not reliable for large content due to token limits

Complete Database Object Management

Expanded from 5 to 23 comprehensive database management tools
Added full CRUD operations for all major database objects
Implemented schema traversal capabilities matching SSMS functionality
Added MCP resource access for tables and views
Enhanced security with proper operation validation

License

This project is open source. See the license file for details.

Contributing

Contributions are welcome! Please feel free to submit pull requests or open issues for bugs and feature requests.