MSSQL Model Context Protocol (MCP) Server

A Node.js implementation of the Model Context Protocol server for Microsoft SQL Server databases. This server provides a standardized API interface to interact with SQL Server databases, exposing database tables as resources and offering tools to execute SQL queries and retrieve schema information.

Multi-Database Support

This project now features automatic configuration detection that allows it to work in two modes:

1. Single-database mode - Uses simple MSSQL_* variables to connect to one database
2. Multi-database mode - Uses prefixed environment variables (MSSQL_<DBNAME>_*) to connect to multiple databases with custom names

The server auto-detects which mode is active at runtime and exposes the same REST/MCP interface in either case. In multi-database mode, you can use any database names you prefer (e.g., MSSQL_MAINDB_*, MSSQL_REPORTINGDB_*, MSSQL_ANALYTICS_*, MSSQL_CUSTOMERS_*, etc.).

Environment Configuration

Single-Database Mode

# Single database configuration
MSSQL_SERVER=your_sql_server_address
MSSQL_PORT=1433
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_DATABASE=your_database_name
MSSQL_ENCRYPT=true
MSSQL_TRUST_SERVER_CERTIFICATE=false

Multi-Database Mode

# Main database
MSSQL_MAINDB_SERVER=your_sql_server_address
MSSQL_MAINDB_PORT=1433
MSSQL_MAINDB_USER=your_username
MSSQL_MAINDB_PASSWORD=your_password
MSSQL_MAINDB_DATABASE=main_db_name
MSSQL_MAINDB_ENCRYPT=true
MSSQL_MAINDB_TRUST_SERVER_CERTIFICATE=false

# Reporting database
MSSQL_REPORTINGDB_SERVER=your_sql_server_address
MSSQL_REPORTINGDB_PORT=1433
MSSQL_REPORTINGDB_USER=your_username
MSSQL_REPORTINGDB_PASSWORD=your_password
MSSQL_REPORTINGDB_DATABASE=reporting_db_name
MSSQL_REPORTINGDB_ENCRYPT=true
MSSQL_REPORTINGDB_TRUST_SERVER_CERTIFICATE=false

Custom Database Names Example

You can use any database names you prefer by following the pattern MSSQL_<YOUR_CUSTOM_NAME>_*:

# Analytics database
MSSQL_ANALYTICS_SERVER=analytics.example.com
MSSQL_ANALYTICS_PORT=1433
MSSQL_ANALYTICS_USER=analytics_user
MSSQL_ANALYTICS_PASSWORD=analytics_password
MSSQL_ANALYTICS_DATABASE=analytics_data
MSSQL_ANALYTICS_ENCRYPT=true
MSSQL_ANALYTICS_TRUST_SERVER_CERTIFICATE=false

# Customer database
MSSQL_CUSTOMERS_SERVER=customers.example.com
MSSQL_CUSTOMERS_PORT=1433
MSSQL_CUSTOMERS_USER=customer_user
MSSQL_CUSTOMERS_PASSWORD=customer_password
MSSQL_CUSTOMERS_DATABASE=customer_data
MSSQL_CUSTOMERS_ENCRYPT=true
MSSQL_CUSTOMERS_TRUST_SERVER_CERTIFICATE=false

The server will automatically detect any database configurations following this pattern and make them available with lowercase keys (e.g., analytics, customers).

Important: Configure EITHER the Single-Database OR the Multi-Database variables in your .env file - not both. The server detects which mode to use based on the presence of specific variables.

Configuration and Behavior Matrix

Default dbKey Behavior

In multi-database mode, when no dbKey is specified in the request, the server automatically uses the first database in your configuration alphabetically. This makes API requests more concise while maintaining backward compatibility.

Features

• Auto-detect Configuration Mode: Automatically determines whether to use single or multi-database mode
• Resource Management: Access SQL Server tables as resources
• SQL Query Execution: Execute SQL queries against the connected database(s)
• Schema Information: Retrieve metadata and schema details for database tables
• MCP Protocol Support: Communicates via STDIO using the Model Context Protocol SDK
• HTTP API: For local testing using Express
• Enhanced Validation: Uses Zod for robust input validation with clear error messages
• Security Features: Parameterized queries and SQL injection protection

Installation

Prerequisites

• Node.js (v14 or higher)
• Access to a Microsoft SQL Server database

Steps

1. Clone the Repository

   git clone https://github.com/mihai-dulgheru/mssql-mcp-node.git
   cd mssql-mcp-node
   

2. Install Dependencies

   npm install
   

3. Configure Environment Variables

   Copy the example environment configuration and update as needed:

   cp .env.example .env
   

   Then, update the .env file with your SQL Server connection details using EITHER single-database OR multi-database format (see above sections).

   Security Recommendations:

   • Development:
     • MSSQL_ENCRYPT="false" or MSSQL_*DB_ENCRYPT="false"
     • MSSQL_TRUST_SERVER_CERTIFICATE="true" or MSSQL_*DB_TRUST_SERVER_CERTIFICATE="true"
   • Production:
     • MSSQL_ENCRYPT="true" or MSSQL_*DB_ENCRYPT="true" (to encrypt the connection)
     • MSSQL_TRUST_SERVER_CERTIFICATE="false" or MSSQL_*DB_TRUST_SERVER_CERTIFICATE="false" (to enforce certificate validation)

