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
- Clone and setup:
git clone https://github.com/hendrickcastro/MCPQL.git
cd MCPQL
npm install
npm run build
- Configure database connection:
Create a
.envfile 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
- 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 Authenticationazure-ad- Azure Active Directory Authentication
📋 Complete Environment Variables
| Variable | Description | Default | Required For |
|---|---|---|---|
| Basic Connection | |||
DB_AUTHENTICATION_TYPE | Authentication type (sql/windows/azure-ad) | sql | All |
DB_SERVER | SQL Server hostname/IP | - | All |
DB_NAME | Database name | - | All |
DB_PORT | SQL Server port | 1433 | All |
DB_TIMEOUT | Connection timeout (ms) | 30000 | All |
DB_REQUEST_TIMEOUT | Request timeout (ms) | 30000 | All |
| SQL Server Authentication | |||
DB_USER | SQL Server username | - | SQL Auth |
DB_PASSWORD | SQL Server password | - | SQL Auth |
| Windows Authentication | |||
DB_DOMAIN | Windows domain | - | Windows Auth |
DB_USER | Windows username | current user | Windows Auth |
DB_PASSWORD | Windows password | - | Windows Auth |
| Azure AD Authentication | |||
DB_USER | Azure AD username | - | Azure AD (Password) |
DB_PASSWORD | Azure AD password | - | Azure AD (Password) |
DB_AZURE_CLIENT_ID | Azure AD App Client ID | - | Azure AD (Service Principal) |
DB_AZURE_CLIENT_SECRET | Azure AD App Client Secret | - | Azure AD (Service Principal) |
DB_AZURE_TENANT_ID | Azure AD Tenant ID | - | Azure AD (Service Principal) |
| SQL Server Express | |||
DB_INSTANCE_NAME | Named instance (e.g., SQLEXPRESS) | - | Express instances |
| Security Settings | |||
DB_ENCRYPT | Enable encryption | false | All |
DB_TRUST_SERVER_CERTIFICATE | Trust server certificate | false | All |
DB_ENABLE_ARITH_ABORT | Enable arithmetic abort | true | All |
DB_USE_UTC | Use UTC for dates | true | All |
| Connection Pool | |||
DB_POOL_MAX | Maximum connections | 10 | All |
DB_POOL_MIN | Minimum connections | 0 | All |
DB_POOL_IDLE_TIMEOUT | Idle timeout (ms) | 30000 | All |
| Advanced Settings | |||
DB_CANCEL_TIMEOUT | Cancel timeout (ms) | 5000 | All |
DB_PACKET_SIZE | Packet size (bytes) | 4096 | All |
DB_CONNECTION_STRING | Complete connection string | - | Alternative to individual settings |
| Security Controls | |||
DB_ALLOW_MODIFICATIONS | Allow DML/DDL operations | false | All |
DB_ALLOW_STORED_PROCEDURES | Allow stored procedure execution | false | All |
🔧 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
falsefor 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_TIMEOUTor check network connectivity
SQL Server Express Setup
- Enable TCP/IP protocol in SQL Server Configuration Manager
- Set a static port (usually 1433) or use dynamic port with Browser Service
- Configure Windows Firewall to allow SQL Server traffic
- Use
DB_INSTANCE_NAME=SQLEXPRESSfor default Express installations
Azure SQL Database Setup
- Create server firewall rules to allow client IP
- Use format:
server.database.windows.netfor server name - Always set
DB_ENCRYPT=trueandDB_TRUST_SERVER_CERTIFICATE=false - 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
- Fork the repository
- Create a feature branch (
git checkout -b feature/amazing-feature) - Make your changes and add tests
- Ensure all tests pass (
npm test) - Commit your changes (
git commit -m 'Add amazing feature') - Push to the branch (
git push origin feature/amazing-feature) - 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! 🚀