mssql-mcp-onpremises

dnldelarosa/mssql-mcp-onpremises

3.2

If you are the rightful owner of mssql-mcp-onpremises and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

This is a Model Context Protocol (MCP) server designed for on-premises SQL Server databases, enabling AI assistants to interact with local SQL Server instances using SQL Server authentication.

MSSQL On-Premises MCP Server

What is this? 🤔

This is a Model Context Protocol (MCP) server specifically designed for on-premises SQL Server databases. It enables AI assistants like GitHub Copilot and Claude to interact directly with your local SQL Server instances using traditional SQL Server authentication.

Key Difference: This version has been adapted from the original Azure SQL samples to work with on-premises SQL Server installations, removing Azure Active Directory dependencies and cloud-specific authentication requirements.

Quick Example

You: "Show me all customers from New York"
GitHub Copilot: *connects to your local SQL Server and returns the results*

How Does It Work? 🛠️

This server leverages the Model Context Protocol (MCP), a versatile framework that acts as a universal translator between AI models and databases. It supports multiple AI assistants including GitHub Copilot and Claude Desktop.

What Can It Do? 📊

  • Connect to on-premises SQL Server instances using SQL Server authentication
  • Run queries using natural language - no SQL knowledge required
  • Create, read, update, and delete data securely
  • Manage database schema (tables, indexes)
  • Secure connection handling with certificate trust options
  • Real-time data interaction with local databases

Quick Start 🚀

Prerequisites

  • Node.js 16+ installed on your system
  • SQL Server instance (local, on-premises, or Docker)
  • SQL Server Authentication enabled on your SQL Server instance
  • Claude Desktop or GitHub Copilot

Installation

  1. Clone the Repository

    git clone https://github.com/dnldelarosa/mssql-mcp-onpremises.git
cd mssql-mcp-onpremises

  2. Install Dependencies

    npm install

  3. Build the Project

    npm run build

Configuration Setup

Option 1: GitHub Copilot Setup

  1. Install GitHub Copilot Extension

    • Open VS Code
    • Go to Extensions (Ctrl+Shift+X)
    • Search for "GitHub Copilot" and install the official extension.

  2. Create MCP Configuration File

    • Create a .vscode/mcp.json file in your workspace
    • Add the following configuration:
    {
  "servers": {
    "mssql-onpremises": {
       "type": "stdio",
       "command": "node",
       "args": ["C:/path/to/your/project/dist/index.js"],
       "env": {
         "SERVER_NAME": "localhost",
         "DATABASE_NAME": "your-database-name",
         "DB_USER": "your-sql-username",
         "DB_PASSWORD": "your-sql-password",
         "READONLY": "false",
         "TRUST_SERVER_CERTIFICATE": "true"
       }
     }
   }
}

    Note: Ensure the path to dist/index.js is correct.

  3. Alternative: User Level MCP Configuration

    • Open the Command Palette (Ctrl+Shift+P)
    • Type MCP:... (JSON) and press Enter.
    • Add the following configuration to your user's mcp.json file:
 {
  "mcp": {
      "servers": {
          "mssql-onpremises": {
              "command": "node",
              "args": ["C:/path/to/your/project/dist/index.js"],
              "env": {
                "SERVER_NAME": "localhost",
                "DATABASE_NAME": "your-database-name",
                "DB_USER": "your-sql-username",
                "DB_PASSWORD": "your-sql-password",
                "READONLY": "false",
                "TRUST_SERVER_CERTIFICATE": "true"
              }
          }
      }
  }
}

  1. Restart VS Code

    • Close and reopen VS Code for the changes to take effect

  2. Verify MCP Server

    • Open Command Palette (Ctrl+Shift+P)
    • Run "MCP: List Servers" to verify your server is configured
    • You should see "mssql-onpremises" in the list of available servers

Option 2: Claude Desktop Setup

  1. Open Claude Desktop Settings

    • Navigate to File → Settings → Developer → Edit Config
    • Open the claude_desktop_config file

  2. Add MCP Server Configuration Replace the content with the configuration below, updating the path and credentials:

    {
  "mcpServers": {
    "mssql-onpremises": {
      "command": "node",
      "args": ["C:/path/to/your/project/dist/index.js"],
      "env": {
        "SERVER_NAME": "localhost",
        "DATABASE_NAME": "your-database-name",
        "DB_USER": "your-sql-username",
        "DB_PASSWORD": "your-sql-password",
        "READONLY": "false",
        "TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

  3. Restart Claude Desktop

    • Close and reopen Claude Desktop for the changes to take effect

Configuration Parameters

  • SERVER_NAME: Your SQL Server instance (e.g., localhost, 192.168.1.100, or MYSERVER\SQLEXPRESS)
  • DATABASE_NAME: Your database name
  • DB_USER: SQL Server username for authentication
  • DB_PASSWORD: SQL Server password for authentication
  • READONLY: Set to "true" to restrict to read-only operations, "false" for full access
  • TRUST_SERVER_CERTIFICATE: Set to "true" to trust self-signed certificates (recommended for local development)
  • CONNECTION_TIMEOUT: (Optional) Connection timeout in seconds. Defaults to 30 if not set
  • Path: Update the path in args to point to your actual project location

Sample Configurations

You can find sample configuration files in the src/samples/ folder:

  • claude_desktop_config.json - For Claude Desktop
  • vscode_agent_config.json - For GitHub Copilot

Usage Examples

Once configured, you can interact with your database using natural language:

  • "Show me all users from New York"
  • "Create a new table called products with columns for id, name, and price"
  • "Update all pending orders to completed status"
  • "List all tables in the database"

Security Notes

  • The server requires a WHERE clause for read operations to prevent accidental full table scans
  • Update operations require explicit WHERE clauses for security
  • Set READONLY: "true" in production environments if you only need read access

🛠️ Troubleshooting

Common Issues

Connection Failed

  • Ensure SQL Server Authentication is enabled
  • Verify firewall settings allow connections on SQL Server port (default 1433)
  • Check if TRUST_SERVER_CERTIFICATE is set to "true" for self-signed certificates

"Login failed for user" Error

  • Verify username and password are correct
  • Ensure the SQL Server user has appropriate database permissions
  • Check if the user account is not locked or disabled

MCP Server Not Listed

  • Restart your AI assistant (Claude Desktop / GitHub Copilot)
  • Verify the path to dist/index.js is correct
  • Check the MCP configuration syntax for any JSON errors

Build Errors

  • Ensure Node.js 16+ is installed
  • Delete node_modules and run npm install again
  • Check for TypeScript compilation errors

Getting Help

  • Check the Issues page
  • Review the for technical details
  • See original Microsoft samples for additional context

You should now have successfully configured the MCP server for on-premises SQL Server with your preferred AI assistant. This setup allows you to seamlessly interact with your local SQL Server through natural language queries!

📄 License

MIT License - See file for details

🙏 Acknowledgments

Based on the original work from Microsoft SQL-AI-samples, modified to support on-premises SQL Server installations.

See for detailed information about the original work and modifications made for on-premises support.