jdlemes/MsSqlMCP
3.3
If you are the rightful owner of MsSqlMCP and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to dayong@mcphub.com.
MsSqlMCP is a Model Context Protocol server designed to query SQL Server database schema, including tables, columns, and relationships.
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
- Check logs in
C:\Services\MsSqlMCP\logs\ - Verify connection string in
appsettings.json - Ensure SQL Server is accessible from the service account
- Run manually to see errors:
C:\Services\MsSqlMCP\MsSqlMCP.exe --http-only
Connection issues
- Verify SQL Server is running
- Check firewall rules for SQL Server port (1433)
- 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
