MSSQL Database MCP Server

What is this? 🤔

This is a server that lets your LLMs (like Claude) talk directly to your MSSQL Database data! Think of it as a friendly translator that sits between your AI assistant and your database, making sure they can chat securely and efficiently.

Quick Example

You: "Show me all customers from New York"
Claude: *queries your MSSQL Database database and gives you the answer in plain English*

How Does It Work? 🛠️

This server leverages the Model Context Protocol (MCP), a versatile framework that acts as a universal translator between AI models and databases. It supports multiple AI assistants including Claude Desktop and VS Code Agent.

What Can It Do? 📊

• Run MSSQL Database queries by just asking questions in plain English
• Create, read, update, and delete data
• Execute stored procedures with parameterized inputs
• Manage database schema (tables, indexes)
• Dry-run confirmation flow — preview operations before executing, then confirm with a token
• MCP Elicitation support — interactive human approval when the client supports it
• Secure connection handling
• Real-time data interaction

Quick Start 🚀

Prerequisites

• Node.js 14 or higher
• Claude Desktop or VS Code with Agent extension
• GitHub account with Personal Access Token for package access

Installation

Option 1: Install from GitHub Packages

1. Create a GitHub Personal Access Token

   • Go to GitHub Settings → Developer settings → Personal access tokens
   • Create a token with read:packages scope
   • Save the token securely

2. Configure npm for GitHub Packages

   echo "@jhlee111:registry=https://npm.pkg.github.com" >> ~/.npmrc
   echo "//npm.pkg.github.com/:_authToken=YOUR_GITHUB_TOKEN" >> ~/.npmrc
   

3. Install the package

   npm install @jhlee111/mssql-mcp-server
   

Option 2: Install from Source

1. Clone the repository

   git clone https://github.com/jhlee111/mssql-mcp-server.git
   cd mssql-mcp-server
   

2. Install Dependencies

   npm install
   

3. Build the Project

   npm run build
   

Configuration Setup

Option 1: VS Code Agent Setup

1. Install VS Code Agent Extension

   • Open VS Code
   • Go to Extensions (Ctrl+Shift+X)
   • Search for "Agent" and install the official Agent extension

2. Create MCP Configuration File

   • Create a .vscode/mcp.json file in your workspace
   • Add the following configuration:

   {
     "servers": {
       "mssql-nodejs": {
          "type": "stdio",
          "command": "node",
          "args": ["q:\\Repos\\SQL-AI-samples\\MssqlMcp\\Node\\dist\\index.js"],
          "env": {
            "SERVER_NAME": "your-server-name.database.windows.net",
            "DATABASE_NAME": "your-database-name",
            "SQL_USER": "your-sql-username",
            "SQL_PASSWORD": "your-sql-password",
            "READONLY": "false"
          }
        }
      }
   }
   

3. Alternative: User Settings Configuration

   • Open VS Code Settings (Ctrl+,)
   • Search for "mcp"
   • Click "Edit in settings.json"
   • Add the following configuration:

 {
  "mcp": {
      "servers": {
          "mssql": {
              "command": "node",
              "args": ["C:/path/to/your/Node/dist/index.js"],
              "env": {
              "SERVER_NAME": "your-server-name.database.windows.net",
              "DATABASE_NAME": "your-database-name",
              "SQL_USER": "your-sql-username",
              "SQL_PASSWORD": "your-sql-password",
              "READONLY": "false"
              }
          }
      }
  }
}

4. Restart VS Code

   • Close and reopen VS Code for the changes to take effect

5. Verify MCP Server

   • Open Command Palette (Ctrl+Shift+P)
   • Run "MCP: List Servers" to verify your server is configured
   • You should see "mssql" in the list of available servers

Option 2: Claude Desktop Setup

1. Open Claude Desktop Settings

   • Navigate to File → Settings → Developer → Edit Config
   • Open the claude_desktop_config file

