MCPQL

MCPQL - SQL Server MCP

A comprehensive Model Context Protocol (MCP) server for SQL Server database operations. This server provides 10 powerful tools for database analysis, object discovery, and data manipulation through the MCP protocol.

🚀 Quick Start

Prerequisites

• Node.js 18+ and npm
• SQL Server database with appropriate connection credentials
• MCP-compatible client (like Claude Desktop, Cursor IDE, or any MCP client)

Installation & Configuration

Option 1: Using npx from GitHub (Recommended)

No installation needed! Just configure your MCP client:

For Claude Desktop (claude_desktop_config.json):

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "your_server",
        "DB_NAME": "your_database",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

For Cursor IDE:

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "your_server",
        "DB_NAME": "your_database",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

Option 2: Local Development Installation

1. Clone and setup:

git clone https://github.com/hendrickcastro/MCPQL.git
cd MCPQL
npm install
npm run build

2. Configure database connection: Create a .env file with your database credentials:

# Basic SQL Server connection
DB_AUTHENTICATION_TYPE=sql
DB_SERVER=localhost
DB_NAME=MyDatabase
DB_USER=sa
DB_PASSWORD=YourPassword123!
DB_PORT=1433
DB_ENCRYPT=false
DB_TRUST_SERVER_CERTIFICATE=true

3. Configure MCP client with local path:

{
  "mcpServers": {
    "mcpql": {
      "command": "node",
      "args": ["path/to/MCPQL/dist/server.js"]
    }
  }
}

🛠️ Available Tools

MCPQL provides 11 comprehensive tools for SQL Server database operations:

1. 🏗️ Table Analysis - mcp_table_analysis

Complete table structure analysis including columns, keys, indexes, and constraints.

2. 📋 Stored Procedure Analysis - mcp_sp_structure

Analyze stored procedure structure including parameters, dependencies, and source code.

3. 👀 Data Preview - mcp_preview_data

Preview table data with optional filtering and row limits.

4. 📊 Column Statistics - mcp_get_column_stats

Get comprehensive statistics for a specific column.

5. ⚙️ Execute Stored Procedure - mcp_execute_procedure

Execute stored procedures with parameters and return results.

6. 🔍 Execute SQL Query - mcp_execute_query

Execute custom SQL queries with full error handling.

7. ⚡ Quick Data Analysis - mcp_quick_data_analysis

Quick statistical analysis including row count, column distributions, and top values.

8. 🔎 Comprehensive Search - mcp_search_comprehensive

Search across database objects by name and definition with configurable criteria.

9. 🔗 Object Dependencies - mcp_get_dependencies

Get dependencies for database objects (tables, views, stored procedures, etc.).

10. 🎯 Sample Values - mcp_get_sample_values

Get sample values from a specific column in a table.

11. 🔒 Security Status - mcp_get_security_status

Get current security configuration and status for database operations.

📋 Usage Examples

Analyzing a Table

// Get complete table structure
const analysis = await mcp_table_analysis({ 
  table_name: "dbo.Users" 
});

// Get quick data overview
const overview = await mcp_quick_data_analysis({ 
  table_name: "dbo.Users",
  sample_size: 500
});

// Preview table data with filters
const data = await mcp_preview_data({
  table_name: "dbo.Users",
  filters: { "Status": "Active", "Department": "IT" },
  limit: 25
});

Finding Database Objects

// Find all objects containing "User"
const objects = await mcp_search_comprehensive({ 
  pattern: "User",
  search_in_names: true,
  search_in_definitions: false
});

// Find procedures that query a specific table
const procedures = await mcp_search_comprehensive({ 
  pattern: "FROM Users",
  object_types: ["PROCEDURE"],
  search_in_definitions: true
});

Analyzing Stored Procedures

// Get complete stored procedure analysis
const spAnalysis = await mcp_sp_structure({ 
  sp_name: "dbo.usp_GetUserData" 
});

