MSSQL Model Context Protocol Server

1.2.2 · active · verified Tue Apr 21

The `mssql-mcp-server` package provides a Model Context Protocol (MCP) server specifically designed for Microsoft SQL Server, enabling comprehensive database schema exploration and modernization planning. Currently at version 1.2.2, this server facilitates detailed analysis of database structures, including tables, views, stored procedures, triggers, and functions. It leverages the `tedious` library for pure JavaScript connectivity, supporting crucial features like Windows Authentication (NTLM), which necessitates explicit domain credentials through environment variables for secure, non-interactive background processes. Key differentiators include its extensive suite of 29 analytical tools, capabilities for extracting full SQL source code from stored procedures for business logic analysis, and a focus on assisting with modernization efforts for applications like Classic ASP to modern .NET/Angular architectures. While a specific release cadence isn't detailed, the project appears actively maintained with recent updates focused on enhancing core analysis capabilities.

Common errors

```
Login failed for user 'DOMAIN\Username'.
```
cause Incorrect or missing Windows Authentication (NTLM) credentials (username, password, or domain) provided via environment variables, or the specified user lacks database permissions.

fix Verify `SQL_SERVER_USER`, `SQL_SERVER_PASSWORD`, and `SQL_SERVER_DOMAIN` environment variables match valid domain credentials with access to the SQL Server instance. Check SQL Server logs for detailed login failure reasons.
```
ConnectionError: Failed to connect to localhost:1433 - Could not connect (Error: connect ECONNREFUSED ::1:1433)
```
cause The SQL Server instance is unreachable due to incorrect host/port, network firewall blocking the connection, or the SQL Server service not running.

fix Confirm `SQL_SERVER_HOST` and `SQL_SERVER_PORT` environment variables are correct. Check network connectivity, firewall rules (both client and server), and ensure the SQL Server instance is running and configured to accept remote connections.
```
TypeError: Cannot read properties of undefined (reading 'connections')
```
cause The `MssqlMcpServer` constructor was called without the required `connections` object in its options, or the options object itself was `undefined`.

fix Ensure the `MssqlMcpServer` is initialized with a valid `MssqlMcpServerOptions` object, specifically providing at least one connection configuration under the `connections` property.

Warnings

gotcha When using Windows Authentication (NTLM), the server requires explicit environment variables for `SQL_SERVER_USER`, `SQL_SERVER_PASSWORD`, and `SQL_SERVER_DOMAIN`. Running as a background process, it cannot access the current user's interactive Windows session for authentication. Incorrect or missing credentials will lead to connection failures.
Fix: Ensure `SQL_SERVER_USER`, `SQL_SERVER_PASSWORD`, and `SQL_SERVER_DOMAIN` environment variables are correctly set for the process running the MCP server.
gotcha For development environments, `trustServerCertificate: true` might be used for convenience. In production, this should generally be `false` and a trusted certificate explicitly provided or validated to prevent 'man-in-the-middle' attacks.
Fix: Review `trustServerCertificate` option in `MssqlMcpServerOptions`. For production, ensure valid SSL certificates are used and configured, setting `trustServerCertificate: false`.
gotcha Certain database tools, such as `execute_query` and `sample_data`, have built-in safety limits (e.g., read-only, max 20 rows, max 100 rows respectively) to prevent accidental data modification or excessive resource consumption. Attempting to bypass these or expecting larger result sets may lead to unexpected behavior.
Fix: Adhere to the documented limitations of specific tools. For larger data extraction, consider direct SQL client access or alternative tools. Use `execute_query` only for read-only queries.

Install

npm install mssql-mcp-server npm
yarn add mssql-mcp-server yarn
pnpm add mssql-mcp-server pnpm

Imports

MssqlMcpServer
wrong:
```
const MssqlMcpServer = require('mssql-mcp-server');
```
correct:
```
import { MssqlMcpServer } from 'mssql-mcp-server';
```
The primary class to instantiate and configure the MCP server. Package ships with TypeScript types, favoring ESM imports.
MssqlMcpServerOptions
wrong:
```
import { MssqlMcpServerOptions } from 'mssql-mcp-server';
```
correct:
```
import type { MssqlMcpServerOptions } from 'mssql-mcp-server';
```
Type definition for the server configuration options. Use `type` import for clarity and bundler optimization.

Quickstart

This quickstart demonstrates how to programmatically initialize and start the `mssql-mcp-server` using environment variables for SQL Server connection details, including NTLM authentication. It then shows a hypothetical HTTP POST request to the running server to execute the `test_connection` tool, simulating how an external client or AI agent would interact with the server's API.

import { MssqlMcpServer } from 'mssql-mcp-server';
import * as dotenv from 'dotenv'; // For local development, install with `npm install dotenv`

dotenv.config(); // Load environment variables from .env file

// Configure SQL Server connection details using environment variables
const SQL_SERVER_HOST = process.env.SQL_SERVER_HOST ?? 'localhost';
const SQL_SERVER_PORT = parseInt(process.env.SQL_SERVER_PORT ?? '1433', 10);
const SQL_SERVER_USER = process.env.SQL_SERVER_USER ?? ''; // For NTLM Windows Authentication
const SQL_SERVER_PASSWORD = process.env.SQL_SERVER_PASSWORD ?? ''; // For NTLM
const SQL_SERVER_DOMAIN = process.env.SQL_SERVER_DOMAIN ?? ''; // For NTLM
const SQL_SERVER_DATABASE = process.env.SQL_SERVER_DATABASE ?? 'master';
const SERVER_PORT = parseInt(process.env.MCP_SERVER_PORT ?? '3000', 10); // Port for the MCP server itself

async function startMcpServer() {
  if (!SQL_SERVER_USER || !SQL_SERVER_PASSWORD || !SQL_SERVER_DOMAIN) {
    console.warn("WARNING: NTLM credentials (SQL_SERVER_USER, SQL_SERVER_PASSWORD, SQL_SERVER_DOMAIN) are not fully set. Windows Authentication might fail.");
  }

  try {
    // Instantiate the MCP server with a default connection
    const server = new MssqlMcpServer({
      connections: {
        default: {
          server: SQL_SERVER_HOST,
          port: SQL_SERVER_PORT,
          userName: SQL_SERVER_USER,
          password: SQL_SERVER_PASSWORD,
          domain: SQL_SERVER_DOMAIN,
          options: {
            database: SQL_SERVER_DATABASE,
            encrypt: true, // Recommended for Azure SQL DB and modern instances
            trustServerCertificate: true // Set to false in production if using trusted certificates
          }
        }
      },
      serverPort: SERVER_PORT
    });

    await server.start();
    console.log(`MSSQL MCP Server started successfully on port ${SERVER_PORT}`);

    // --- Simulate an external client (e.g., an AI agent) calling a tool ---
    console.log('\n--- Simulating API call to test_connection tool ---');
    const toolCallResponse = await fetch(`http://localhost:${SERVER_PORT}/api/tool`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        toolName: 'test_connection',
        toolArgs: { connectionName: 'default' }
      })
    });

    const result = await toolCallResponse.json();
    console.log('Result from test_connection:', result);

  } catch (error) {
    console.error('Failed to start MSSQL MCP Server or call tool:', error);
    process.exit(1);
  }
}

startMcpServer();

view raw JSON →