Hey there! Let’s break down your two main questions step by step—first fixing that frustrating PostgreSQL DSN connection issue, then walking through the basics of building a custom ODBC driver for your Power BI data masking needs.

Your existing code has a few critical issues that are causing the SQL_ERROR return. Let’s start with a corrected version, then break down the fixes:

Corrected Code with Error Diagnostics #

#include <iostream>
#include <sql.h>
#include <sqlext.h>

// Helper function to get detailed ODBC error messages (critical for debugging!)
void printODBCError(SQLSMALLINT handleType, SQLHANDLE handle) {
    SQLCHAR sqlState[6], message[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER nativeError;
    SQLSMALLINT textLength;
    RETCODE ret;

    ret = SQLGetDiagRec(handleType, handle, 1, sqlState, &nativeError, message, SQL_MAX_MESSAGE_LENGTH, &textLength);
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        std::cout << "ODBC Error: SQL State: " << sqlState 
                  << ", Native Error: " << nativeError 
                  << ", Message: " << message << std::endl;
        ret = SQLGetDiagRec(handleType, handle, 2, sqlState, &nativeError, message, SQL_MAX_MESSAGE_LENGTH, &textLength);
    }
}

int main() {
    HENV hEnv = NULL;
    HDBC hDBC = NULL;
    RETCODE retcode;

    const char* szDSN = "PostgreSQL30";
    const char* szUID = "postgres";
    const char* szPasswd = "postgres";

    // 1. Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        std::cout << "Failed to allocate environment handle. Retcode: " << retcode << std::endl;
        return 1;
    }

    // 2. Set ODBC version to 3.0 (required for modern drivers)
    retcode = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        std::cout << "Failed to set ODBC version. Retcode: " << retcode << std::endl;
        printODBCError(SQL_HANDLE_ENV, hEnv);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return 1;
    }

    // 3. Allocate connection handle
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        std::cout << "Failed to allocate connection handle. Retcode: " << retcode << std::endl;
        printODBCError(SQL_HANDLE_ENV, hEnv);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return 1;
    }

    // 4. Connect to the DSN
    retcode = SQLConnectA(hDBC, (SQLCHAR*)szDSN, SQL_NTS, (SQLCHAR*)szUID, SQL_NTS, (SQLCHAR*)szPasswd, SQL_NTS);
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        std::cout << "Successfully connected to the PostgreSQL DSN!" << std::endl;
        SQLDisconnect(hDBC); // Clean up connection before freeing handles
    } else {
        std::cout << "Connection failed. Retcode: " << retcode << std::endl;
        printODBCError(SQL_HANDLE_DBC, hDBC);
    }

    // 5. Clean up handles (pass the handle directly, not its address!)
    if (hDBC != NULL) {
        SQLFreeHandle(SQL_HANDLE_DBC, hDBC);
    }
    if (hEnv != NULL) {
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    }

    return 0;
}

Key Fixes & Notes #

• Error Diagnostics: Added printODBCError to get specific error details (this will tell you exactly why the connection failed—e.g., wrong DSN name, driver not installed, credentials mismatch).
• Handle Cleanup: Your original code passed &hEnv and &hDBC to SQLFreeHandle—you need to pass the handle itself, not its address.
• Early Error Checking: Added checks after every ODBC call to catch issues before they cascade.
• Linker Dependency: When compiling, make sure to link against odbc32.lib (in Visual Studio, add it to your project’s linker input libraries).

Your high-level understanding is correct: a custom ODBC driver is a DLL that implements the standard ODBC API. It acts as a proxy between Power BI and the target database—forwarding queries, masking results, and sending them back to Power BI.

Core Concepts & Steps #

1. Learn the ODBC API Basics: Focus on core functions:

   • Handle management (SQLAllocHandle, SQLFreeHandle)
   • Connection logic (SQLConnect, SQLDriverConnect)
   • Query execution (SQLExecDirect, SQLPrepare)
   • Result retrieval (SQLFetch, SQLGetData)

2. Start with a Skeleton Driver:

   • Use Visual Studio’s ODBC Driver Template (if on Windows) or open-source skeleton drivers as a base. This gives you the required entry points and handle management boilerplate.
   • Your driver will need to maintain state for each connection (e.g., the actual target database connection handle).

3. Implement Proxy & Masking Logic:

   • Connection: When Power BI calls SQLConnect, your driver connects to the target database using its native ODBC driver.
   • Query Execution: Forward the incoming query to the target database.
   • Result Masking: When SQLFetch is called, retrieve the raw result from the target database, apply your masking rules (e.g., redact SSNs, email domains), then return the masked data to Power BI.

Simplified Skeleton Example (Conceptual) #

#include <sql.h>
#include <sqlext.h>
#include <string.h>

// Proxy connection state: holds the actual target database connection
typedef struct {
    HDBC targetDBC;
} ProxyDBC;

// Proxy statement state: holds the actual target statement
typedef struct {
    HSTMT targetStmt;
} ProxyStmt;

// Override SQLFetch to mask results before returning to Power BI
RETCODE SQL_API SQLFetch(HSTMT hStmt) {
    ProxyStmt* pStmt = (ProxyStmt*)hStmt;
    
    // First, fetch the raw result from the target database
    RETCODE ret = SQLFetch(pStmt->targetStmt);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        return ret;
    }

    // Example: Mask a SSN column (column 2)
    SQLCHAR ssn[20];
    SQLINTEGER len;
    SQLGetData(pStmt->targetStmt, 2, SQL_C_CHAR, ssn, sizeof(ssn), &len);
    
    if (len >= 4) {
        // Keep last 4 digits, mask the rest
        memset(ssn, '*', len - 4);
        memcpy(ssn + len - 4, (char*)ssn + len - 4, 4);
        // In a real driver, you'd store this masked value in your own result buffer
    }

    return ret;
}

// Override SQLConnect to connect to the target database
RETCODE SQL_API SQLConnectA(HDBC hDBC, SQLCHAR* szDSN, SQLSMALLINT cbDSN, SQLCHAR* szUID, SQLSMALLINT cbUID, SQLCHAR* szAuthStr, SQLSMALLINT cbAuthStr) {
    ProxyDBC* pDBC = (ProxyDBC*)hDBC;
    
    // Allocate target environment and connection handles
    HENV targetEnv;
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &targetEnv);
    SQLSetEnvAttr(targetEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    SQLAllocHandle(SQL_HANDLE_DBC, targetEnv, &pDBC->targetDBC);
    
    // Connect to the actual target database using the provided DSN/credentials
    return SQLConnectA(pDBC->targetDBC, szDSN, cbDSN, szUID, cbUID, szAuthStr, cbAuthStr);
}

// You'll need to implement all required ODBC functions (SQLAllocHandle, SQLFreeHandle, etc.)

Critical Next Steps #

• Register Your Driver: Add entries to the Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI pointing to your DLL, so ODBC Administrator and Power BI can detect it.
• Test Incrementally: Use isql.exe (a command-line ODBC tool) to test your driver before integrating with Power BI.
• Configuration: Add support for masking rules (e.g., let users specify which columns to mask via DSN settings or connection string parameters).

内容的提问来源于stack exchange，提问作者Filip Cacic