Copilot SQL MCP POC

Proof of concept for answering business questions over local SQL Server WideWorldImporters data through MCP.

Current Milestone

Milestone 9: Copilot Studio integration documentation.

Status:

• [x] Legacy Cursor implementation preserved in legacy/.
• [x] Shared app package created.
• [x] STDIO entry point added as server_stdio.py.
• [x] Existing MCP tool names preserved.
• [x] Existing Cursor MCP App resource URI preserved.
• [x] Data dictionary copied from the legacy implementation.
• [x] Cursor renderer copied from the legacy implementation.
• [x] Unit tests added for SQL validation and result handling.
• [x] Shared VisualResponse model added.
• [x] Cursor visual results now pass through the neutral model.
• [x] Cursor renderer can normalize visual_response payloads.
• [x] SQL remains excluded from public visual response payloads.
• [x] Adaptive Card renderer added for KPI, bar, horizontal bar, line, pie, doughnut, table, and fallback outputs.
• [x] Copilot-facing output helper added with business result, Adaptive Card, and fallback text.
• [x] Streamable HTTP entry point added as server_http.py.
• [x] /health endpoint added.
• [x] MCP HTTP endpoint defaults to /mcp.
• [x] Windows and SQL authentication modes configured through environment variables.
• [x] Query timeout, max rows, encryption, trust certificate, and approved schemas are configurable.
• [x] Cross-database, system object, unapproved schema, unqualified table, and SELECT INTO queries are rejected.
• [x] .env.example added without secrets.
• [x] Dockerfile added for the HTTP MCP server.
• [x] .dockerignore added.
• [x] Container build/run guide added.
• [x] Azure App Service / ACR deployment guide added.
• [x] Hybrid Connection and SQL Server Express preparation steps documented.
• [x] Copilot Studio MCP onboarding guide added.
• [x] Copilot Studio testing and publish steps documented.
• [ ] Manual Cursor acceptance questions verified against live SQL Server.

Local STDIO Server

Run the refactored Cursor-compatible MCP server with:

python server_stdio.py

Local Streamable HTTP Server

Run the local HTTP MCP server with:

python server_http.py

Defaults:

• host: 0.0.0.0
• port: 8000, or PORT if set
• MCP endpoint: /mcp, or MCP_HTTP_PATH if set
• health endpoint: /health

Example health URL:

http://localhost:8000/health

The original working implementation remains available at:

legacy/sqlserver_mcp_wwi_cursor_apps_business.py

Configuration

The refactored server keeps the legacy local defaults:

• SQLSERVER_HOST, default 050027346-3
• SQLSERVER_PORT, optional
• SQLSERVER_DB, default WideWorldImporters
• SQLSERVER_AUTH_MODE, windows or sql
• SQLSERVER_USER, optional
• SQLSERVER_PASS, optional legacy password variable
• SQLSERVER_PASSWORD, optional preferred password variable
• SQLSERVER_DRIVER, default ODBC Driver 17 for SQL Server
• SQLSERVER_ENCRYPT, default no
• SQLSERVER_TRUST_CERT, default yes
• QUERY_TIMEOUT_SECONDS, default 15
• MAX_QUERY_ROWS, default 500
• SQLSERVER_APPROVED_SCHEMAS, default Application,Sales,Purchasing,Warehouse

If no SQL user/password is provided, the connection uses Windows trusted authentication, matching the legacy Cursor setup.

Local state files default to the legacy/ directory for compatibility:

• mcp_memory.txt
• mcp_pending_suggestions.txt
• mcp_queries.log

Override that directory with MCP_STATE_DIR if needed.

Tests

The current tests use the Python standard library and do not require a live SQL Server:

python -m unittest discover -s tests -p "test_*.py" -v

Manual Cursor and SQL Server acceptance checks are listed in docs/acceptance_tests.md.

The shared visual contract is summarized in docs/visual_contract.md. Adaptive Card rendering notes are in docs/adaptive_card_examples.md. Local HTTP setup notes are in docs/local_http.md. Database configuration notes are in docs/database_configuration.md. Containerisation notes are in docs/containerization.md. Azure deployment notes are in AZURE_DEPLOYMENT.md. Copilot Studio integration notes are in COPILOT_STUDIO_INTEGRATION.md.