MSSQL Model Context Protocol (MCP) Server

!npm version
!MIT License

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__*) 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

$3

``ini


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
`

$3

`ini


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__*:

`ini


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.

$3

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

$3

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

$3

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

$3

1. Clone the Repository

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

2. Install Dependencies

`bash
npm install
`

3. Configure Environment Variables

Copy the example environment configuration and update as needed:

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

$3

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:

`bash
npm start
`

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

$3

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

- Start Express Mode:

`bash
npm run start:express
`

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

- Development Mode with Auto-Reload:

`bash
npm run dev:express
`

$3

- List Resources (Tables):

`http
GET /resources?dbKey=maindb
`

Example Response:

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

- Get Resource Data:

`http
GET /resource?uri=mssql://dbo.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:

`http
GET /tools
`

Example Response:

`json
[
{
"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 (supports schema.table format, e.g., 'dbo.YourTable')"
},
"dbKey": {
"type": "string",
"description": "The database key to use (e.g., 'maindb', 'reportingdb', etc.). Optional in single-db mode."
}
},
"required": ["table"]
}
}
]
`

- Execute SQL Query:

`http
POST /execute-sql
`

Request Body:

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

Response Example for SELECT queries:

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

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

- Get Table Schema:

`http
POST /get-table-schema
`

Request Body:

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

For tables in the default schema, you can omit the schema prefix:

`json
{
"table": "YourTable",
"dbKey": "reportingdb"
}
`

Response Example:

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

$3

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

#### Single-Database Configuration

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

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

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

$3

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

Input:

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

$3

Retrieve the schema information for a specific table.

Input:

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

$3

List all configured databases and their connection information.

Input:

`json
{}


// 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:

$3

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

#### Single-Database Mode (with one database only)

`bash


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

`bash


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.

$3

- 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:///data or mssql://./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.

$3

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