Usage

There are two modes of operation:

1. MCP Mode (STDIO)

This mode uses the Model Context Protocol (MCP) SDK with STDIO transport and is designed for integration with clients like Claude Desktop or VS Code.

• Start MCP Mode:

  npm start
  

  This runs the MCP server from src/index.js.

2. HTTP Mode (Express)

For local testing via HTTP, you can start the Express server that exposes API endpoints.

• Start Express Mode:

  npm run start:express
  

  This runs the Express server defined in src/express.js.

• Development Mode with Auto-Reload:

  npm run dev:express
  

API Endpoints (Express Mode)

• List Resources (Tables):

  GET /resources?dbKey=maindb
  

  Example Response:

  [
    {
      "uri": "mssql://YourTable/data",
      "name": "Table: YourTable",
      "description": "Data in table: YourTable (DB: your_database)",
      "mimeType": "text/plain"
    }
  ]
  

• Get Resource Data:

  GET /resource?uri=mssql://YourTable/data&dbKey=maindb
  

  Example Response:

  # Database: your_database
  id,name,created_at
  1,Item1,2025-01-01
  2,Item2,2025-01-02
  

• List Available Tools:

  GET /tools
  

  Example Response:

  [
    {
      "name": "execute_sql",
      "description": "Execute an SQL query on the SQL Server (multi-database support)",
      "inputSchema": {
        "type": "object",
        "properties": {
          "query": {
            "type": "string",
            "description": "The SQL query to execute"
          },
          "dbKey": {
            "type": "string",
            "description": "The database key to use (e.g., 'maindb', 'reportingdb', etc.). Optional in single-db mode."
          }
        },
        "required": ["query"]
      }
    },
    {
      "name": "get_table_schema",
      "description": "Retrieve the schema of a specified table (multi-database support)",
      "inputSchema": {
        "type": "object",
        "properties": {
          "table": {
            "type": "string",
            "description": "The name of the table"
          },
          "dbKey": {
            "type": "string",
            "description": "The database key to use (e.g., 'maindb', 'reportingdb', etc.). Optional in single-db mode."
          }
        },
        "required": ["table"]
      }
    }
  ]
  

• Execute SQL Query:

  POST /execute-sql
  

  Request Body:

  {
    "query": "SELECT TOP 10 * FROM YourTable",
    "dbKey": "maindb" // Optional, defaults to first configured database
  }
  

  Response Example for SELECT queries:

  {
    "db": "your_database",
    "rowCount": 2,
    "recordset": [
      { "id": 1, "name": "Item1", "created_at": "2025-01-01" },
      { "id": 2, "name": "Item2", "created_at": "2025-01-02" }
    ]
  }
  

  Response Example for non-SELECT queries:

  {
    "message": "Query executed successfully",
    "db": "your_database",
    "rowsAffected": 1
  }
  

• Get Table Schema:

  POST /get-table-schema
  

  Request Body:

  {
    "table": "YourTable",
    "dbKey": "reportingdb" // Optional, defaults to first configured database
  }
  

  Response Example:

  {
    "db": "reporting_db_name",
    "table": "YourTable",
    "columns": [
      {
        "COLUMN_NAME": "id",
        "DATA_TYPE": "int",
        "CHARACTER_MAXIMUM_LENGTH": null
      },
      {
        "COLUMN_NAME": "name",
        "DATA_TYPE": "varchar",
        "CHARACTER_MAXIMUM_LENGTH": 100
      },
      {
        "COLUMN_NAME": "created_at",
        "DATA_TYPE": "datetime",
        "CHARACTER_MAXIMUM_LENGTH": null
      }
    ],
    "rowCount": 3
  }
  

Integration with Claude Desktop or VS Code

To integrate this MCP server with Claude Desktop or VS Code, add the following JSON snippet to your MCP configuration file. For Claude Desktop, this is typically in mcpServers.json, and for VS Code, in your workspace configuration (.vscode/mcp.json).

VS Code

For VS Code 1.86.0 and newer, use either single or multi-database configuration:

Single-Database Configuration

