Microsoft SQL Server MCP Server (MSSQL)

🚀 MS SQL MCP Server Documentation

MS SQL MCP (Microsoft SQL Machine Learning Platform) Server is a modular and extensible tool designed to assist users in exploring and operating Microsoft SQL databases. It simplifies the process of interacting with SQL databases by offering various functions, such as query execution, table structure viewing, and database discovery.

✨ Features

Core Components

database.mjs - Database Connection

• Manages the SQL Server connection pool.
• Provides query execution with retry logic and error handling.
• Handles database connections, transactions, and configurations.
• Includes tools for cleaning SQL statements and formatting error messages.

tools.mjs - Tool Registration

• Registers all database tools with the MCP server.
• Implements tool validation and parameter checking.
• Offers core functions for SQL queries, table structure viewing, and database discovery.
• Maps tool calls to database operations.

resources.mjs - Database Resources

• Exposes database metadata through resource endpoints.
• Provides schema information, table lists, and procedure documentation.
• Formats database structure information for AI usage.
• Contains utilities for discovering databases.

pagination.mjs - Result Navigation

• Implements cursor-based pagination for handling large result sets.
• Generates cursors for the next/previous page.
• Converts SQL queries into a pagination-supported format.
• Handles the OFFSET/FETCH pagination syntax in SQL Server.

errors.mjs - Error Handling

• Defines custom error types for different failure scenarios.
• Implements JSON-RPC error formatting.
• Provides highly readable error messages.
• Includes global error handling middleware.

logger.mjs - Logging System

• Configures logging using Winston, supporting multiple transports.
• Provides context-aware logging capabilities.
• Handles log rotation and formatting.
• Captures uncaught exceptions and unhandled rejections.

📦 Installation

Environment Configuration

The .env file controls how the MS SQL MCP server connects to the database and how it operates. Here is a detailed explanation of each setting:

# Database connection settings
DB_USER=your_username           # SQL Server username
DB_PASSWORD=your_password       # SQL Server password
DB_SERVER=your_server_name      # Server hostname or IP address (e.g., localhost, 10.0.0.1, myserver.database.windows.net)
DB_DATABASE=your_database_name  # Name of the database to connect to

# Server configuration
PORT=3333                       # Port on which the HTTP/SSE server listens
TRANSPORT=stdio                 # Connection method: 'stdio' (for Claude Desktop) or 'sse' (for network connections)
SERVER_URL=http://localhost:3333 # Base URL when using SSE transport (must match the PORT setting)

# Advanced settings
DEBUG=false                     # Set to 'true' to enable detailed logging (useful for debugging)
QUERY_RESULTS_PATH=/path/to/query_results  # Folder path to store query results

Connection Type Explanation

STDIO Transport

• Used when directly connecting with Claude Desktop.
• Communicates via standard input/output streams.
• Set TRANSPORT=stdio in the .env file.
• Start the server using the command npm start.

HTTP/SSE Transport

• Used when connecting over the network (e.g., with Cursor).
• Supports the Server-Sent Events (SSE) HTTP protocol.
• Set TRANSPORT=sse in the .env file.
• Start the server using the command npm run sse.

📚 Documentation

Database Discovery

MS SQL MCP Server offers a powerful database discovery feature that allows users to easily browse and analyze the database structure. The main functions are as follows:

1. Table Structure Viewing: Supports displaying column information of tables in a tree view or tabular form.
2. Data Preview: Allows users to select a specific number of rows for quick data analysis.
3. Metadata Extraction: Provides detailed metadata at the database, table, and column levels.

💻 Usage Examples

Connecting to SQL Server

const { Database } = require('mssql-mcp');

async function connect() {
    const db = new Database({
        config: {
            user: 'your_username',
            password: 'your_password',
            server: 'localhost',
            database: 'your_database'
        }
    });

    try {
        await db.connect();
        console.log('Connected to SQL Server.');
    } catch (error) {
        console.error('Failed to connect to the database:', error);
    }
}

connect();

Executing a Query

async function queryExample() {
    const db = new Database({
        config: {
            user: 'your_username',
            password: 'your_password',
            server: 'localhost',
            database: 'your_database'
        }
    });

    try {
        await db.connect();
        const result = await db.query('SELECT TOP 5 * FROM YourTable');
        console.log('Query results:', result);
    } catch (error) {
        console.error('Error executing the query:', error);
    } finally {
        await db.disconnect();
    }
}

queryExample();

Pagination Handling

async function paginatedQuery() {
    const db = new Database({
        config: {
            user: 'your_username',
            password: 'your_password',
            server: 'localhost',
            database: 'your_database'
        },
        options: {
            pageNumber: 2,
            pageSize: 10
        }
    });

    try {
        await db.connect();
        const result = await db.query('SELECT * FROM YourTable ORDER BY Id');
        console.log('Paginated results:', result);
    } catch (error) {
        console.error('Error executing the paginated query:', error);
    } finally {
        await db.disconnect();
    }
}

paginatedQuery();

🔧 Technical Details

Logging

MS SQL MCP Server supports detailed logging to assist users in debugging and monitoring its running status. You can enable or disable detailed log output by setting the DEBUG environment variable.

# Enable debug mode
DEBUG=true npm start

# Disable debug mode (default)
npm start

Error Handling

When an error occurs, MS SQL MCP Server provides detailed error information to help users quickly locate and resolve issues. Here are some common error scenarios and their solutions:

1. Connection Errors

• Cause: Incorrect database credentials or network issues.
• Solution: Check if the username, password, and server address are correct, and ensure a stable network connection.

2. Query Errors

• Cause: Syntax errors or insufficient permissions.
• Solution: Verify the syntax of the SQL query and confirm that the user has the necessary permissions to execute the query.

3. Resource Limitations

• Cause: Exhausted database resources (e.g., insufficient memory).
• Solution: Optimize query performance, reduce the number of concurrent queries, or increase the resources of the database server.

📄 Summary

MS SQL MCP Server is a powerful and flexible tool that enables users to interact with Microsoft SQL databases efficiently. With its modular design and rich feature set, users can easily perform tasks ranging from basic queries to complex data analysis.