RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/mihai-dulgheru/mssql-mcp-node below:

GitHub - mihai-dulgheru/mssql-mcp-node

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.

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

Single-database mode - Uses simple MSSQL_* variables to connect to one database
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 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

# 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 Launch Config Environment Setup Behavior mssql-mcp-node-single Single-Database variables Operates in single-DB mode with one database mssql-mcp-node-multi Multi-Database variables Operates in multi-DB mode with multiple databases

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.

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

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

Clone the Repository

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

Install Dependencies
Configure Environment Variables

Copy the example environment configuration and update as needed:

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)

There are two modes of operation:

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:

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

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

Start Express Mode:

This runs the Express server defined in src/express.js.
Development Mode with Auto-Reload:

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:

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:

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:

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).

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

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

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.

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

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:

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"}'

# 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"}'

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.

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

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

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

Mihai-Nicolae Dulgheru mihai.dulgheru18@gmail.com

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4