# 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