SQL Server MCP Server

A comprehensive Model Context Protocol (MCP) server implementation for Microsoft SQL Server using .NET 9, providing both traditional stdio transport and modern HTTP streaming capabilities.

Features

• Dual Transport Support: Both stdio (traditional MCP) and HTTP streaming
• Comprehensive SQL Operations: Execute queries, manage tables, retrieve metadata
• Dapper Integration: High-performance SQL data access
• Server-Sent Events: Real-time streaming capabilities for HTTP clients
• Robust Error Handling: Detailed error messages and logging
• Cross-Platform: Runs on Windows, macOS, and Linux

Architecture

├── Controllers/
│   └── McpController.cs       # HTTP transport and JSON-RPC endpoints
├── Models/
│   └── DatabaseModels.cs      # Data models and DTOs
├── Services/
│   └── SqlServerService.cs    # Database operations using Dapper
├── Tools/
│   └── SqlServerTools.cs      # MCP tool implementations
└── Program.cs                 # Dual-mode server configuration

Quick Start

Prerequisites

• .NET 9 SDK or later
• SQL Server instance (local or remote)
• Connection string to your database

Installation

1. Clone the repository:

   git clone <repository-url>
   cd mcp-mssql/SqlServerMcpServer

2. Build the project:

   dotnet build

3. Run in stdio mode (traditional MCP):

   dotnet run

4. Run in HTTP mode:

   dotnet run -- --http

Usage

Stdio Mode (Traditional MCP)

For use with MCP clients that expect stdio transport:

dotnet run

The server will communicate via stdin/stdout using the MCP protocol.

HTTP Mode

For HTTP clients and Server-Sent Events:

dotnet run -- --http

Server will start on http://localhost:5000 (or configured port).

HTTP Endpoints

• GET / - Server information and available tools
• POST /mcp - JSON-RPC 2.0 MCP calls
• GET /mcp/sse - Server-Sent Events streaming
• GET /health - Health check endpoint

Available Tools

1. ListTables

Lists all tables in the SQL Server database.

Parameters:

• connectionString (required): SQL Server connection string
• schema (optional): Schema name to filter tables

Example:

{
  "jsonrpc": "2.0",
  "id": "1",
  "method": "tools/call",
  "params": {
    "name": "ListTables",
    "arguments": {
      "connectionString": "Server=localhost;Database=MyDB;Integrated Security=true;",
      "schema": "dbo"
    }
  }
}

2. ReadData

Executes SQL queries to read data.

Parameters:

• connectionString (required): SQL Server connection string
• sql (required): SQL SELECT query

Example:

{
  "jsonrpc": "2.0",
  "id": "2", 
  "method": "tools/call",
  "params": {
    "name": "ReadData",
    "arguments": {
      "connectionString": "Server=localhost;Database=MyDB;Integrated Security=true;",
      "sql": "SELECT TOP 10 * FROM Users WHERE Active = 1"
    }
  }
}

3. InsertData

Executes INSERT statements.

Parameters:

• connectionString (required): SQL Server connection string
• sql (required): INSERT SQL statement

4. UpdateData

Executes UPDATE statements.

Parameters:

• connectionString (required): SQL Server connection string
• sql (required): UPDATE SQL statement

5. DescribeTable

Returns detailed table schema information.

Parameters:

• connectionString (required): SQL Server connection string
• name (required): Table name
• schema (optional): Schema name (defaults to "dbo")

6. CreateTable

Creates new tables using CREATE TABLE statements.

Parameters:

• connectionString (required): SQL Server connection string
• sql (required): CREATE TABLE SQL statement

7. DropTable

Drops tables using DROP TABLE statements.

Parameters:

• connectionString (required): SQL Server connection string
• sql (required): DROP TABLE SQL statement

8. TestConnection

Tests database connectivity.

Parameters:

• connectionString (required): SQL Server connection string

9. GetDatabaseMetadata

Retrieves comprehensive database metadata including tables, views, and stored procedures.

Parameters:

• connectionString (required): SQL Server connection string

Configuration

Connection Strings

The server accepts standard SQL Server connection strings:

// Windows Authentication
"Server=localhost;Database=MyDatabase;Integrated Security=true;"

// SQL Server Authentication  
"Server=localhost;Database=MyDatabase;User Id=sa;Password=YourPassword;"

// Azure SQL Database
"Server=tcp:myserver.database.windows.net,1433;Database=MyDatabase;User Id=myuser;Password=mypassword;Encrypt=True;"

Environment Variables

• MCP_HTTP_MODE=true: Force HTTP mode
• ASPNETCORE_URLS: Configure HTTP binding (default: http://localhost:5000)
• ASPNETCORE_ENVIRONMENT: Set to "Development" for detailed logging

API Examples

Using curl with HTTP transport

1. List available tools:

   curl -X POST http://localhost:5000/mcp \
     -H "Content-Type: application/json" \
     -d '{
       "jsonrpc": "2.0",
       "id": "1", 
       "method": "tools/list",
       "params": {}
     }'

2. Execute a query:

   curl -X POST http://localhost:5000/mcp \
     -H "Content-Type: application/json" \
     -d '{
       "jsonrpc": "2.0",
       "id": "2",
       "method": "tools/call", 
       "params": {
         "name": "ReadData",
         "arguments": {
           "connectionString": "Server=localhost;Database=master;Integrated Security=true;",
           "sql": "SELECT name FROM sys.databases"
         }
       }
     }'

Server-Sent Events

Connect to the SSE endpoint for real-time updates:

curl -N http://localhost:5000/mcp/sse

Error Handling

The server provides comprehensive error handling with detailed messages:

{
  "jsonrpc": "2.0",
  "id": "1",
  "error": {
    "code": -32000,
    "message": "Connection failed: A network-related or instance-specific error occurred...",
    "data": {
      "code": "connection_error"
    }
  }
}

Security Considerations

• Connection Strings: Never log connection strings containing credentials
• SQL Injection: All queries use parameterized statements via Dapper
• Network Security: Use encrypted connections for production databases
• Authentication: Consider implementing API key authentication for HTTP mode

Development

Building from Source

git clone <repository-url>
cd mcp-mssql/SqlServerMcpServer
dotnet restore
dotnet build

Running Tests

dotnet test

Adding New Tools

1. Add methods to SqlServerTools.cs with [McpServerTool] attribute
2. Implement the corresponding service methods in SqlServerService.cs
3. Add HTTP endpoint handling in McpController.cs

Troubleshooting

Common Issues

1. Connection Failures: Verify SQL Server is running and accessible
2. Authentication Issues: Check Windows Authentication vs SQL Server Authentication
3. Firewall: Ensure SQL Server port (default 1433) is open
4. Stdio Mode: Ensure stdout is not being captured by other processes

Logging

The server logs to stderr for MCP compatibility. Set log level in appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "SqlServerMcpServer": "Debug"
    }
  }
}

Contributing

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

License

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

Support

For issues and questions:

• Create an issue in the GitHub repository
• Check the troubleshooting section above
• Review SQL Server connection documentation