2. Add MCP Server Configuration Replace the content with the configuration below, updating the path and credentials:

   {
     "mcpServers": {
       "mssql": {
         "command": "node",
         "args": ["C:/path/to/your/Node/dist/index.js"],
         "env": {
           "SERVER_NAME": "your-server-name.database.windows.net",
           "DATABASE_NAME": "your-database-name",
           "SQL_USER": "your-sql-username",
           "SQL_PASSWORD": "your-sql-password",
           "READONLY": "false"
         }
       }
     }
   }
   

3. Restart Claude Desktop

   • Close and reopen Claude Desktop for the changes to take effect

Configuration Parameters

Required Database Connection Parameters

• MSSQL_SERVER: Your MSSQL server name (e.g., my-server.database.windows.net for Azure SQL, or localhost for local SQL Server)
• MSSQL_DATABASE: Your database name
• MSSQL_USER: SQL Server username for authentication
• MSSQL_PASSWORD: SQL Server password for authentication

Optional Connection Parameters

• READONLY: Set to "true" to restrict to read-only operations, "false" for full access
• MSSQL_CONNECTION_TIMEOUT: Connection timeout in seconds. Defaults to 30 if not set
• MSSQL_TRUST_SERVER_CERTIFICATE: Set to "true" to trust self-signed server certificates (useful for development). Defaults to "false"

Safety Configuration Parameters (New!)

These parameters control approval requirements and operational safety:

STORED PROCEDURE EXECUTION:

• ALLOW_EXEC_PROCEDURE: Allow stored procedure execution via the exec_procedure tool (default: false - DISABLED)
  • Stored procedures can read and modify data, so this requires explicit opt-in
  • Set to true to enable the exec_procedure tool

DANGEROUS OPERATIONS (DROP):

• ALLOW_DANGEROUS_OPERATIONS: Allow DROP operations (default: false - FORBIDDEN)
  • DROP operations are FORBIDDEN by default for maximum safety
  • Must explicitly set to true to enable DROP TABLE/INDEX operations
  • ⚠️ CRITICAL WARNING: DROP operations permanently delete tables/indexes and all their data
  • Even when enabled, use with ENABLE_DRY_RUN=true to preview before executing

REGULAR OPERATIONS APPROVAL:

• REQUIRE_APPROVAL_CREATE: Require approval for CREATE operations (default: false)

  • When true, CREATE TABLE operations are blocked until explicitly enabled
  • Set to false to allow CREATE operations

• REQUIRE_APPROVAL_UPDATE: Require approval for UPDATE operations (default: false)

  • When true, UPDATE operations are blocked until explicitly enabled
  • Set to false to allow UPDATE operations

• REQUIRE_APPROVAL_DELETE: Require approval for DELETE operations (default: false)

  • When true, DELETE (row removal) operations are blocked until explicitly enabled
  • Set to false to allow DELETE operations
  • Note: DELETE removes rows, DROP removes entire tables (different operations!)

• REQUIRE_APPROVAL_INSERT: Require approval for INSERT operations (default: false)

  • When true, INSERT operations are blocked until explicitly enabled
  • Set to false to allow INSERT operations

DRY-RUN AND LOGGING:

• ENABLE_DRY_RUN: Enable dry-run mode for all destructive operations (default: false)

  • When true, all CREATE, DROP, UPDATE, DELETE, INSERT, and EXEC operations return a preview with a confirmation token
  • The caller can re-invoke the same tool with the same parameters plus confirmToken to execute
  • Shows the exact SQL query, estimated impact, and row count estimates (for UPDATE/DELETE)
  • Essential for testing and understanding what the LLM intends to do before execution
  • Recommended: Always enable for DROP operations even when ALLOW_DANGEROUS_OPERATIONS=true

• DRY_RUN_TTL_SECONDS: TTL for confirmation tokens in seconds (default: 300 = 5 minutes)

  • Tokens are single-use and expire after this duration
  • Set to a shorter value for tighter security