{
  "servers": {
    "mssql-mcp-node-single": {
      "command": "npx",
      "args": ["-y", "mssql-mcp-node"],
      "env": {
        "MSSQL_SERVER": "your_server_name",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

Multi-Database Configuration

{
  "servers": {
    "mssql-mcp-node-multi": {
      "command": "npx",
      "args": ["-y", "mssql-mcp-node"],
      "env": {
        "MSSQL_MAINDB_SERVER": "your_server_name",
        "MSSQL_MAINDB_PORT": "1433",
        "MSSQL_MAINDB_USER": "your_username",
        "MSSQL_MAINDB_PASSWORD": "your_password",
        "MSSQL_MAINDB_DATABASE": "main_database",
        "MSSQL_MAINDB_ENCRYPT": "true",
        "MSSQL_MAINDB_TRUST_SERVER_CERTIFICATE": "false",

        "MSSQL_REPORTINGDB_SERVER": "your_server_name",
        "MSSQL_REPORTINGDB_PORT": "1433",
        "MSSQL_REPORTINGDB_USER": "your_username",
        "MSSQL_REPORTINGDB_PASSWORD": "your_password",
        "MSSQL_REPORTINGDB_DATABASE": "reporting_database",
        "MSSQL_REPORTINGDB_ENCRYPT": "true",
        "MSSQL_REPORTINGDB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

You can also install this package locally instead of using npx:

npm install --save-dev mssql-mcp-node

MCP Tools

When using the MCP server through the Claude Desktop or VS Code integration, you can use the following tools:

execute_sql

Execute an SQL query against the connected database(s).

Input:

{
  "query": "SELECT TOP 10 * FROM YourTable",
  "dbKey": "maindb" // Optional in both modes, defaults to first available database
}

Example usage in Claude Desktop:

I'd like to see data from the YourTable table in the main database.

get_table_schema

Retrieve the schema information for a specific table.

Input:

{
  "table": "YourTable",
  "dbKey": "reportingdb" // Optional in both modes, defaults to first available database
}

Example usage in Claude Desktop:

What columns are in the YourTable table in the reporting database?

list_databases

List all configured databases and their connection information.

Input:

{}


// No parameters required

Example usage in Claude Desktop:

Show me all the available databases in the configuration.

Testing

A Postman collection is provided in the postman/ folder for testing the HTTP endpoints of the Express server. Here are curl examples to test both single and multi-database configurations:

Testing with curl

Test all four combinations (single/multi-database mode × maindb/reportingdb):

Single-Database Mode (with one database only)

# List Resources
curl -X GET "http://localhost:3000/resources"

# Execute SQL Query
curl -X POST "http://localhost:3000/execute-sql" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT TOP 10 * FROM Users"}'

# Get Table Schema
curl -X POST "http://localhost:3000/get-table-schema" \
  -H "Content-Type: application/json" \
  -d '{"table": "Users"}'

Multi-Database Mode

# List Resources from maindb
curl -X GET "http://localhost:3000/resources?dbKey=maindb"

# List Resources from reportingdb
curl -X GET "http://localhost:3000/resources?dbKey=reportingdb"

# Execute SQL Query on maindb
curl -X POST "http://localhost:3000/execute-sql" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT TOP 10 * FROM Users", "dbKey": "maindb"}'

# Execute SQL Query on reportingdb
curl -X POST "http://localhost:3000/execute-sql" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT TOP 10 * FROM SalesReport", "dbKey": "reportingdb"}'

Schema Validation

This project uses Zod for schema validation throughout the application to ensure data integrity and provide more robust error handling.

Implemented Schema Validations

• SQL Query Validation: Validates that SQL queries are non-empty strings within a reasonable length limit.
• Table Name Validation: Ensures table names follow proper naming conventions (alphanumeric characters and underscores only).
• Resource URI Validation: Validates that resource URIs follow the expected format (mssql://<table_name>/data).
• Database Configuration Validation: Ensures that all required database configuration parameters are provided and properly formatted.
• Safety Checks: SQL queries are validated against a list of potentially dangerous operations for additional security.

Security Enhancements

1. SQL Injection Protection: Uses parameterized queries wherever possible.
2. Query Safety Validation: Checks for potentially dangerous SQL operations (DROP, TRUNCATE, etc.)
3. Enhanced Error Messages: Provides detailed but safe error messages that don't expose sensitive details.
4. Configuration Validation: Validates all configuration parameters before attempting to connect.

Project Structure

mssql-mcp-node/
├── .editorconfig
├── .env                  # Environment variables file (not committed)
├── .env.example          # Sample environment configuration (both modes)
├── .gitignore
├── .markdownlint.json
├── .prettierignore
├── .prettierrc
├── eslint.config.mjs
├── LICENSE
├── node_modules/
├── package-lock.json
├── package.json
├── postman/              # Postman collection for API testing
├── README.md
└── src/
    ├── config/
    │   ├── dbConfig.js    # Database connection handling module
    │   └── index.js       # Configuration auto-detection module
    ├── express.js         # Entry point for Express server (HTTP mode)
    ├── index.js           # MCP server entry point (STDIO mode via SDK)
    ├── modules/           # Core modules (resource and tool management)
    │   ├── resources.js   # Functions for listing resources and reading table data
    │   └── tools.js       # Functions for SQL operations
    ├── server/            # Express server setup (used by express.js)
    │   └── index.js       # Express server implementation
    └── validation/        # Schema validation module using Zod
        └── index.js       # Schema definitions and validation functions

License

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

Author

Mihai-Nicolae Dulgheru [email protected]