mcp-sqlserver

# MCP SQL Server A read-only Model Context Protocol (MCP) server for Microsoft SQL Server that enables AI agents to safely explore and query SQL Server databases. ## Quick Start ### Step 1: Install the Package ```bash # Global installation (recommended) npm install -g @bilims/mcp-sqlserver # Verify installation mcp-sqlserver --version ``` ### Step 2: Configure Your SQL Server Connection Choose your SQL Server type and follow the configuration: #### Azure SQL Database ```bash export SQLSERVER_HOST="your-server.database.windows.net" export SQLSERVER_USER="your-username" export SQLSERVER_PASSWORD="your-password" export SQLSERVER_DATABASE="your-database" export SQLSERVER_ENCRYPT="true" export SQLSERVER_TRUST_CERT="false" ``` #### On-Premises SQL Server ```bash export SQLSERVER_HOST="your-sql-server.company.com" export SQLSERVER_USER="your-username" export SQLSERVER_PASSWORD="your-password" export SQLSERVER_DATABASE="your-database" export SQLSERVER_ENCRYPT="true" export SQLSERVER_TRUST_CERT="true" # For self-signed certificates ``` #### Local SQL Server Express ```bash export SQLSERVER_HOST="localhost\\SQLEXPRESS" export SQLSERVER_USER="sa" export SQLSERVER_PASSWORD="your-password" export SQLSERVER_DATABASE="master" export SQLSERVER_ENCRYPT="false" export SQLSERVER_TRUST_CERT="true" ``` ### Step 3: Test the Connection ```bash # Test your configuration mcp-sqlserver --help # Quick connection test (press Ctrl+C to exit) mcp-sqlserver ``` ### Step 4: Add to Claude Desktop 1. **Find your Claude Desktop config file:** - **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json` - **Windows**: `%APPDATA%\Claude\claude_desktop_config.json` 2. **Add the MCP server configuration:** ```json { "mcpServers": { "sqlserver": { "command": "mcp-sqlserver", "env": { "SQLSERVER_HOST": "your-server.database.windows.net", "SQLSERVER_USER": "your-username", "SQLSERVER_PASSWORD": "your-password", "SQLSERVER_DATABASE": "your-database", "SQLSERVER_ENCRYPT": "true", "SQLSERVER_TRUST_CERT": "false" } } } } ``` 3. **Restart Claude Desktop** ### Step 5: Start Exploring! Try these commands in Claude Desktop: - `"Test the SQL Server connection"` - `"List all databases on the server"` - `"Show me the tables in [database name]"` - `"Describe the structure of the Users table"` - `"Show me foreign key relationships"` ## Features - **🔒 Read-only operations**: Only SELECT queries allowed, with comprehensive security validation - **🗄️ Schema discovery**: Explore databases, tables, views, relationships, and metadata - **📊 Data exploration**: Execute safe queries with built-in limits and timeouts - **🔐 Enterprise-ready**: Encrypted connections with certificate trust for production environments - **🛡️ Security-first**: Query validation, SQL injection protection, and access controls ## Available Tools ### Schema Discovery - `list_databases` - List all databases on the SQL Server instance - `list_tables` - List tables in a database or schema - `list_views` - List views in a database or schema - `describe_table` - Get detailed table schema including columns, data types, and constraints ### Relationship Analysis - `get_foreign_keys` - Get foreign key relationships for tables - `get_table_stats` - Get table statistics including row counts and size information ### Data Exploration - `execute_query` - Execute read-only SELECT queries with safety validation - `get_server_info` - Get SQL Server version, edition, and configuration details ## Common Commands for Claude Desktop Once your MCP server is configured, try these natural language commands: ### Getting Started Commands ``` "Test the SQL Server connection" "Show me server information" "List all databases on this server" "What tables are in the [database name] database?" ``` ### Database Exploration ``` "Describe the structure of the Users table" "Show me foreign key relationships in this database" "What are the largest tables by row count?" "Give me a sample of data from the Orders table" ``` ### Advanced Analysis ``` "Help me understand the relationship between Orders and Customers" "Show me all lookup tables in this database" "What columns contain date/time information?" "Find tables that might contain user authentication data" ``` ### Custom Queries ``` "Run this query: SELECT TOP 10 * FROM Products WHERE Price > 100" "Show me all customers created in the last 30 days" "What are the different product categories in the database?" ``` ## Installation ```bash npm install npm run build ``` ## Configuration The server is configured using environment variables: ### Required - `SQLSERVER_USER` - SQL Server username - `SQLSERVER_PASSWORD` - SQL Server password ### Optional - `SQLSERVER_HOST` - Server hostname (default: localhost) - `SQLSERVER_DATABASE` - Default database name - `SQLSERVER_PORT` - Port number (default: 1433) - `SQLSERVER_ENCRYPT` - Enable encryption (default: true) - `SQLSERVER_TRUST_CERT` - Trust server certificate (default: true) - `SQLSERVER_CONNECTION_TIMEOUT` - Connection timeout in ms (default: 30000) - `SQLSERVER_REQUEST_TIMEOUT` - Request timeout in ms (default: 60000) - `SQLSERVER_MAX_ROWS` - Maximum rows per query (default: 1000) ## Usage ### Environment Variables ```bash export SQLSERVER_HOST="your-server.database.windows.net" export SQLSERVER_USER="your-username" export SQLSERVER_PASSWORD="your-password" export SQLSERVER_DATABASE="your-database" export SQLSERVER_ENCRYPT="true" export SQLSERVER_TRUST_CERT="true" ``` ### Running the Server ```bash npm start ``` ## Installation Options ### Option 1: Global Installation (Recommended) ```bash npm install -g @bilims/mcp-sqlserver ``` ### Option 2: Local Installation ```bash npm install @bilims/mcp-sqlserver npx mcp-sqlserver ``` ### Option 3: Run with npx (No Installation) ```bash npx @bilims/mcp-sqlserver ``` ## Integration Examples ### Claude Desktop Add to your Claude Desktop configuration (`claude_desktop_config.json`): ```json { "mcpServers": { "sqlserver": { "command": "mcp-sqlserver", "env": { "SQLSERVER_HOST": "your-server.database.windows.net", "SQLSERVER_USER": "your-username", "SQLSERVER_PASSWORD": "your-password", "SQLSERVER_DATABASE": "your-database" } } } } ``` ### Claude Code CLI ```bash # Set environment variables export SQLSERVER_HOST="your-server" export SQLSERVER_USER="your-username" export SQLSERVER_PASSWORD="your-password" # Use with Claude Code claude mcp add sqlserver mcp-sqlserver ``` ### VSCode with MCP Extension Install the MCP extension for VSCode and add the server configuration. ## Security Features ### Query Validation - Only SELECT, WITH, SHOW, DESCRIBE, and EXPLAIN statements allowed - Comprehensive blacklist of dangerous keywords (INSERT, UPDATE, DELETE, DROP, etc.) - SQL injection pattern detection - Automatic query sanitization ### Connection Security - TLS/SSL encryption enabled by default - Server certificate trust options for enterprise environments - Connection pooling with timeout controls - Configurable request timeouts ### Result Limits - Maximum row limits per query (configurable) - Automatic TOP clause injection for SELECT queries - Query execution time tracking - Memory usage protection ## Example Queries Once connected, you can use the tools through your MCP client: ```typescript // List all databases await callTool("list_databases", {}); // List tables in a specific schema await callTool("list_tables", { schema: "dbo" }); // Get table schema details await callTool("describe_table", { table_name: "Users", schema: "dbo" }); // Execute a read-only query await callTool("execute_query", { query: "SELECT TOP 10 * FROM Users WHERE active = 1", limit: 10 }); // Get foreign key relationships await callTool("get_foreign_keys", { table_name: "Orders" }); ``` ## Development ```bash # Install dependencies npm install # Build the project npm run build # Run in development mode npm run dev # Run linting npm run lint # Run tests npm test ``` ## Troubleshooting ### Connection Issues 1. Verify server hostname and port 2. Check if encryption/certificate settings match your SQL Server configuration 3. Ensure user has appropriate read permissions 4. Test connection using SQL Server Management Studio first ### Permission Issues The user account needs at minimum: - `CONNECT` permission to the database - `SELECT` permission on tables/views you want to query - Access to system views for metadata queries ### Common SQL Server Configurations #### Azure SQL Database ```bash export SQLSERVER_HOST="your-server.database.windows.net" export SQLSERVER_ENCRYPT="true" export SQLSERVER_TRUST_CERT="false" ``` #### On-premises SQL Server with self-signed certificates ```bash export SQLSERVER_HOST="sql-server.company.com" export SQLSERVER_ENCRYPT="true" export SQLSERVER_TRUST_CERT="true" ``` ## License MIT ## Contributing 1. Fork the repository 2. Create a feature branch 3. Make your changes 4. Add tests if applicable 5. Submit a pull request ## Support For issues and questions: - Check the troubleshooting section above - Review SQL Server connection documentation - Ensure MCP client compatibility --- Built with the [Model Context Protocol SDK](https://github.com/modelcontextprotocol/typescript-sdk) for seamless AI integration.