🛡️ mssql-readonly-mcp

A Model Context Protocol (MCP) server that safely connects AI assistants (Claude, Cursor, etc.) to your Microsoft SQL Server — with an iron-clad read-only guarantee.

No matter how an AI is prompted, this server will never run an INSERT, UPDATE, DELETE, or any DDL statement. Safety is enforced at two independent layers:

1. Database level — The SQL login used has only db_datareader permissions.
2. Application level — A built-in query validator blocks any write operations before they ever reach the database.

---

📋 Table of Contents

• How It Works
• Prerequisites
• Quick Start
• Configuration Reference
• Step 1 — Create a Read-Only SQL Login
• Step 2 — Enable TCP/IP (if needed)
• Step 3 — Install & Run
• Connecting AI Clients
• Running over HTTP (Multi-Client Mode)
• Development & Testing
• Publishing to npm

---

💡 How It Works

  Your AI Tool              This MCP Server             SQL Server
  (Claude/Cursor)  ──────►  mssql-readonly-mcp  ──────►  (Read-Only Login)
                            │
                            ├─ Validates query (blocks writes)
                            ├─ Enforces row cap (default: 1000 rows)
                            └─ Enforces query timeout (default: 30s)

The AI client sends natural-language requests → the MCP server translates them into safe SQL queries → results are returned to the AI. No data is ever modified.

---

✅ Prerequisites

Before you begin, make sure you have the following installed:

Requirement	Minimum Version	Notes
Node.js	v18+	Download here
npm	Comes with Node.js	Used to install and run the server
SQL Server	Any version	Must be running and network-accessible

You'll also need administrative access to your SQL Server instance — just once — to create the read-only login in Step 1.

---

⚡ Quick Start

Here's the complete setup at a glance. Each step is explained in detail below.

# 1. Clone and install dependencies
git clone https://github.com/kushgit9842/MCP_MS_SQL.git
cd MCP_MS_SQL
npm install
npm run build

# 2. Copy the example environment file
cp .env.example .env
# → Open .env and fill in your SQL Server connection details

# 3. Create the read-only SQL login (run in SQL Server Management Studio)
# → See "Step 1" below for the SQL script

# 4. Start the MCP server
npm start

---

⚙️ Configuration Reference

Copy .env.example to .env and fill in your values:

cp .env.example .env

SQL Server Connection

Variable	Required	Default	Description
MSSQL_SERVER	✅ Yes	—	Your server hostname. E.g. localhost, localhost\SQLEXPRESS, or myserver.com,1433
MSSQL_PORT	No	1433	The TCP port SQL Server listens on
MSSQL_DATABASE	✅ Yes	—	The default database to connect to
MSSQL_USER	✅ Yes	—	The read-only SQL login you create in Step 1
MSSQL_PASSWORD	✅ Yes	—	Password for the read-only login. Never use sa
MSSQL_AUTH	No	sql	Authentication type. Only sql is supported currently
MSSQL_ENCRYPT	No	true	Whether to encrypt the connection (recommended)
MSSQL_TRUST_SERVER_CERT	No	true	Set to true for local/dev servers with self-signed certificates

Safety & Performance

Variable	Default	Description
MAX_ROWS	1000	Maximum rows returned per query. Prevents accidentally dumping huge tables
QUERY_TIMEOUT_MS	30000	How long (in milliseconds) before a query is cancelled. 30000 = 30 seconds

Transport (STDIO vs HTTP)

Variable	Default	Description
MCP_TRANSPORT	stdio	How the server communicates. Use stdio for local AI clients, http for remote/multi-client setups
MCP_HTTP_PORT	3000	Port to listen on when using HTTP transport
MCP_HTTP_API_KEY	(unset)	Optional API key to restrict access to the HTTP endpoint

---

🔐 Step 1 — Create a Read-Only SQL Login

This is a one-time setup step. Connect to your SQL Server as an admin (using SQL Server Management Studio, Azure Data Studio, or sqlcmd) and run the following script:

-- Step 1: Create the login at the server level
CREATE LOGIN mcp_readonly WITH PASSWORD = '<choose a strong password>';
GO

-- Step 2: Create the user in your target database
USE <YourDatabaseName>;  -- ← Replace with your actual database name
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;

-- Step 3: Grant read-only access to all tables and views
ALTER ROLE db_datareader ADD MEMBER mcp_readonly;

-- Step 4: Allow reading object definitions (stored procedures, views, etc.)
GRANT VIEW DEFINITION TO mcp_readonly;
GO

-- Step 5: Allow reading server-level performance stats (optional but recommended)
USE master;
GRANT VIEW SERVER STATE TO mcp_readonly;
GO

What these permissions allow

Permission	What it does
db_datareader	Read all tables and views in the database
VIEW DEFINITION	Read the source code of stored procedures, views, and functions
VIEW SERVER STATE	Read server performance stats (index usage, wait stats, active queries)

🔒 Security Note: This login intentionally does NOT have db_datawriter, db_ddladmin, db_owner, or sysadmin. Even if the application validator were somehow bypassed, the SQL login itself cannot write any data.

After running this, use mcp_readonly and your chosen password as MSSQL_USER / MSSQL_PASSWORD in your .env file.

---

🌐 Step 2 — Enable TCP/IP (if needed)

If you get a "could not open a connection" error, SQL Server's TCP/IP protocol might be disabled. This is common on default Developer or Express installs.

