mcp-sqlserv

MCP Server for read-only access to SQL Server databases — anti SQL-injection by construction, managed via the Web Admin UI.

Zero raw SQL · Default deny · Bind parameter 100% · Full audit

About

mcp-sqlserv lets AI agents (Claude, Cursor, Claude Code, any MCP client) read SQL Server databases safely and in a controlled way:

• All queries are built structurally by the server — the AI never writes raw SQL.

• Identifiers (tables/columns) are validated against real database metadata (sys.tables, sys.columns).

• Values are always bound parameters → SQL injection is impossible by construction.

• Per-table permissions are default deny: without explicit permission, a table cannot be touched.

• Every request is recorded in the audit log — key, tool, filter, row count, and duration.

Related MCP server: SQL Server MCP

Features

Architecture

┌──────────────┐   HTTPS    ┌─────────────┐          ┌──────────────────────────────┐
│  AI Agent    ├───────────►│    nginx    ├─────────►│  mcp-sqlserv (Docker)        │
│  (MCP client)│  Bearer    │  reverse    │ app-net  │  Express + MCP + OAuth       │
└──────────────┘  token     │  proxy+SSL  │  work    │      │            │          │
                            └─────────────┘          │      ▼            ▼          │
┌──────────────┐   HTTPS                              │  SQLite         mssql pool   │
│ Web Admin UI ├─────────────────────────────────────►│  (data/, keys,   │           │
│  (browser)   │            REST /api/*               │   audit, izin)   ▼           │
└──────────────┘                                      │              ┌──────────┐    │
                                                      │              │ SQL Srvr │    │
                                                      └──────────────┴──────────┴────┘

Quick To Start

# 1. Clone & siapkan environment
git clone https://github.com/<username>/mcp-sqlserv.git
cd mcp-sqlserv
cp .env.example .env            # isi ADMIN_USER / ADMIN_PASSWORD (min 8 karakter)

# 2. Build & jalankan
docker compose up -d --build

# 3. Verifikasi
curl http://localhost:4000/healthz

Server runs on http://localhost:4000 — Web admin UI at /, MCP endpoint at /mcp.

Environment Variables

Usage Flow

1. Log in to the Web UI → DB Connection menu → fill in host/port/user/pass/database, safest option — Test Connection.

   For Docker cases, SQL Server on the host can be reached via host.docker.internal.

2. API Keys menu → create a key (view once, store it!).

3. Table Permissions menu → check the maritime identifier table allowed for AI → Save Permissions. Default deny.

4. Connect the Agent to URL https://<domain>/mcp with a header Authorization: Bearer <api-key>.

Connecting an Arbitrary MCP Client

{
  "mcpServers": {
    "sql-server": {
      "url": "https://<domain>/mcp",
      "headers": { "Authorization": "Bearer sk-xxxx" }
    }
  }
}

Quick test with curl:

curl -X POST https://<domain>/mcp \
  -H "Authorization: Bearer sk-xxxx" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-03-26","capabilities":{},"clientInfo":{"name":"curl","version":"1.0"}}}'

Claude Custom Connector (claude.ai / Desktop)

1. Open Customize → Connectors → Add custom connector.

2. Set Remote MCP server URL: https://<domain>/mcp.

3. In Advanced settings, fill in an OAuth Client ID + Secret from the OAuth Clients menu (redirect URI: https://claude.ai/api/mcp/auth_callback).

   Could be left blank — Claude auto registers via Dynamic Client Registration (RFC 7591).

4. Click Add → Connect — browser opens the administrator login page → Allow access.

5. Claude stores a refresh token and sends MCP requests with a bearer token.

Claude Code (CLI):

claude mcp add mcp-sqlserv https://<domain>/mcp --transport http \
  ... # bila client pre-registered: --client-id <id> --client-secret --callback-port

Endpoints OAuth

OAuth identity = operator session. Access token maps mapped to an internal API key oauth:<client_id>` — all table permissions, rate limit, and audit logging also apply to Claude connection. Revoking the client invalidates this client’s tokens instantly.

MCP Tools

Table names must be specified without schema prefix (users, not dbo.users). Columns are validated against sys.columns; all values are 100% bound parameters.

Supported structured filters: eq, neq, lt, lte, gt, gte, like, startsWith, endsWith, in, between, isNull, isNotNull.

Security

• No raw SQL — only the structured query builder

• Identifier allowlist — regex + check against real DB metadata

• Default deny — without grant, a table cannot be touched

• Hard limits — max 1000 rows/query, 20 filters, 50 INS · time out 30s

• API key + rate limiter per key + audit log of all requests

• Read-only (recommend through SQL Server-only GRANT SELECT)

• DB password stored encrypted AES-256-GCM in SQLite

Deployment

Deploy with Docker to the app-network, behind nginx, as a reverse proxy (wildcard SSL cert, SSE-buffering disabled, CORS for web‑based MCP clients).

Migrating Between VPS

Code and Docker will run automatically, but the following — not track in Git (they’re in .gitignore) — have to be carried manually:

# Di VPS baru
git clone https://github.com/<username>/mcp-sqlserv.git && cd mcp-sqlserv

# Migrasi state dari VPS lama (opsional)
rsync -av vps-lama:/path/mcp-sqlserv/.env .env
rsync -av vps-lama:/path/mcp-sqlserv/data ./data

# Network eksternal harus ada dulu (dipakai docker-compose.yaml)
docker network create app-network   # abaikan jika sudah ada

docker compose up -d --build

Leaving data/ behind means the server still runs — you only have to recreate the DB connection, API keys, grant list from admin UI.

Project Structure

mcp-sqlserv/
├── src/
│   ├── index.ts            # Bootstrap Express + routing
│   ├── config.ts           # Env config
│   ├── db/storage.ts       # SQLite: api_keys, db_config, permissions, audit_log
│   ├── sqlserver/          # Connection pool, metadata (sys.tables), query builder
│   ├── mcp/                # MCP server (per-session) + tools
│   ├── oauth/              # OAuth 2.1: router, PKCE, discovery
│   ├── api/                # REST admin (auth, config, keys, permissions, audit)
│   └── ui/                 # SPA vanilla JS (public/)
├── public/                 # Web UI admin (tanpa build step)
├── test/                   # Test suite keamanan + OAuth + smoke
├── Dockerfile              # Multi-stage build (node:20-alpine)
├── docker-compose.yaml     # Attach ke app-network, host.docker.internal
└── LICENSE                 # MIT

Admin REST API

Testing

npm run test:smoke      # smoke test dasar
npm run test:security   # 29 test: injection, permission, limit, pagination, auth
npm run test:oauth      # 46 test: discovery, DCR, PKCE, consent, token, refresh, revoke

test/oauth.mjs starts its own server (port: 4100, data-dir: oauth-test-data/) — no further config required.

Contribution

Contributions are welcome ! Open an issue or a pull request. For larger changes, please discuss the idea first via Issue, to stay aligned with the product principle: Security is the product. Every surface (MCP, UI, Agent Test) must meet the same standards:

License

This project is licensed under the [MIT License