MCPQL - SQL Server MCP Server for database analysis and operations
npm install mcpqlclaude_desktop_config.json):
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:
`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"
}
}
}
}
`
#### Option 2: Local Development Installation
1. Clone and setup:
`bash
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:
`bash
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:
`json
{
"mcpServers": {
"mcpql": {
"command": "node",
"args": ["path/to/MCPQL/dist/server.js"]
}
}
}
`
🛠️ Available Tools
MCPQL provides 11 comprehensive tools for SQL Server database operations:
$3
Complete table structure analysis including columns, keys, indexes, and constraints.
$3
Analyze stored procedure structure including parameters, dependencies, and source code.
$3
Preview table data with optional filtering and row limits.
$3
Get comprehensive statistics for a specific column.
$3
Execute stored procedures with parameters and return results.
$3
Execute custom SQL queries with full error handling.
$3
Quick statistical analysis including row count, column distributions, and top values.
$3
Search across database objects by name and definition with configurable criteria.
$3
Get dependencies for database objects (tables, views, stored procedures, etc.).
$3
Get sample values from a specific column in a table.
$3
Get current security configuration and status for database operations.
📋 Usage Examples
$3
`typescript
// 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
});
`
$3
`typescript
// 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
});
`
$3
`typescript
// 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 }
});
`
$3
`typescript
// 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:
$3
Set DB_AUTHENTICATION_TYPE to one of:
- sql - SQL Server Authentication (default)
- windows - Windows Authentication
- azure-ad - Azure Active Directory Authentication
$3
| 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
$3
`json
{
"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"
}
}
}
}
`
$3
`json
{
"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
`json
{
"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"
}
}
}
}
`
$3
`json
{
"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"
}
}
}
}
`
$3
`json
{
"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"
}
}
}
}
`
$3
`json
{
"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.
$3
#### 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:
`typescript
const status = await mcp_get_security_status({});
`
$3
#### For Development Environment
`json
{
"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)
`json
{
"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"
}
}
}
}
`
$3
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
`
$3
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
$3
- "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
$3
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
$3
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:
`bash
npm test
`
The test suite includes comprehensive testing of all 10 tools with real database testing and complete coverage.
🏗️ Architecture
$3
`
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
`
$3
- ⚡ 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`