feat: Add configurable verbose error mode for AI debugging

[nateGeorge]

Problem

The MSSQL MCP server intentionally sanitizes error messages for security, making it impossible for AI agents to debug SQL command failures.

Before:

{
  "success": false,
  "message": "Failed to execute query: Database query execution failed",
  "error": "SQL_EXECUTION_FAILED"
}

No actionable information for debugging - AI agents can't identify syntax errors, invalid columns, or table issues.

Solution

Added configurable VERBOSE_ERRORS mode that exposes full SQL error details when enabled while keeping secure defaults for production.

After (VERBOSE_ERRORS=true):

{
  "success": false,
  "message": "Failed to execute query: Invalid object name 'NonexistentTable'.",
  "error": "SQL_EXECUTION_FAILED",
  "details": {
    "code": "EREQUEST",
    "number": 208,
    "state": 1,
    "class": 16,
    "lineNumber": 1,
    "serverName": "empoweranalyticssql",
    "procName": "",
    "stack": "RequestError: Invalid object name...",
    "originalError": {
      "query": "SELECT * FROM NonexistentTable"
    }
  }
}

Changes

Files Added

• MssqlMcp/Node/src/utils/errorHandler.ts - Error formatting utilities
• MssqlMcp/Node/VERBOSE_ERRORS.md - Complete documentation

Files Modified

• MssqlMcp/Node/src/index.ts - Use formatError() in main handler
• MssqlMcp/Node/src/tools/ReadDataTool.ts - Use formatSqlError() for SQL errors

New Features

• isVerboseErrorMode() - Check VERBOSE_ERRORS env var
• formatError() - Format errors with optional verbose details
• formatSqlError() - SQL-specific error formatting with query context
• Comprehensive error logging to console

Usage

Enable in MCP configuration:

{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["/path/to/MssqlMcp/Node/dist/index.js"],
      "env": {
        "SERVER_NAME": "your-server.database.windows.net",
        "DATABASE_NAME": "your-database",
        "READONLY": "true",
        "VERBOSE_ERRORS": "true"
      }
    }
  }
}

Testing

Verified with intentional SQL errors:

✅ Invalid table name (Error #208):

• Full error message: "Invalid object name 'FakeTableThatDoesNotExist'"
• Error number, state, class, line number provided
• Server name and stack trace included

✅ Invalid column name (Error #207):

• Would show: "Invalid column name 'nonexistent_column'"
• With full SQL error context

Security

• Secure by default: VERBOSE_ERRORS not set = sanitized errors
• Opt-in debugging: Only enabled when explicitly configured
• Development focused: Recommended for dev/test environments only
• Read-only safe: Particularly useful with READONLY=true databases

Impact

For AI Agents:

• Can identify SQL syntax errors
• Can debug table/column name issues
• Can see exact line numbers in complex queries
• Can provide actionable fixes based on error codes

For Developers:

• Toggle debugging on/off without code changes
• Keep production secure while enabling dev debugging
• Comprehensive error context for troubleshooting

Related

This enhancement is particularly valuable when using the MCP with:

• Claude Code and code-executor-mcp pattern
• AI-assisted database query generation
• Automated SQL debugging workflows

---

Tested on:

• macOS with Azure SQL Database
• Read-only connections (analytics database)
• With code-executor-mcp integration