# 🛡️ 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](#-how-it-works)
- [Prerequisites](#-prerequisites)
- [Quick Start](#-quick-start)
- [Configuration Reference](#-configuration-reference)
- [Step 1 — Create a Read-Only SQL Login](#-step-1--create-a-read-only-sql-login)
- [Step 2 — Enable TCP/IP (if needed)](#-step-2--enable-tcpip-if-needed)
- [Step 3 — Install & Run](#-step-3--install--run)
- [Connecting AI Clients](#-connecting-ai-clients)
- [Running over HTTP (Multi-Client Mode)](#-running-over-http-multi-client-mode)
- [Development & Testing](#-development--testing)
- [Publishing to npm](#-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](https://nodejs.org/) |
| **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.

```sh
# 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:

```sh
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:

```sql
-- 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)

```sh
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)

```sh
npx mssql-readonly-mcp
```

### Verify it's working

Use the [MCP Inspector](https://github.com/modelcontextprotocol/inspector) to test the server interactively in your browser:

```sh
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"`:

```json
{
  "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:

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

```json
{
  "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:

```sh
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

```sh
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.

```sh
# 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:**

```sh
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](./LICENSE) file for full details.