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*

Related MCP server: MSSQL Database MCP Server

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"
              }
          }
      }
  }
}

4. Restart VS Code

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

5. 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 ATTRIBUTION.md 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 LICENSE file for details

🙏 Acknowledgments

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

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