• OPERATION_LOG_DIR: Directory for operation logs (default: ./logs)

  • All destructive operations are automatically logged to operations.log in this directory
  • Logs include timestamp, operation type, severity, SQL query, and success/failure status
  • Provides audit trail for compliance and debugging

Path Configuration

• Path: Update the path in args to point to your actual project location (absolute path to dist/index.js)

Authentication Methods

This MCP server has been modified to support SQL Server authentication (username/password).

Original Source: The original implementation from Microsoft SQL-AI-samples repository uses Azure Active Directory (AAD) authentication with InteractiveBrowserCredential.

Current Modification: This version has been modified to use SQL Server authentication for broader compatibility:

• Removed Azure AD dependency (@azure/identity)
• Changed from token-based authentication to username/password authentication
• Added SQL_USER and SQL_PASSWORD environment variables
• Simplified connection pooling without token refresh logic

To revert to Azure AD authentication, you would need to:

1. Re-add the @azure/identity package
2. Restore the InteractiveBrowserCredential or DefaultAzureCredential in index.ts
3. Remove SQL_USER and SQL_PASSWORD from configuration
4. Restore the token-based authentication configuration

Sample Configurations

You can find sample configuration files in the src/samples/ folder:

• claude_desktop_config.json - For Claude Desktop
• vscode_agent_config.json - For VS Code Agent

SQL Server Version Support

The MCP server automatically detects your SQL Server version and adapts its syntax accordingly. It supports:

• SQL Server 2008/2008 R2 (10.x) and later
• SQL Server 2012 (11.x) - Adds sequences, window functions, OFFSET/FETCH
• SQL Server 2014 (12.x) - Adds In-Memory OLTP
• SQL Server 2016 (13.x) - Adds DROP IF EXISTS, JSON type, temporal tables
• SQL Server 2017 (14.x) - Adds STRING_AGG, graph database
• SQL Server 2019 (15.x) - Adds UTF-8 support
• SQL Server 2022 (16.x) - Adds JSON extensions

Version-Aware Features

Automatic Syntax Adaptation:

• DROP TABLE IF EXISTS on SQL Server 2016+ (falls back to standard DROP on older versions)
• Data type validation (warns if JSON type used on pre-2016)
• Feature availability warnings

On First Connection:

Detected SQL Server version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5
Server capabilities detected: {
  version: 'SQL Server 2019',
  dropIfExists: true,
  json: true,
  stringAgg: true
}

Compatibility Notes

The LLM will automatically:

• Use version-appropriate syntax
• Warn about unsupported features
• Suggest alternatives for older versions

For example, if you ask to create a table with JSON column on SQL Server 2014:

⚠️  Data Type Warnings:
  - Column 'data': JSON type requires SQL Server 2016+.
    Current: SQL Server 2014. Consider using NVARCHAR(MAX) instead.

Usage Examples

Once configured, you can interact with your database using natural language:

• "Show me all users from New York"
• "Create a new table called products with columns for id, name, and price"
• "Update all pending orders to completed status"
• "List all tables in the database"
• "Drop the temp_data table if it exists" (uses IF EXISTS on SQL Server 2016+)

Stored Procedure Execution

The exec_procedure tool allows executing stored procedures with parameterized inputs. It requires explicit opt-in via ALLOW_EXEC_PROCEDURE=true.

Usage

You: "Run the annual income report for 2025"
Claude: *calls exec_procedure with the appropriate SP and parameters*

Parameters

• procedureName (required): The stored procedure name, optionally with schema prefix (e.g., qb.up_QbAnuualIncome_since2018_forSTI or up_SomeProcedure)
• parameters (optional): Key-value pairs of input parameters (e.g., { "year": "2025", "orgCd": "N20110" })

Security

• Procedure names are validated against strict patterns (word characters only, optional schema prefix)
• Parameters are passed via parameterized inputs (SQL injection safe)
• Gated by ALLOW_EXEC_PROCEDURE environment variable (default: disabled)