To fix it:

1. Open SQL Server Configuration Manager (search for it in the Start menu).
2. In the left panel, expand SQL Server Network Configuration.
3. Click Protocols for <YourInstanceName>.
4. Right-click TCP/IP → click Enable.
5. Restart the SQL Server service (you can do this from the same tool under SQL Server Services).

After restarting, try connecting again.

---

🚀 Step 3 — Install & Run

Option A — From this repo (recommended for development)

npm install       # Install dependencies
npm run build     # Compile TypeScript to JavaScript
npm start         # Start the MCP server

Option B — Without cloning (once published to npm)

npx mssql-readonly-mcp

Verify it's working

Use the MCP Inspector to test the server interactively in your browser:

npx @modelcontextprotocol/inspector npm start

This opens a visual interface where you can send test queries and see responses in real time.

---

🤖 Connecting AI Clients

Once the server is set up, configure your AI tool to use it. Replace the placeholder values with your actual .env values.

Claude Desktop

Open your claude_desktop_config.json file and add the following block inside "mcpServers":

{
  "mcpServers": {
    "mssql-readonly": {
      "command": "npx",
      "args": ["-y", "mssql-readonly-mcp"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_DATABASE": "YourDatabaseName",
        "MSSQL_USER": "mcp_readonly",
        "MSSQL_PASSWORD": "your-password",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERT": "true",
        "MSSQL_AUTH": "sql"
      }
    }
  }
}

📁 Where is this file?

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json

---

Claude Code (CLI)

Run this command in your terminal:

claude mcp add mssql-readonly -- npx -y mssql-readonly-mcp \
  -e MSSQL_SERVER=localhost \
  -e MSSQL_PORT=1433 \
  -e MSSQL_DATABASE=YourDatabaseName \
  -e MSSQL_USER=mcp_readonly \
  -e MSSQL_PASSWORD=your-password \
  -e MSSQL_ENCRYPT=true \
  -e MSSQL_TRUST_SERVER_CERT=true \
  -e MSSQL_AUTH=sql

---

Cursor

Add the following to ~/.cursor/mcp.json (global) or .cursor/mcp.json inside your project folder (project-specific):

{
  "mcpServers": {
    "mssql-readonly": {
      "command": "npx",
      "args": ["-y", "mssql-readonly-mcp"],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_DATABASE": "YourDatabaseName",
        "MSSQL_USER": "mcp_readonly",
        "MSSQL_PASSWORD": "your-password",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERT": "true",
        "MSSQL_AUTH": "sql"
      }
    }
  }
}

💡 Testing locally before publishing? Replace "command": "npx", "args": ["-y", "mssql-readonly-mcp"] with "command": "node", "args": ["/absolute/path/to/dist/index.js"] to point at your local build.

---

🌍 Running over HTTP (Multi-Client Mode)

By default, the server uses STDIO — meaning it's launched directly by the AI client process and only serves that one client.

If you want multiple AI clients to share a single running server instance, switch to HTTP mode:

MCP_TRANSPORT=http MCP_HTTP_PORT=3000 npm start

Clients then connect to: http://<your-host>:3000/mcp

Each client gets its own isolated session — one client's queries or context never leak into another's.

⚠️ HTTP Security Rules

Scenario	Recommendation
Running on localhost only	MCP_HTTP_API_KEY is optional
Accessible on a local network	Set MCP_HTTP_API_KEY to a strong secret
Exposed to the internet	DO NOT do this without a reverse proxy + TLS + authentication

When MCP_HTTP_API_KEY is set, clients must include it in every request as:

• Authorization: Bearer <key>, or
• X-API-Key: <key>

Important: HTTP mode still uses the same single read-only SQL login. It does not create per-user credentials or loosen the read-only guarantee in any way.

---

🛠️ Development & Testing

Available Scripts

npm run dev     # Run directly from TypeScript source (no build step needed)
npm run lint    # Run ESLint to check for code issues
npm run format  # Auto-format code with Prettier
npm test        # Run all unit tests with Vitest

Integration Testing Against a Real Database

The integration tests spin up a real SQL Server in Docker and verify that all tools work correctly — including confirming the read-only guarantee holds against an actual database engine.

# 1. Start a disposable SQL Server container
docker compose up -d

# 2. Seed the test database with sample data
npm run test:integration:seed

# 3. Run all tests (unit + integration)
npm test

No Docker? No problem. If localhost:1433 is unreachable, the integration tests skip themselves cleanly. The rest of the test suite still runs and exits with code 0.

To skip integration tests explicitly even when a database is available: SKIP_INTEGRATION_TESTS=1 npm test

Tear down the test database when done:

docker compose down -v   # ⚠️ This destroys all seeded test data

---

📦 Publishing to npm

This repo is ready to publish. Follow these one-time steps when you're ready to make it public:

Step	Command / Action
1. Make your first commit	git init && git add . && git commit -m "Initial commit"
2. Push to GitHub	Create a repo on GitHub and push
3. Verify CI passes	Check the Actions tab — .github/workflows/ci.yml should be green ✅
4. Log in to npm	npm login
5. Publish	npm run build && npm publish
6. Verify the published package	npx mssql-readonly-mcp (on any machine, no clone needed)
7. (Optional) Promote it	Submit to an MCP server registry or awesome-mcp-servers list

---

📄 License

This project is licensed under the MIT License — see the LICENSE file for full details.