🛡️ 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

Related MCP server: MSSQL MCP Server

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

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

Safety & Performance

Transport (STDIO vs HTTP)

🔐 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

🔒 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

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:

📄 License

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