Overview

This MCP (Model Context Protocol) server module provides a configurable Microsoft SQL Server (MSSQL) integration for MCP-compatible systems. It connects MSSQL instances to your MCP server and exposes controlled, auditable database access and on-demand schema inspection. The goal is to let language models and other agents reason about a database schema safely and to run constrained queries without broad direct access to production data.

The integration is useful when you need fine-grained access controls (table/column allowlists, deny rules, read-only modes), secure connection handling (connection strings and pools), and a mechanism to fetch the current database schema on demand so an LLM can construct valid SQL. It is designed for developers who want to couple LLMs or automated agents with MSSQL while minimizing data exposure and maintaining operational control.

Features

Fine-grained access control: table- and column-level allowlists/denylists and read-only modes
On-demand schema inspection: fetch tables, columns, types and indexes when needed
Configurable connection management: support for connection strings, pooling, and timeouts
Tool definitions for MCP: expose only defined “tools” (query, schema inspector) to clients
Audit-friendly: logs and configurable request tracing for database interactions
Runtime toggles: enable/disable schema inspection and query execution per connection

Installation / Configuration

Prerequisites: Node.js (>= 16) and network access to your MSSQL instance.

Clone and install:

git clone https://github.com/aekanun2020/mcp-server.git
cd mcp-server
npm install

Basic environment variables (example):

# .env
MCP_PORT=8080
MSSQL_CONNECTION_STRING="Server=sql.example.com;Database=mydb;User Id=myuser;Password=mypassword;Encrypt=true;"
MSSQL_POOL_MAX=10

Example connector configuration (mssql-connector.json):

{
  "id": "mssql-main",
  "type": "mssql",
  "connectionStringEnv": "MSSQL_CONNECTION_STRING",
  "pool": {
    "max": 10,
    "idleTimeoutMillis": 30000
  },
  "access": {
    "readOnly": true,
    "tables": {
      "allow": ["customers", "orders", "products"],
      "deny": ["sys.*", "audit_logs"]
    },
    "columns": {
      "customers": { "deny": ["ssn", "credit_card_number"] }
    }
  },
  "schema": {
    "enabled": true,
    "inspectOnDemand": true,
    "cacheSeconds": 3600
  },
  "tools": [
    { "name": "mssql_schema_inspector", "type": "schema" },
    { "name": "mssql_query_executor", "type": "query" }
  ]
}

Start the MCP server:

npm run start -- --config ./mssql-connector.json
# or using environment variables
MCP_PORT=8080 node server.js

You can also run in Docker by building the project and passing env vars or mounting config files into the container.

Configuration Reference

Key	Type	Description
id	string	Connector identifier used by MCP tools
type	string	Connector type (mssql)
connectionStringEnv	string	Environment variable name containing the MSSQL connection string
pool	object	Connection pool settings (max, idleTimeoutMillis)
access.readOnly	boolean	If true, blocks write queries
access.tables.allow	array	Whitelisted table patterns/names
access.tables.deny	array	Blacklisted table patterns/names
access.columns	object	Per-table column-level allow/deny rules
schema.enabled	boolean	Enable schema inspection support
schema.inspectOnDemand	boolean	Inspect schema only when requested
schema.cacheSeconds	number	Schema cache lifetime in seconds
tools	array	Tools exposed to MCP clients (query, schema, etc.)

Available Resources

Schema Inspector tool: retrieves tables, columns, data types, primary keys and indexes.
Query Executor tool: runs SQL queries subject to configured access controls and read-only restrictions.
Repository: https://github.com/aekanun2020/mcp-server/ — contains source, issues, and example configs.
Logging and audit hooks: configure request/response logging to external sinks for compliance.

Use Cases

LLM-assisted query generation
- Use the schema inspector tool to fetch the current schema so a model can generate valid SQL. The query executor runs the generated SQL against allowed tables only and enforces read-only mode for safety.
Analytics sandboxing
- Expose only specific tables and columns to analytics agents. Deny sensitive columns (SSNs, credit cards) while allowing aggregated SELECTs for reporting.
On-demand schema validation in CI
- During deployment tests, fetch schema snapshots to validate migrations and ensure client tools still match the database layout.
Secure operational tooling
- Build chatops tools that can answer questions like “Which columns exist on orders?” without granting ad-hoc query permissions.

Best Practices

Keep the connection string in environment variables or a secrets manager; avoid committing secrets to source control.
Prefer allowlists over denylists for tighter security.
Enable schema caching for performance but invalidate on migrations.
Use readOnly mode for any MCP clients that should not perform writes.

If you need specific examples for your environment (Docker Compose, Kubernetes secrets, or advanced access rules), consult the repository and its examples on GitHub.

MSSQL MCP Server: Configurable Access & Schema Inspection