Dry-Run Confirmation Flow

When ENABLE_DRY_RUN=true, destructive operations use a two-phase confirmation flow instead of simply blocking execution:

Phase 1: Preview

All destructive operations (INSERT, UPDATE, DELETE, CREATE, DROP, EXEC) return a preview with a confirmation token:

{
  "mode": "preview",
  "success": true,
  "message": "DRY RUN PREVIEW - MEDIUM OPERATION\n...\nTo execute, call the same tool again with confirmToken",
  "dryRun": true,
  "confirmToken": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
}

Phase 2: Confirm

Re-invoke the same tool with the same parameters plus the confirmToken to execute:

{
  "mode": "executed",
  "success": true,
  "message": "Stored procedure executed successfully. 1 result set(s), 901 total record(s).",
  "data": [ ... ],
  "rowsAffected": [0]
}

Token Security

• Tokens are single-use — consumed on first successful validation
• Tokens expire after 5 minutes (configurable via DRY_RUN_TTL_SECONDS)
• Tokens are bound to exact parameters — SHA-256 hash of query and params must match
• Maximum 100 pending tokens in memory with oldest-first eviction
• Server restart invalidates all pending tokens

MCP Elicitation (Interactive Approval)

When the MCP client supports elicitation (e.g., future Claude Code versions), the server automatically upgrades to interactive human approval:

1. The tool pauses mid-execution and presents the SQL to the human user directly
2. The user approves or declines in their terminal
3. The LLM is structurally excluded from the approval path

This activates automatically when the client advertises elicitation support. Falls back to token-based confirmation otherwise. No configuration needed.

Response Mode Field

All destructive tool responses include a mode field for programmatic handling:

Mode	Meaning
preview	Dry-run preview with confirmation token
executed	Operation was executed successfully
confirmation_failed	Token validation failed (expired, used, params changed)
error	Execution error
approval_required	Blocked by approval config
forbidden	Operation forbidden (e.g., DROP without ALLOW_DANGEROUS_OPERATIONS)

Safety and Security Features

Built-in Security

• The server requires a WHERE clause for read operations to prevent accidental full table scans
• Update operations require explicit WHERE clauses for security
• Set READONLY: "true" in production environments if you only need read access
• Extensive SQL injection protection in ReadDataTool with keyword blocking and pattern detection

Safety System (New!)

The MCP server includes a comprehensive safety system with special protection for destructive operations:

1. DROP Operations - FORBIDDEN by Default

DROP operations are FORBIDDEN unless explicitly enabled:

{
  "env": {
    "ALLOW_DANGEROUS_OPERATIONS": "false"  // Default - DROP is FORBIDDEN
  }
}

When attempted, the LLM receives:

🔴 DANGEROUS OPERATION FORBIDDEN
============================================================

DROP operations are FORBIDDEN by default for safety.

Operation Type: DROP
Target: users_table

⛔ WARNING: Enabling dangerous operations can lead to permanent data loss!

To enable DROP (use with extreme caution):

{
  "env": {
    "ALLOW_DANGEROUS_OPERATIONS": "true",  // Enable DROP operations
    "ENABLE_DRY_RUN": "true"               // ALWAYS preview DROP first!
  }
}

2. DELETE vs DROP - Important Distinction

• DELETE = Removes rows from a table (data operation) - configurable approval
• DROP = Removes entire table/index (schema change) - FORBIDDEN by default

{
  "env": {
    "REQUIRE_APPROVAL_DELETE": "true",  // Control row deletion
    "ALLOW_DANGEROUS_OPERATIONS": "false"  // DROP still forbidden
  }
}

3. Dry-Run Mode

Test what the LLM wants to do without making any changes:

{
  "env": {
    "ENABLE_DRY_RUN": "true"  // Preview all operations without executing
  }
}

Example dry-run output:

🔴 DRY RUN PREVIEW - CRITICAL OPERATION
============================================================

Operation Type: DROP
Target: users_table

