Mssql Onpremises

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

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.