Content
# 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`:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=(local);Initial Catalog=YourDatabase;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Urls": "http://localhost:5000"
}
```
## Running
### Console Mode (Development)
```bash
# Run with both stdio and HTTP transports
dotnet run
# Run with HTTP transport only (for debugging)
dotnet run -- --http-only
```
### Run Tests
```bash
dotnet test --filter "FullyQualifiedName~Tests"
```
## MCP Client Configuration
### Option 1: stdio Transport (VS Code)
Add to your VS Code `settings.json`:
```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:
```bash
dotnet run -- --http-only
```
Then add to your VS Code `settings.json`:
```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`:
```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:
```bash
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:
```json
{
"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:
```powershell
# 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
```powershell
# Check service status
Get-Service -Name MsSqlMCP
# Test the endpoint
Invoke-RestMethod -Uri "http://localhost:5000/sse/tools"
```
### Service Management Commands
```powershell
# 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)
```powershell
# 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
```bash
curl -X POST http://localhost:5000/sse/invoke \
-H "Content-Type: application/json" \
-d '{"Tool": "GetTables", "Params": {}}'
```
```powershell
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`:
```json
{
"Urls": "http://localhost:5001"
}
```
## License
MIT
Connection Info
You Might Also Like
markitdown
MarkItDown-MCP is a lightweight server for converting URIs to Markdown.
markitdown
Python tool for converting files and office documents to Markdown.
Filesystem
Node.js MCP Server for filesystem operations with dynamic access control.
TrendRadar
TrendRadar: Your hotspot assistant for real news in just 30 seconds.
mempalace
The highest-scoring AI memory system ever benchmarked. And it's free.
mempalace
The highest-scoring AI memory system ever benchmarked. And it's free.