SQL Query:
DROP TABLE [users_table]

Estimated Impact:
This will permanently delete the table and all its data.

============================================================
⚠️  This is a DRY RUN. No changes have been made to the database.
To execute this operation, call the same tool again with "confirmToken": "a1b2c3d4-..."

4. Operation Logging

All destructive operations are automatically logged to ./logs/operations.log:

{
  "timestamp": "2025-10-31T12:34:56.789Z",
  "operationType": "DROP",
  "target": "users_table",
  "query": "DROP TABLE [users_table]",
  "severity": "CRITICAL",
  "dryRun": false,
  "success": true
}

Recommended Safety Configurations

Development/Testing:

{
  "env": {
    "ENABLE_DRY_RUN": "true",                  // Preview everything first
    "ALLOW_DANGEROUS_OPERATIONS": "false",     // Keep DROP forbidden
    "REQUIRE_APPROVAL_CREATE": "false",        // Allow CREATE for testing
    "REQUIRE_APPROVAL_DELETE": "false",        // Allow DELETE for testing
    "REQUIRE_APPROVAL_UPDATE": "false",        // Allow UPDATE for testing
    "OPERATION_LOG_DIR": "./logs"              // Local logging
  }
}

Production (Safest):

{
  "env": {
    "READONLY": "true"  // Only allow read operations
  }
}

Production (With Write Access, Maximum Safety):

{
  "env": {
    "READONLY": "false",
    "ALLOW_DANGEROUS_OPERATIONS": "false",     // DROP forbidden
    "REQUIRE_APPROVAL_CREATE": "true",         // Block CREATE
    "REQUIRE_APPROVAL_DELETE": "true",         // Block DELETE
    "REQUIRE_APPROVAL_UPDATE": "false",        // Allow UPDATE (has WHERE requirement)
    "REQUIRE_APPROVAL_INSERT": "false",        // Allow INSERT
    "OPERATION_LOG_DIR": "/var/log/mcp"        // Centralized logging
  }
}

Production (Allow DROP with extreme caution):

{
  "env": {
    "READONLY": "false",
    "ALLOW_DANGEROUS_OPERATIONS": "true",      // ⚠️ Enable DROP
    "ENABLE_DRY_RUN": "true",                  // ⚠️ ALWAYS preview first!
    "REQUIRE_APPROVAL_CREATE": "false",
    "REQUIRE_APPROVAL_DELETE": "false",
    "OPERATION_LOG_DIR": "/var/log/mcp"
  }
}

You should now have successfully configured the MCP server for MSSQL Database with your preferred AI assistant. This setup allows you to seamlessly interact with MSSQL Database through natural language queries with robust safety protections!

Testing Your MCP Server

Before using the server in production, it's recommended to test it thoroughly.

Quick Test with MCP Inspector

The easiest way to test is using the official MCP Inspector:

# Set environment variables first
export MSSQL_SERVER="your-server.database.windows.net"
export MSSQL_DATABASE="your-database"
export MSSQL_USER="your-username"
export MSSQL_PASSWORD="your-password"
export ENABLE_DRY_RUN="true"  # Safe testing mode

# Run inspector
npx @modelcontextprotocol/inspector node dist/index.js

The Inspector will open in your browser where you can:

• See all available tools
• Test tools with custom parameters
• View real-time responses
• Debug issues

Testing Checklist

• Server starts without errors
• Version detection works (check console output)
• List tables returns results
• DROP operations are forbidden by default
• Dry-run mode shows operation previews with confirmation tokens
• Confirmation tokens allow execution when replayed with same params
• Expired/reused tokens are rejected
• exec_procedure tool works with stored procedures
• Operations are logged to logs/operations.log

For detailed testing procedures, see TESTING.md which covers:

• MCP Inspector usage
• Manual testing with Node.js scripts
• Testing with Claude Desktop/VS Code
• Safety feature testing
• Version compatibility testing
• Troubleshooting common issues