// Execute a stored procedure
const result = await mcp_execute_procedure({
  sp_name: "dbo.usp_GetUserById",
  params: { "UserId": 123, "IncludeDetails": true }
});

Data Analysis

// Get column statistics
const stats = await mcp_get_column_stats({
  table_name: "dbo.Users",
  column_name: "Age"
});

// Get sample values from a column
const samples = await mcp_get_sample_values({
  table_name: "dbo.Users",
  column_name: "Department",
  limit: 15
});

🔧 Environment Variables & Connection Types

MCPQL supports multiple SQL Server connection types with comprehensive configuration options:

🔐 Authentication Types

Set DB_AUTHENTICATION_TYPE to one of:

• sql - SQL Server Authentication (default)
• windows - Windows Authentication
• azure-ad - Azure Active Directory Authentication

📋 Complete Environment Variables

🔧 Connection Configuration Examples

1. 🏠 SQL Server Local (SQL Authentication)

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "localhost",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

2. 🏢 SQL Server Express (Named Instance)

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "localhost",
        "DB_INSTANCE_NAME": "SQLEXPRESS",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

3. 🪟 Windows Authentication

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "windows",
        "DB_SERVER": "MYSERVER",
        "DB_NAME": "MyDatabase",
        "DB_DOMAIN": "MYDOMAIN",
        "DB_USER": "myuser",
        "DB_PASSWORD": "mypassword",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

4. ☁️ Azure SQL Database (Azure AD Password)

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "azure-ad",
        "DB_SERVER": "myserver.database.windows.net",
        "DB_NAME": "MyDatabase",
        "DB_USER": "user@domain.com",
        "DB_PASSWORD": "userpassword",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

5. 🔐 Azure SQL Database (Service Principal)

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "azure-ad",
        "DB_SERVER": "myserver.database.windows.net",
        "DB_NAME": "MyDatabase",
        "DB_AZURE_CLIENT_ID": "your-client-id",
        "DB_AZURE_CLIENT_SECRET": "your-client-secret",
        "DB_AZURE_TENANT_ID": "your-tenant-id",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

6. 🔗 Using Connection String

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_CONNECTION_STRING": "Server=localhost;Database=MyDatabase;User Id=sa;Password=YourPassword123!;Encrypt=false;TrustServerCertificate=true;"
      }
    }
  }
}

🔒 Security Features

MCPQL includes comprehensive security controls to prevent accidental database modifications, especially important in production environments.

🛡️ Security Controls

Database Modification Protection

• DB_ALLOW_MODIFICATIONS: Controls DML/DDL operations (INSERT, UPDATE, DELETE, ALTER, DROP, CREATE)
• DB_ALLOW_STORED_PROCEDURES: Controls stored procedure execution
• Default: Both variables default to false for maximum security

Security Status Tool

Use mcp_get_security_status to check current security configuration:

const status = await mcp_get_security_status({});

🔧 Enabling Operations

For Development Environment

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_ALLOW_MODIFICATIONS": "true",
        "DB_ALLOW_STORED_PROCEDURES": "true"
      }
    }
  }
}

For Production Environment (Recommended)

{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_SERVER": "prod-server",
        "DB_NAME": "ProductionDB",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "secure_password",
        "DB_ALLOW_MODIFICATIONS": "false",
        "DB_ALLOW_STORED_PROCEDURES": "false"
      }
    }
  }
}

🚨 Security Error Messages

When operations are blocked, MCPQL provides clear guidance:

Error: Modification operations are disabled for security.
To enable modifications, configure: DB_ALLOW_MODIFICATIONS=true

Error: Stored procedure execution is disabled for security.
To enable stored procedures, configure: DB_ALLOW_STORED_PROCEDURES=true

📋 Always Allowed Operations

These operations are always permitted regardless of security settings:

• SELECT queries
• Table analysis and schema inspection
• Column statistics and data preview
• Object search and dependency analysis
• Database metadata operations

For complete security documentation, see SECURITY.md.

🚨 Troubleshooting Common Issues

Connection Issues

