BI Gateway MCP Server
Unified MCP server for SQL databases (MSSQL, PostgreSQL), Power BI Desktop, and Fabric Semantic Models — accessed through a single gateway with shared infrastructure.
Quick Start
cd bi-gateway
# Activate venv
.venv\Scripts\activate
# Edit .env with your connection strings (already configured for local MSSQL + PG)
# Run the server
python gateway.py
# Run tests
python -m pytest tests/ -v
Architecture
MCP Host (Claude Desktop / Code)
| JSON-RPC 2.0 (stdio)
v
+------------------------------------------+
| BI Gateway (Python) |
| |
| gateway.py <- MCP server |
| models.py <- shared infra |
| safety.py <- validation/limits |
| pbi_desktop.py <- PBI Desktop bridge |
| .env <- config |
| |
| Infrastructure: |
| - Audit Logger (SQLite) |
| - TTL Cache (in-memory) |
| - Circuit Breaker |
| - Rate Limiter |
| - Error Playbooks |
| - Output Formatter + Schema Compression |
| - SQL Injection Protection |
| - DAX Validation |
| - Query Cost Estimation |
+------------------------------------------+
| | | |
v v v v
MSSQL PostgreSQL PBI Desktop Fabric XMLA
DW2022 17.9 (local SSAS) (TOM + MSAL)
DW2017
Tools (27)
SQL Read Tools [R]
| Tool | Purpose | Annotation |
|---|
sql_run_query | Execute read-only SQL with pagination | readOnly |
sql_list_tables | List tables and views | readOnly |
sql_describe_table | Columns, types, PKs, indexes, FKs | readOnly |
sql_search_schema | Keyword search across table/column names | readOnly |
sql_list_databases | Show all configured connections | readOnly |
sql_list_procedures | List stored procedures | readOnly |
sql_proc_history | View procedure version history | readOnly |
SQL Write Tools [W]
| Tool | Purpose | Annotation |
|---|
sql_execute | Run DDL/DML (INSERT, UPDATE, CREATE, etc.) | destructive |
sql_rollback_last | Preview or undo last write operation | destructive |
sql_create_procedure | Create/replace stored procedure (versioned) | write |
sql_call_procedure | Execute stored procedure by name | write |
Power BI Desktop Tools [R]
| Tool | Purpose | Annotation |
|---|
pbi_status | Check if PBI Desktop is running | readOnly |
pbi_run_dax | Execute DAX query against open model | readOnly |
pbi_get_schema | Get tables, columns, measures, relationships | readOnly |
Fabric Semantic Model Tools [R]
| Tool | Purpose | Annotation |
|---|
fabric_connect | Connect to a Fabric workspace/model via XMLA | write |
fabric_get_schema | Tables, columns, measures, relationships, calc groups | readOnly |
fabric_run_dax | Execute DAX query against connected Fabric model | readOnly |
fabric_audit_relationships | Scan for missing/orphaned/ambiguous relationships | readOnly |
Fabric Semantic Model Tools [W]
| Tool | Purpose | Annotation |
|---|
fabric_create_relationship | Add relationship with cardinality/direction | write |
fabric_modify_column | Hide/unhide column properties | write |
fabric_batch_modify_columns | Batch modify multiple columns in one transaction | write |
fabric_create_measure | Create or update a DAX measure | write |
fabric_create_udf | Create a DAX User-Defined Function | write |
fabric_create_calc_group | Create calculation group with items | write |
Cross-Platform Tools [R]
| Tool | Purpose | Annotation |
|---|
glossary_lookup | Resolve business terms to schema objects | readOnly |
cross_platform_compare | Compare a metric across SQL and PBI | readOnly |
Audit Tools [R]
| Tool | Purpose | Annotation |
|---|
get_audit_log | Query the audit trail (filter by tool, limit rows) | readOnly |
Safety Model
Database-Level (Dual Connection)
| User | Can Do | Cannot Do |
|---|
mcp_reader | SELECT | INSERT, UPDATE, DELETE, GRANT |
mcp_developer | SELECT, INSERT, UPDATE, DELETE, procedures | GRANT, REVOKE, admin ops |
Application-Level
| Check | Behavior |
|---|
| GRANT/REVOKE/DROP DATABASE | Always blocked (hardcoded, word-boundary match) |
| DROP/TRUNCATE/DELETE/ALTER | Requires confirm=True + readwrite mode |
| DELETE/UPDATE without WHERE | Blocked as high-risk query (needs confirm) |
| SQL injection patterns | 15 regex patterns block chaining, UNION, timing attacks, xp_cmdshell, remote-query functions, etc. |
| Identifier validation | Alphanumeric + underscore + brackets only, max 128 chars |
| Query length | Max 10,000 chars (configurable) |
| Rate limiting | 60 reads/min, 20 writes/min, 30 PBI calls/min (configurable) |
| Pre-state capture | UPDATE/DELETE snapshots affected rows for rollback |
| DAX validation | Must start with EVALUATE/DEFINE; SQL keywords blocked |
| Readonly mode | SQL_{DB}_MODE=readonly disables all write tools for that database |
MCP Tool Annotations
All 27 tools have readOnlyHint and destructiveHint annotations per MCP spec, so Claude knows which tools are safe before calling them.
Resources (8)
| URI | Description |
|---|
gateway://status | Connected DBs, cache stats, circuit breaker state, uptime |
gateway://safety | Rate limit usage, database modes, query limits |
gateway://audit/recent | Last 20 operations logged |
sql://databases | All configured connections with mode/dialect |
glossary://terms | Business term to schema object mappings |
powerbi://desktop/status | PBI Desktop connection status |
fabric://status | Fabric semantic model connection status |
Template resources (auto-register on access):
| URI | Description |
|---|
sql://{database_id}/schema | Auto-compressed schema (cached 5 min) |
Prompts (4)
| Prompt | Description |
|---|
explore_database | Guided schema exploration workflow |
investigate_metric | Glossary-driven metric investigation |
compare_databases | Cross-database schema comparison |
explore_pbi_model | Guided PBI Desktop model exploration |
Test Suite
155 tests covering all components:
| Test File | Coverage |
|---|
test_safety.py (59 tests) | SQL injection, identifier validation, DAX safety, query classification, rate limiting, cost estimation |
test_sqlguard.py (46 tests) | AST/token classification, dynamic-SQL unwrap, cross-DB guard, single-SELECT enforcement, error categorization |
test_integration.py (47 tests) | Live MSSQL read/write, stored procedures, rollback, glossary, PBI Desktop, cross-platform, resources |
test_surface.py (3 tests) | Surface inventory guard — registered tool/resource/prompt counts |
Run: python -m pytest tests/ -v
Configuration (.env)
# Database connections (dual reader/writer with SQL Auth)
SQL_MSSQL_DSN_READ=mssql+pyodbc://mcp_reader:pass@localhost/DBName?driver=...
SQL_MSSQL_DSN_WRITE=mssql+pyodbc://mcp_developer:pass@localhost/DBName?driver=...
SQL_MSSQL_MODE=readwrite # readonly | readwrite
SQL_PG_DSN_READ=postgresql+psycopg2://user:pass@localhost:5432/db
SQL_PG_MODE=readonly
# Defaults
SQL_DEFAULT_DB=mssql
SQL_MAX_ROWS=1000
SQL_TIMEOUT=30
SQL_WRITE_TIMEOUT=60
# Output formatting
SQL_MAX_DISPLAY_ROWS=50
SQL_MAX_COL_WIDTH=100
MAX_RESULT_CHARS=8000
MAX_QUERY_LENGTH=10000
# Rate limits
RATE_SQL_READ=60 # reads per minute
RATE_SQL_WRITE=20 # writes per minute
RATE_PBI=30 # PBI calls per minute
# Gateway
AUDIT_DB_PATH=~/.mcp-gateway/audit.db
CACHE_DEFAULT_TTL=300
LOG_LEVEL=INFO
GLOSSARY_PATH=~/.mcp-gateway/glossary.yaml
Files
bi-gateway/
├── gateway.py # MCP server: 27 tools, 8 resources, 4 prompts
├── models.py # Audit, cache, circuit breaker, formatter, errors
├── safety.py # SQL injection, DAX validation, rate limiter, cost estimation
├── pbi_desktop.py # PBI Desktop local SSAS connection via PowerShell + ADOMD.NET
├── fabric_model.py # Fabric semantic model connection via XMLA/TOM + MSAL auth
├── .env # Connection strings and config (git-ignored)
├── .env.example # Template
├── .gitignore
├── pyproject.toml # Dependencies
├── tests/
│ ├── test_safety.py # 57 unit tests for safety module
│ └── test_integration.py # 38 live integration tests
└── .venv/
Related Documents
ADR-001-bi-gateway-architecture.md — Architecture Decision Record (10 decisions)RESIDUAL-RISK.md / RED-PASS-2026-06-30.md — accepted risks and the pre-publication adversarial audit~/.mcp-gateway/glossary.yaml — Business glossary (29 AdventureWorks terms)
