npm package: @lincolnminto/mssql-mcp-server
Description: MCP server for SQL Server with secure data access and business context metadata tools.
Author: Lincoln Minto
Latest version: 1.0.6
Deprecated: Package no longer supported. Contact Support at https://www.npmjs.com/support for more info.

# @lincolnminto/mssql-mcp-server 🚀

An [MCP](https://modelcontextprotocol.io) server for **Microsoft SQL Server** that gives AI agents secure, natural-language access to your database — including a business-context metadata layer for legacy schemas with cryptic naming conventions.

[![npm version](https://img.shields.io/npm/v/%40lincolnminto%2Fmssql-mcp-server?logo=npm)](https://www.npmjs.com/package/@lincolnminto/mssql-mcp-server)
[![npm downloads](https://img.shields.io/npm/dm/%40lincolnminto%2Fmssql-mcp-server?logo=npm)](https://www.npmjs.com/package/@lincolnminto/mssql-mcp-server)
[![node](https://img.shields.io/node/v/%40lincolnminto%2Fmssql-mcp-server)](https://www.npmjs.com/package/@lincolnminto/mssql-mcp-server)
[![license](https://img.shields.io/npm/l/%40lincolnminto%2Fmssql-mcp-server)](LICENSE)
[![CI](https://img.shields.io/github/actions/workflow/status/lincolnminto/MssqlMcp/ci.yml?branch=main&label=CI&logo=github)](https://github.com/lincolnminto/MssqlMcp/actions/workflows/ci.yml)
[![Publish](https://img.shields.io/github/actions/workflow/status/lincolnminto/MssqlMcp/publish.yml?label=Publish&logo=github)](https://github.com/lincolnminto/MssqlMcp/actions/workflows/publish.yml)

---

## ⚡ Quick Start

```bash
npx -y @lincolnminto/mssql-mcp-server
```

> **Note:** The server requires environment variables to connect (see [Configuration](#-configuration) below). For real usage, add it to your AI agent's MCP config.

### 🔗 Quick Links

[![View on NPM](https://img.shields.io/badge/NPM-View_Package-CB3837?logo=npm)](https://www.npmjs.com/package/@lincolnminto/mssql-mcp-server)
[![Cursor Setup](https://img.shields.io/badge/Cursor-Setup_Guide-111111?logo=data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMjQiIGhlaWdodD0iMjQiIHZpZXdCb3g9IjAgMCAyNCAyNCIgZmlsbD0id2hpdGUiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PGNpcmNsZSBjeD0iMTIiIGN5PSIxMiIgcj0iMTAiLz48L3N2Zz4=)](#-cursor)
[![Copilot Setup](https://img.shields.io/badge/GitHub_Copilot-Setup_Guide-181717?logo=github)](#-vs-code--github-copilot)
[![Claude Setup](https://img.shields.io/badge/Claude_Desktop-Setup_Guide-6B46C1)](#-claude-desktop)

---

## ✨ Features

- 🔍 **Secure SQL queries** — validates `SELECT`-only, blocks destructive patterns, parameterized inputs
- 📋 **Schema exploration** — list tables, views, relationships, stored procedures, and server metadata
- 🧠 **Business context layer** — map legacy names (`STD0123`, `NM_FRST`) to human terms (`Student`, `First Name`)
- 🔒 **Read-only mode** — set `READONLY=true` to restrict all write operations
- 🔑 **Flexible auth** — SQL Server credentials or Azure AD (OIDC)
- 📦 **Zero install** — runs directly via `npx`

---

## 🧰 Available MCP Tools

### 📖 Read-Only Tools (always available)

| Tool                      | Description                                               |
| ------------------------- | --------------------------------------------------------- |
| `list_table`              | List base tables, optionally filtered by schema           |
| `read_data`               | Execute validated `SELECT` queries (max 10,000 rows)      |
| `describe_table`          | Column metadata, primary keys, foreign keys               |
| `list_views`              | List views with optional SQL definitions                  |
| `get_table_relationships` | Foreign-key relationships across tables                   |
| `get_database_info`       | Server version, collation, compatibility level, size      |
| `list_stored_procedures`  | Procedures and parameter metadata                         |
| `describe_entities`       | Business entity metadata from config file                 |
| `search_business_context` | NL search across entities, fields, glossary, and examples |

### ✏️ Write Tools (when `READONLY=false`)

| Tool                       | Description                                       |
| -------------------------- | ------------------------------------------------- |
| `insert_data`              | Insert one or many rows with parameterized values |
| `update_data`              | Update rows (requires `WHERE` clause)             |
| `create_table`             | Create table from column definitions              |
| `create_index`             | Create clustered/non-clustered indexes            |
| `drop_table`               | Drop a table by name or `schema.table`            |
| `execute_stored_procedure` | Execute a procedure with named parameters         |

---

## 🔧 Configuration

### Environment Variables

| Variable                   | Required | Description                                            |
| -------------------------- | -------- | ------------------------------------------------------ |
| `SERVER_NAME`              | ✅       | SQL Server host (e.g. `myserver.database.windows.net`) |
| `DATABASE_NAME`            | ✅       | Database name                                          |
| `SQL_USERNAME`             |          | SQL login username                                     |
| `SQL_PASSWORD`             |          | SQL login password                                     |
| `READONLY`                 |          | Set to `true` to disable write tools                   |
| `CONNECTION_TIMEOUT`       |          | Connection timeout in seconds (default: `30`)          |
| `QUERY_TIMEOUT`            |          | Query timeout in seconds (default: `30`)               |
| `TRUST_SERVER_CERTIFICATE` |          | Set to `true` for self-signed certs (dev only)         |
| `ENTITIES_CONFIG_PATH`     |          | Path to business context JSON/JSONC metadata file      |

> **Auth mode:** If both `SQL_USERNAME` and `SQL_PASSWORD` are set, SQL authentication is used. Otherwise, the server falls back to Azure AD via `InteractiveBrowserCredential`.

---

## 🧩 Agent Configuration

<a id="-cursor"></a>

<details>
<summary>🖥️ <strong>Cursor</strong> — <code>.cursor/mcp.json</code></summary>

```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@lincolnminto/mssql-mcp-server"],
      "env": {
        "SERVER_NAME": "your-server.database.windows.net",
        "DATABASE_NAME": "your-database",
        "SQL_USERNAME": "your-username",
        "SQL_PASSWORD": "your-password",
        "READONLY": "true"
      }
    }
  }
}
```

</details>

<a id="-vs-code--github-copilot"></a>

<details>
<summary>🤖 <strong>VS Code + GitHub Copilot</strong> — <code>settings.json</code> or <code>.vscode/mcp.json</code></summary>

**Option A: User/Workspace Settings** (`settings.json`)

```json
{
  "mcp": {
    "servers": {
      "mssql": {
        "command": "npx",
        "args": ["-y", "@lincolnminto/mssql-mcp-server"],
        "env": {
          "SERVER_NAME": "your-server.database.windows.net",
          "DATABASE_NAME": "your-database",
          "SQL_USERNAME": "your-username",
          "SQL_PASSWORD": "your-password",
          "READONLY": "false"
        }
      }
    }
  }
}
```

**Option B: Workspace MCP File** (`.vscode/mcp.json`)

```json
{
  "servers": {
    "mssql": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "@lincolnminto/mssql-mcp-server"],
      "env": {
        "SERVER_NAME": "your-server.database.windows.net",
        "DATABASE_NAME": "your-database",
        "SQL_USERNAME": "your-username",
        "SQL_PASSWORD": "your-password",
        "READONLY": "false"
      }
    }
  }
}
```

</details>

<a id="-claude-desktop"></a>

<details>
<summary>💬 <strong>Claude Desktop</strong> — <code>claude_desktop_config.json</code></summary>

```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@lincolnminto/mssql-mcp-server"],
      "env": {
        "SERVER_NAME": "your-server.database.windows.net",
        "DATABASE_NAME": "your-database",
        "SQL_USERNAME": "your-username",
        "SQL_PASSWORD": "your-password",
        "READONLY": "true"
      }
    }
  }
}
```

</details>

<details>
<summary>🔧 <strong>Windsurf / Generic MCP Client</strong></summary>

```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@lincolnminto/mssql-mcp-server"],
      "env": {
        "SERVER_NAME": "your-server.database.windows.net",
        "DATABASE_NAME": "your-database",
        "SQL_USERNAME": "your-username",
        "SQL_PASSWORD": "your-password",
        "READONLY": "true"
      }
    }
  }
}
```

</details>

---

## 🧠 Business Context for Legacy Databases

Large enterprise databases often have cryptic naming conventions (`STD0123`, `NM_FRST`, `CD_SMSTR`). The business context layer maps these to human-readable terms so AI agents can translate natural language into correct SQL.

This approach is compatible with [Data API Builder entity descriptions](https://learn.microsoft.com/en-us/azure/data-api-builder/mcp/how-to-add-descriptions).

### How It Works

1. Create a metadata file based on [`metadata/entities-config.example.jsonc`](metadata/entities-config.example.jsonc)
2. Validate it against [`metadata/entities-config.schema.json`](metadata/entities-config.schema.json) (IDE auto-validation via `$schema`)
3. Set `ENTITIES_CONFIG_PATH` in your MCP config
4. The agent workflow becomes:

```
User: "List students over 30 in the first semester"
  → Agent calls search_business_context("students age semester")
  → Agent gets: table STD0123, column DT_BRTH (age), CD_SMSTR=1 (First Semester)
  → Agent calls read_data with the correct SQL
  → Results displayed in chat
```

---

## 🛡️ Security

- `read_data` enforces `SELECT`-only and rejects destructive keywords, multi-statement queries, and obfuscation patterns
- Write tools are completely disabled when `READONLY=true`
- `update_data` requires an explicit `WHERE` clause
- Table, column, and schema identifiers are validated and bracket-quoted
- All user values are passed via parameterized queries
- Logs are written to `stderr` to avoid corrupting the MCP stdio protocol

---

## 🛠️ Development

```bash
npm install
npm test
npm run build
```

---

## 📄 License

[MIT](LICENSE)