# SQL Server MCP Server (Diamond Inventory)

Production-ready [Model Context Protocol](https://modelcontextprotocol.io) server for Microsoft SQL Server, preconfigured for the **Diamond Inventory** database on your Ubuntu VM.

Connect from **Cursor**, Claude Desktop, or any MCP client to explore schema and run safe read-only queries.

## Features

- **Multiple databases** on one SQL Server instance (pools per database)
- **Connection pooling** with startup health check per database
- **Read-only by default** — blocks DDL, `EXEC`, and writes unless `MSSQL_ALLOW_WRITE=true`
- **Row limits** — auto-applies `TOP (n)` on `SELECT` when missing
- **Parameterized queries** — `?` placeholders via pyodbc
- **Schema tools** — list databases/schemas/tables, describe columns, search objects, column null counts
- **Production MCP prompts** — explore, performance audit, data quality, packet analysis, health check
- **stdio transport** for Cursor; optional **Streamable HTTP** for remote use

## Multiple databases (one server)

Configure several databases that share the same host, port, and login.

**Option A — `databases.json` (recommended)**

```powershell
copy databases.json.example databases.json
# Edit names/descriptions; set default_database
```

**Option B — comma-separated in `.env`**

```env
MSSQL_DATABASE=SJSINGLE
MSSQL_DATABASES=SJSINGLE,SJWEB,WEBCRM
```

Every tool accepts an optional `database` argument. Call `list_databases` first.

| Setting | Purpose |
|---------|---------|
| `MSSQL_DATABASES` | Comma-separated DB names |
| `databases.json` | Names, descriptions, per-DB `allow_write`, tags |
| `MSSQL_ALLOW_ANY_DATABASE` | `false` = only catalog DBs; `true` = any DB on server |

## MCP prompts (better AI results)

Use **Prompts** in Cursor MCP panel:

| Prompt | Use when |
|--------|----------|
| `multi_database_overview_prompt` | Start — which DB to use |
| `explore_database_prompt` | Discover schemas/tables |
| `analyze_table_prompt` | Deep-dive one table |
| `packet_master_analysis_prompt` | Diamond `Single.PACKET_MASTER` |
| `performance_audit_prompt` | Slow procedures / indexes |
| `data_quality_audit_prompt` | Nulls, duplicates, bad values |
| `safe_adhoc_query_prompt` | Answer a business question safely |
| `compare_databases_prompt` | Diff two databases |
| `production_healthcheck_prompt` | Size, backups, sessions |
| `stored_procedure_review_prompt` | Top slow procedures |

## Prerequisites

### On your Windows machine (MCP client)

1. **Python 3.11+**
2. **[uv](https://docs.astral.sh/uv/)** (recommended) or pip
3. **Microsoft ODBC Driver 18 for SQL Server**  
   - [Download for Windows](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server)

### On your Ubuntu VM (SQL Server host)

Ensure SQL Server accepts remote TCP connections on port **1433** and that firewall allows your client IP.

```bash
# Example: allow port (adjust for your setup)
sudo ufw allow 1433/tcp
```

Create / verify the Diamond Inventory database and a SQL login for MCP (avoid `sa` in production):

```sql
CREATE DATABASE DiamondInventory;
GO
USE DiamondInventory;
-- CREATE USER mcp_reader WITH PASSWORD = '...';
-- GRANT SELECT ON SCHEMA::dbo TO mcp_reader;
```

## Quick start

```powershell
cd D:\DEVOPS_PROJECTS\MCPSERVER

# Copy and edit credentials
copy .env.example .env

# Install and run
uv sync
uv run sqlserver-mcp
```

### Configure `.env`

| Variable | Description |
|----------|-------------|
| `MSSQL_SERVER` | Ubuntu VM IP or hostname |
| `MSSQL_PORT` | Default `1433` |
| `MSSQL_DATABASE` | `DiamondInventory` |
| `MSSQL_USER` / `MSSQL_PASSWORD` | SQL login |
| `MSSQL_ALLOW_WRITE` | `false` (recommended) |
| `MSSQL_MAX_ROWS` | Max rows per query (default `1000`) |

## Cursor integration

Add to **Cursor Settings → MCP** (or merge into `.cursor/mcp.json`):

```json
{
  "mcpServers": {
    "sqlserver-diamond-inventory": {
      "command": "uv",
      "args": [
        "run",
        "--directory",
        "D:\\DEVOPS_PROJECTS\\MCPSERVER",
        "sqlserver-mcp"
      ],
      "env": {
        "MSSQL_SERVER": "192.168.1.100",
        "MSSQL_PORT": "1433",
        "MSSQL_DATABASE": "DiamondInventory",
        "MSSQL_USER": "mcp_reader",
        "MSSQL_PASSWORD": "your-password",
        "MSSQL_DRIVER": "ODBC Driver 18 for SQL Server",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "true",
        "MSSQL_ALLOW_WRITE": "false"
      }
    }
  }
}
```

Restart Cursor. You should see tools: `list_schemas`, `list_tables`, `describe_table`, `execute_query`, `execute_parameterized_query`, `get_database_info`.

## MCP tools

| Tool | Purpose |
|------|---------|
| `list_schemas` | All schemas in the database |
| `list_tables` | Tables/views (optional `schema` filter) |
| `describe_table` | Column metadata + primary keys |
| `execute_query` | Read-only T-SQL (`SELECT` / `WITH`) |
| `execute_parameterized_query` | Read queries with `?` parameters |
| `get_database_info` | Current DB, server name, config summary |

## Example prompts in Cursor

- *"List all tables in the Diamond Inventory database."*
- *"Describe the Products table and show 5 sample rows."*
- *"How many items are low on stock?"* (agent will use `execute_query`)

## REST API for Node.js (and other apps)

Run a **simple HTTP API** on port **8766** (separate from Cursor MCP stdio):

```powershell
uv sync
uv run sqlserver-mcp-api
```

API base URL: `http://127.0.0.1:8766`

| Endpoint | Method | Body |
|----------|--------|------|
| `/health` | GET | — |
| `/api/v1/run` | POST | `{ "text": "SELECT TOP 5 ...", "database": "SJSINGLE" }` |
| `/api/v1/query` | POST | `{ "sql": "SELECT ...", "database": "SJSINGLE" }` |
| `/api/v1/invoke` | POST | `{ "tool": "list_tables", "arguments": { "schema": "Single" } }` |
| `/api/v1/databases` | GET | — |

Optional: set `MCP_API_KEY` in `.env` and send header `X-API-Key`.

### Node.js example

```javascript
const API = "http://127.0.0.1:8766";
const headers = {
  "Content-Type": "application/json",
  // "X-API-Key": "your-secret-key",  // if MCP_API_KEY is set
};

// Send text (SQL) from your app — you handle the JSON response
const res = await fetch(`${API}/api/v1/run`, {
  method: "POST",
  headers,
  body: JSON.stringify({
    text: "SELECT TOP 10 LOT_CODE, CARAT FROM Single.PACKET_MASTER",
    database: "SJSINGLE",
  }),
});
const data = await res.json();
if (data.ok) {
  console.log(data.result.rows);       // array of rows
  console.log(data.result.columns);    // column names
} else {
  console.error(data.error);
}

// Or call a specific tool
const tables = await fetch(`${API}/api/v1/invoke`, {
  method: "POST",
  headers,
  body: JSON.stringify({
    tool: "list_tables",
    arguments: { database: "SJSINGLE", schema: "Single" },
  }),
}).then((r) => r.json());
```

**Note:** This API runs SQL and schema tools only. Free-form natural language (e.g. “how many stones?”) needs your Node app to turn text into SQL, or use an LLM in Node, then call `/api/v1/run` or `/api/v1/query`.

## HTTP transport (optional)

For non-stdio clients:

```powershell
$env:MCP_TRANSPORT = "streamable-http"
$env:MCP_HTTP_PORT = "8765"
uv run sqlserver-mcp
```

Endpoint: `http://127.0.0.1:8765/mcp` (bind to localhost only unless behind a reverse proxy with auth).

## Security notes

- Never commit `.env` or passwords.
- Use a **read-only** SQL user for MCP; keep `MSSQL_ALLOW_WRITE=false`.
- Blocked always: `DROP`, `ALTER`, `CREATE`, `EXEC`, `TRUNCATE`, `BACKUP`, etc.
- Queries are validated before execution; results are capped by `MSSQL_MAX_ROWS`.

## Development

```powershell
uv sync --extra dev
uv run pytest
uv run mcp dev src/sqlserver_mcp/server.py
```

## Troubleshooting

| Issue | Fix |
|-------|-----|
| `Data source name not found` | Install ODBC Driver 18; set `MSSQL_DRIVER` exactly |
| `Login failed` | Check user/password; enable SQL auth on SQL Server |
| `Connection timeout` | Open port 1433; verify VM IP; SQL Server listening on TCP |
| `SSL Provider` errors | Set `MSSQL_TRUST_SERVER_CERTIFICATE=true` for self-signed certs |

## License

MIT