• "Login failed": Check username/password. For Windows auth, ensure DB_AUTHENTICATION_TYPE=windows
• "Server was not found": Verify server name and port. For SQL Express, add DB_INSTANCE_NAME
• "Certificate" errors: For local development, set DB_TRUST_SERVER_CERTIFICATE=true
• Timeout errors: Increase DB_TIMEOUT or check network connectivity

SQL Server Express Setup

1. Enable TCP/IP protocol in SQL Server Configuration Manager
2. Set a static port (usually 1433) or use dynamic port with Browser Service
3. Configure Windows Firewall to allow SQL Server traffic
4. Use DB_INSTANCE_NAME=SQLEXPRESS for default Express installations

Azure SQL Database Setup

1. Create server firewall rules to allow client IP
2. Use format: server.database.windows.net for server name
3. Always set DB_ENCRYPT=true and DB_TRUST_SERVER_CERTIFICATE=false
4. For Service Principal auth, register app in Azure AD and assign permissions

🧪 Testing

Run the comprehensive test suite:

npm test

The test suite includes comprehensive testing of all 10 tools with real database testing and complete coverage.

🏗️ Architecture

Project Structure

MCPQL/
├── src/
│   ├── __tests__/          # Comprehensive test suite
│   ├── tools/              # Modular tool implementations
│   │   ├── tableAnalysis.ts      # Table analysis tools
│   │   ├── storedProcedureAnalysis.ts  # SP analysis tools
│   │   ├── dataOperations.ts     # Data operation tools
│   │   ├── objectSearch.ts       # Search and discovery tools
│   │   ├── types.ts              # Type definitions
│   │   └── index.ts              # Tool exports
│   ├── db.ts               # Database connection management
│   ├── server.ts           # MCP server setup and handlers
│   ├── tools.ts            # Tool definitions and schemas
│   └── mcp-server.ts       # Tool re-exports
├── dist/                   # Compiled JavaScript output
└── package.json           # Dependencies and scripts

Key Features

• ⚡ Connection Pooling: Efficient database connection management
• 🛡️ Robust Error Handling: Comprehensive error handling and validation
• 📋 Rich Metadata: Detailed results with comprehensive database information
• 🔧 Flexible Configuration: Environment-based configuration
• 📊 Optimized Queries: Efficient SQL queries for all operations

📝 Important Notes

• Object Names: Always use schema-qualified names (e.g., dbo.Users, api.Idiomas)
• Error Handling: All tools return structured responses with success/error indicators
• Type Safety: Full TypeScript support with proper type definitions
• Connection Management: Automatic connection pooling and retry logic
• Security: Parameterized queries to prevent SQL injection

🤝 Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Make your changes and add tests
4. Ensure all tests pass (npm test)
5. Commit your changes (git commit -m 'Add amazing feature')
6. Push to the branch (git push origin feature/amazing-feature)
7. Open a Pull Request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• Built with the Model Context Protocol SDK
• Uses mssql for SQL Server connectivity
• Comprehensive testing with Jest

🏷️ Tags & Keywords

Database: sql-server azure-sql database-analysis database-tools mssql t-sql database-management database-administration database-operations data-analysis

MCP & AI: model-context-protocol mcp-server mcp-tools ai-tools claude-desktop cursor-ide anthropic llm-integration ai-database intelligent-database

Technology: typescript nodejs npm-package cli-tool database-client sql-client database-sdk rest-api json-api database-connector

Features: table-analysis stored-procedures data-preview column-statistics query-execution database-search object-dependencies schema-analysis data-exploration database-insights

Deployment: docker azure-deployment cloud-ready enterprise-ready production-ready scalable secure authenticated encrypted configurable

Use Cases: database-development data-science business-intelligence database-migration schema-documentation performance-analysis data-governance database-monitoring troubleshooting automation

---

🎯 MCPQL provides comprehensive SQL Server database analysis and manipulation capabilities through the Model Context Protocol. Perfect for database administrators, developers, and anyone working with SQL Server databases! 🚀