连接Azure SQL执行查询报错：错误码0085E084，SQLSTATE 0085E88C

First off, those error codes look unusual—standard SQL Server/Azure SQL error codes and SQLSTATE values don’t use that hexadecimal format. This is likely a sign that your error handling code isn’t correctly retrieving or displaying the actual error details, or there’s a wide-character encoding issue at play. Let’s break down the steps to diagnose and fix this:

1. Fix Your Error Handling Function #

Looking at your code, the show_error function is truncated (you have mess... instead of message) and missing critical parameters for SQLGetDiagRec. This incomplete implementation is probably mangling the actual error data. Replace it with a complete version that properly captures all error details:

void show_error(unsigned int handletype, const SQLHANDLE& handle) {
    SQLWCHAR sqlstate[1024];
    SQLWCHAR message[1024];
    SQLINTEGER native_error;
    SQLSMALLINT msg_length;

    if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, &native_error, message, sizeof(message)/sizeof(SQLWCHAR), &msg_length))
    {
        wcout << L"SQL State: " << sqlstate << endl;
        wcout << L"Native Error Code: " << native_error << endl;
        wcout << L"Full Error Message: " << message << endl;
    }
}

Make sure to call this function after every ODBC operation (e.g., SQLAllocHandle, SQLConnect, SQLExecDirect) if the return code isn’t SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. This will give you the real, uncorrupted error details needed to pinpoint the issue.

2. Verify Your Connection String #

Azure SQL requires specific parameters in the ODBC connection string to connect securely. Double-check that yours includes these mandatory settings:

• DRIVER={ODBC Driver 17 for SQL Server} (use the latest driver version available)
• SERVER=tcp:your-server-name.database.windows.net,1433 (include tcp: and port 1433)
• Encrypt=yes (required for Azure SQL connections)
• TrustServerCertificate=no (prevents man-in-the-middle attacks)
• Valid UID (your Azure SQL username, usually in user@server format) and PWD

Example valid connection string:

SQLWCHAR conn_str[] = L"DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:myazuresql.database.windows.net,1433;DATABASE=MyDB;UID=admin@myazuresql;PWD=MyStrongPassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;";

3. Check Driver Compatibility #

Older ODBC drivers may not support Azure SQL’s security requirements. Ensure you have the latest ODBC Driver for SQL Server (version 17 or newer) installed on your machine.

4. Validate Azure SQL Firewall & Permissions #

• Firewall Rules: Log into the Azure Portal, navigate to your SQL Server, and confirm that your client IP address is allowed in the server-level firewall rules. You can also enable "Allow Azure services and resources to access this server" if you’re testing from an Azure resource.
• User Permissions: Ensure the username you’re using has at least CONNECT permission on the target database, plus appropriate permissions for the query you’re trying to run (e.g., db_datareader for SELECT queries).

5. Debug Your Connection Flow #

Make sure you’re properly initializing ODBC handles before connecting. Here’s a complete minimal example to test your connection:

#include "stdafx.h"
#include <iostream>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>
using namespace std;

void show_error(unsigned int handletype, const SQLHANDLE& handle) {
    SQLWCHAR sqlstate[1024];
    SQLWCHAR message[1024];
    SQLINTEGER native_error;
    SQLSMALLINT msg_length;

    if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, &native_error, message, sizeof(message)/sizeof(SQLWCHAR), &msg_length))
    {
        wcout << L"SQL State: " << sqlstate << endl;
        wcout << L"Native Error Code: " << native_error << endl;
        wcout << L"Full Error Message: " << message << endl;
    }
}

int main() {
    SQLHENV env;
    SQLHDBC dbc;
    SQLRETURN ret;

    // Initialize environment handle
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        show_error(SQL_HANDLE_ENV, env);
        return 1;
    }
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

    // Allocate connection handle
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        show_error(SQL_HANDLE_ENV, env);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        return 1;
    }

    // Replace with your connection details
    SQLWCHAR conn_str[] = L"DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:your-server.database.windows.net,1433;DATABASE=your-db;UID=your-user@your-server;PWD=your-password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;";
    ret = SQLDriverConnect(dbc, NULL, conn_str, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        show_error(SQL_HANDLE_DBC, dbc);
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        return 1;
    }

    wcout << L"Successfully connected to Azure SQL!" << endl;

    // Cleanup
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    return 0;
}

Run this minimal example first—if it fails, the corrected show_error function will give you the actual error message instead of the mangled codes you’re seeing now.

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