SQL Server MCP Service
A secure and robust Model Context Protocol (MCP) service for executing SQL Server queries via MCP protocol. This service provides a safe way to interact with SQL Server databases while preventing destructive operations and SQL injection attacks.
🚀 Features
- 🔒 Secure Query Execution: Built-in protection against SQL injection and destructive operations
- 📊 Schema Discovery: Get table, function, and procedure schemas and database information
- 🛠️ Multiple Tools: 9 specialized tools for different database operations
- ⚡ High Performance: Connection pooling for efficient database operations
- 🔧 TypeScript Support: Full TypeScript implementation with type safety
- 🧪 Comprehensive Testing: Jest-based test suite for reliability
🛠️ Available Tools
1. execute_query
Executes safe SQL queries with parameter support.
Parameters:
query(string, required): The SQL query to executeparameters(object, optional): Query parameters for prepared statements
Example:
{
"query": "SELECT * FROM Users WHERE Status = @status",
"parameters": {
"status": "active"
}
}
2. get_table_schema
Retrieves detailed column information for a specific table.
Parameters:
tableName(string, required): Name of the tableschemaName(string, optional): Schema name (default: "dbo")
Example:
{
"tableName": "Users",
"schemaName": "dbo"
}
3. list_tables
Lists all tables in the database with optional schema filtering.
Parameters:
schemaName(string, optional): Filter tables by specific schema
Example:
{
"schemaName": "dbo"
}
4. get_database_info
Retrieves general database information (name, version, edition, etc.).
Parameters: None
5. list_procedures
Lists all stored procedures in the database, optionally filtered by schema.
Parameters:
schemaName(string, optional): Filter procedures by schema
Example:
{
"schemaName": "dbo"
}
6. list_functions
Lists all functions (scalar and table-valued) in the database, optionally filtered by schema and function type.
Parameters:
schemaName(string, optional): Filter functions by schemafunctionType(string, optional): 'SCALAR' or 'TABLE'
Example:
{
"schemaName": "dbo",
"functionType": "SCALAR"
}
7. get_procedure_schema
Gets the schema and parameters of a specific stored procedure.
Parameters:
procedureName(string, required): Name of the procedureschemaName(string, optional): Schema name (default: "dbo")
Example:
{
"procedureName": "MyProcedure",
"schemaName": "dbo"
}
8. get_function_schema
Gets the schema and parameters of a specific function.
Parameters:
functionName(string, required): Name of the functionschemaName(string, optional): Schema name (default: "dbo")
Example:
{
"functionName": "MyFunction",
"schemaName": "dbo"
}
9. execute_procedure
Executes a stored procedure with parameters.
Parameters:
procedureName(string, required): Name of the procedureparameters(object, optional): Procedure parametersschemaName(string, optional): Schema name (default: "dbo")
Example:
{
"procedureName": "MyProcedure",
"parameters": {
"param1": 123,
"param2": "abc"
},
"schemaName": "dbo"
}
📦 Installation
Prerequisites
- Node.js >= 20.0.0
- SQL Server instance
- pnpm (recommended)
Install Dependencies
pnpm install
⚙️ Configuration
- Copy Environment Template
cp .env.example .env
- Configure Environment Variables
| Variable | Description | Default |
|---|---|---|
HTTP_PORT | HTTP server port | 3333 |
NODE_ENV | Node environment (development/production) | development |
ORIGIN | Allowed CORS origins (comma-separated) | - |
SQL_SERVER | SQL Server hostname/IP | localhost |
SQL_DATABASE | Database name | master |
SQL_USER | Database username | - |
SQL_PASSWORD | Database password | - |
SQL_PORT | SQL Server port | 1433 |
SQL_ENCRYPT | Enable encryption | true |
SQL_TRUST_CERT | Trust server certificate | false |
Example .env:
HTTP_PORT=3333
NODE_ENV=development
ORIGIN=http://localhost:3000,http://example.com
SQL_SERVER=localhost
SQL_DATABASE=master
SQL_USER=sa
SQL_PASSWORD=YourSecurePassword123!
SQL_PORT=1433
SQL_ENCRYPT=true
SQL_TRUST_CERT=false
🚀 Usage
Start HTTP Server (Recommended)
pnpm run dev:http
# or
pnpm run start:http
Build for Production
pnpm run build
Running Tests
pnpm run test
🖥️ Desktop App Integration
To integrate this server with a desktop app, add the following to your app's server configuration:
Using Node.js directly:
{
"mcpServers": {
"sqlserver": {
"command": "node",
"args": [
"{ABSOLUTE PATH TO FILE HERE}/dist/cli.js"
]
}
}
}
Using npx:
{
"mcpServers": {
"sqlserver": {
"command": "npx",
"args": [
"mcp-mssql-server"
]
}
}
}
Note: Replace {ABSOLUTE PATH TO FILE HERE} with the actual absolute path to your project's dist/cli.js file.
🤖 OpenAI Integration
To use this MCP server with OpenAI's API, you can integrate it using the MCP protocol. Here's an example:
import OpenAI from "openai";
const client = new OpenAI();
const resp = await client.responses.create({
model: "gpt-5",
tools: [
{
type: "mcp",
server_label: "mssql",
server_description: "A SQL Server MCP server for executing safe database queries and schema discovery.",
server_url: "http://localhost:3333/mcp",
require_approval: "never",
},
],
input: "Show me all tables in the database",
});
console.log(resp.output_text);
Note: Make sure your HTTP server is running on the specified port before making requests to OpenAI.
🔒 Security Features
Query Validation
The service automatically blocks potentially destructive operations:
- ❌
DROP TABLE - ❌
DELETE FROM - ❌
TRUNCATE TABLE - ❌
INSERT INTO - ❌
UPDATE - ❌
CREATE TABLE - ❌
ALTER TABLE - ❌ Stored procedures (
sp_,xp_) - ❌ SQL injection patterns
- ❌ Comments (
--,/* */)
Allowed Operations
- ✅
SELECTqueries - ✅
WITHclauses (CTEs) - ✅
SHOWcommands - ✅
DESCRIBEcommands - ✅
EXPLAINcommands - ✅ Safe
EXEC/EXECUTEfor procedures/functions
Parameter Sanitization
All query and procedure parameters are automatically sanitized to prevent injection attacks.
🧪 Testing
The project includes comprehensive tests for schema validation and core functionality:
# Run all tests
pnpm run test
# Run tests in watch mode
pnpm run test -- --watch
# Run tests with coverage
pnpm run test -- --coverage
📝 License
This project is licensed under the MIT License - see the LICENSE file for details.
👨💻 Author
Vinicius de Souza Santos
- Email: viniciuskt0@gmail.com
- GitHub: @vini-cius
🙏 Acknowledgments
- Model Context Protocol for the MCP specification
- mssql for SQL Server connectivity
- Zod for runtime type validation
⭐ If this project helps you, please give it a star!