MsSqlMCP

MsSqlMCP

MCP Server for SQL Server database schema inspection and read-only query execution.

Features

• Read-only access: All queries are validated to prevent data modification (INSERT, UPDATE, DELETE, DROP, EXEC, etc. are blocked)
• Schema discovery: Tables, columns, relationships, and stored procedures
• SQL execution: Safe SELECT queries only
• Dual transport: Supports both stdio and HTTP/SSE protocols
• Windows Service: Can run as a Windows Service for production deployments
• MCP Protocol: Compatible with VS Code Copilot, Claude Desktop, and other MCP clients

Prerequisites

• .NET 10 (or .NET 9 with minor adjustments)
• SQL Server

Architecture

The project follows SOLID principles with dependency injection:

MsSqlMCP/
├── Program.cs                    # Entry point with DI and dual transport
├── SchemaTool.cs                 # MCP tool definitions
├── Interfaces/
│   ├── IConnectionFactory.cs     # SQL connection abstraction
│   ├── IQueryExecutor.cs         # Query execution abstraction
│   ├── ISchemaRepository.cs      # Schema queries abstraction
│   └── ISqlQueryValidator.cs     # Query validation abstraction
├── Services/
│   ├── SqlConnectionFactory.cs   # Connection management
│   ├── SafeQueryExecutor.cs      # Validated query execution
│   ├── SchemaRepository.cs       # Schema query implementation
│   └── ReadOnlySqlQueryValidator.cs # Security validation (27 blocked keywords)
└── Tests/
    └── ReadOnlySqlQueryValidatorTests.cs # 42 security tests

Configuration

Connection String

Edit appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(local);Initial Catalog=YourDatabase;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Urls": "http://localhost:5000"
}

Running

Console Mode (Development)

# Run with both stdio and HTTP transports
dotnet run

# Run with HTTP transport only (for debugging)
dotnet run -- --http-only

Run Tests

dotnet test --filter "FullyQualifiedName~Tests"

MCP Client Configuration

Option 1: stdio Transport (VS Code)

Add to your VS Code settings.json:

{
  "mcp": {
    "servers": {
      "MsSqlMCP": {
        "type": "stdio",
        "command": "dotnet",
        "args": ["run", "--project", "c:\\path\\to\\MsSqlMCP.csproj"]
      }
    }
  }
}

Option 2: HTTP Transport (VS Code)

First, start the server:

dotnet run -- --http-only

Then add to your VS Code settings.json:

{
  "mcp": {
    "servers": {
      "MsSqlMCP": {
        "type": "http",
        "url": "http://localhost:5000/sse",
        "autoApprove": [
          "get_tables",
          "get_columns", 
          "get_relationships",
          "execute_sql",
          "get_store_procedure"
        ]
      }
    }
  }
}

Option 3: Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "MsSqlMCP": {
      "command": "dotnet",
      "args": ["run", "--project", "c:\\path\\to\\MsSqlMCP.csproj"]
    }
  }
}

Available Tools

Tool	Description	Required Parameters
GetTables	Get all table names in the database	None
GetColumns	Get columns (fields) for a specific table	tableName
GetRelationships	Get foreign key relationships between tables	None
GetStoreProcedure	Get stored procedure definition	spName
ExecuteSql	Execute a read-only SELECT query	sqlQuery

All tools accept an optional databaseName parameter to query different databases in the same SQL Server instance.

Security

The ExecuteSql tool only allows SELECT queries. The following statements are blocked:

• DML: INSERT, UPDATE, DELETE, MERGE, TRUNCATE
• DDL: CREATE, ALTER, DROP
• DCL: GRANT, REVOKE, DENY
• Execution: EXEC, EXECUTE, SP_EXECUTESQL, XP_
• Others: BACKUP, RESTORE, BULK, OPENROWSET, OPENQUERY, OPENDATASOURCE

Windows Service Installation

1. Publish the Application

On your development machine:

cd c:\path\to\MsSqlMCP
dotnet publish -c Release -r win-x64 --self-contained true

This creates files in: bin\Release\net10.0\win-x64\publish\

2. Copy to Server

Copy the contents of the publish folder to the server:

Source: bin\Release\net10.0\win-x64\publish\*
Destination: C:\Services\MsSqlMCP\

3. Configure on Server

Edit C:\Services\MsSqlMCP\appsettings.json with your SQL Server connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=YOUR_SQL_SERVER;Initial Catalog=YOUR_DATABASE;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Urls": "http://localhost:5000"
}

4. Install the Service

Open PowerShell as Administrator and run:

# Create the Windows Service
sc.exe create MsSqlMCP binPath= "C:\Services\MsSqlMCP\MsSqlMCP.exe --http-only" start= auto DisplayName= "MsSql MCP Server"

# Add description
sc.exe description MsSqlMCP "Model Context Protocol server for SQL Server database inspection"

# Create logs directory
mkdir C:\Services\MsSqlMCP\logs -Force

# Start the service
net start MsSqlMCP

# Verify status
sc.exe query MsSqlMCP

5. Verify Installation

# Check service status
Get-Service -Name MsSqlMCP

# Test the endpoint
Invoke-RestMethod -Uri "http://localhost:5000/sse/tools"

Service Management Commands

# Stop service
net stop MsSqlMCP

# Start service
net start MsSqlMCP

# Restart service
net stop MsSqlMCP; net start MsSqlMCP

# Uninstall service
net stop MsSqlMCP
sc.exe delete MsSqlMCP

Firewall Configuration (if accessing remotely)

# Allow inbound traffic on port 5000
New-NetFirewallRule -DisplayName "MsSqlMCP" -Direction Inbound -Port 5000 -Protocol TCP -Action Allow

HTTP API Endpoints

When running in HTTP mode, the following endpoints are available:

Endpoint	Method	Description
/sse	GET	SSE stream for MCP protocol
/sse/tools	GET	List all available tools
/sse/invoke	POST	Invoke a tool

Example: Invoke Tool via HTTP

curl -X POST http://localhost:5000/sse/invoke \
  -H "Content-Type: application/json" \
  -d '{"Tool": "GetTables", "Params": {}}'

Invoke-RestMethod -Uri "http://localhost:5000/sse/invoke" -Method POST -ContentType "application/json" -Body '{"Tool": "GetTables", "Params": {}}'

Troubleshooting

Service won't start

1. Check logs in C:\Services\MsSqlMCP\logs\
2. Verify connection string in appsettings.json
3. Ensure SQL Server is accessible from the service account
4. Run manually to see errors: C:\Services\MsSqlMCP\MsSqlMCP.exe --http-only

Connection issues

1. Verify SQL Server is running
2. Check firewall rules for SQL Server port (1433)
3. If using Windows Authentication, ensure the service account has database access

Port already in use

Change the port in appsettings.json:

{
  "Urls": "http://localhost:5001"
}

License

MIT