SQL Server MCP Server (Diamond Inventory)

Production-ready Model Context Protocol 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)

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

Option B — comma-separated in .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 (recommended) or pip
3. Microsoft ODBC Driver 18 for SQL Server
   • Download for Windows

On your Ubuntu VM (SQL Server host)

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

# 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):

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

Quick start

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):

{
  "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):

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

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:

$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

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