C++使用ODBC Driver 17连接SQL Server报错[53]求助

问题简述 #

已开放防火墙1433/1434端口，SQL配置管理器中启用了远程连接，但连接SQL Server时返回错误：

SQL Driver message:[Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState08001.

使用Windows身份认证，服务器名称DESKTOP-FT3KNQR\MSSQLSERVER01，当前用户DESKTOP-FT3KNQR\mathi，相关代码如下：

#pragma once
using namespace std;
#include "stdafx.h"
#include <iostream>
#include <ctime>
#include <list>
#include <sqlext.h> // main SQLAPI++ header
#include <sqltypes.h>
#include <Windows.h>
#include <sql.h>
#include <cstring>
#include <string>

void showSQLError(unsigned int handletype, const SQLHANDLE& handle)
{
    SQLCHAR SQLState[1024];
    SQLCHAR message[1024];
    if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, SQLState, NULL, message, 1024, NULL))
        cout << "SQL DRiver message:" << message << "\nSQLState" << SQLState << "." << endl;
}

class Datapoint {
public:
    time_t Date; string Name; double Value;

    Datapoint() {
        //default constructor to initialize Datapoint
        ;
    }

    Datapoint(time_t Dates, string Names, double Values)
    {
        Date = Dates;
        Name = Names;
        Value = Values;
    }

    static list<Datapoint> Timeseries(string Names)
    {
        list<Datapoint> data;

        SQLHANDLE SQLEnvHandle = NULL;
        SQLHANDLE SQLConnexionHandle = NULL;
        SQLHANDLE SQLStatementHandle = NULL;
        SQLRETURN retcSQLQuerystringode = 0;
        int retcode = 0; // 初始化retcode，避免未定义行为
        // assigning value to string SQLQuerystring
        string SQLQuerystring = "SELECT * FROM MDA..MarketData WHERE Name = '" + Names + "' order by Date Asc";

        // declaring character array (+1 for null terminator)
        char* SQLQuery = new char[SQLQuerystring.length() + 1];

        // copying the contents of the string to char array
        strcpy(SQLQuery, SQLQuerystring.c_str());

        do {
            // Allocate an environment handle
            if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &SQLEnvHandle))
            {
                break;
            }

            // Set the ODBC version environment attribute
            if (SQL_SUCCESS != SQLSetEnvAttr(SQLEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
            {
                break;
            }
            // Allocate a connection handle
            if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, SQLEnvHandle, &SQLConnexionHandle))
            {
                break;
            }
            if (SQL_SUCCESS != SQLSetConnectAttr(SQLConnexionHandle, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0))
            {
                break;
            }

            SQLCHAR retConnString[1024];
            // 修正连接字符串：转义反斜杠，改用Windows认证专用参数
            switch (SQLDriverConnect(SQLConnexionHandle, NULL, (SQLCHAR*)"Driver={ODBC Driver 17 for SQL Server};Server=DESKTOP-FT3KNQR\\MSSQLSERVER01;Database=MDA;Trusted_Connection=Yes;", SQL_NTS, retConnString, 1024, NULL, SQL_DRIVER_NOPROMPT)) {
            case SQL_SUCCESS:
                break;
            case SQL_SUCCESS_WITH_INFO:
                break;
            case SQL_NO_DATA_FOUND:
                showSQLError(SQL_HANDLE_DBC, SQLConnexionHandle);
                retcode = 1;
                break;
            case SQL_INVALID_HANDLE:
                showSQLError(SQL_HANDLE_DBC, SQLConnexionHandle);
                retcode = 1;
                break;
            case SQL_ERROR:
                showSQLError(SQL_HANDLE_DBC, SQLConnexionHandle);
                retcode = 1;
                break;
            default:
                break;
            }

            if (retcode != 0)
            {
                break;
            }

            if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, SQLConnexionHandle, &SQLStatementHandle))
            {
                break;
            }

            if (SQL_SUCCESS != SQLExecDirect(SQLStatementHandle, (SQLCHAR*)SQLQuery, SQL_NTS)) 
            {
                showSQLError(SQL_HANDLE_STMT, SQLStatementHandle);
                break;
            }
            else
            {
                while (SQLFetch(SQLStatementHandle) == SQL_SUCCESS)
                {
                    time_t datefromdb;
                    float valuesfromdb;
                    char* namesfromdb = new char[256];
                    SQLGetData(SQLStatementHandle, 1, SQL_C_DEFAULT, &datefromdb, sizeof(datefromdb), NULL);
                    SQLGetData(SQLStatementHandle, 2, SQL_C_DEFAULT, namesfromdb, sizeof(namesfromdb), NULL);
                    SQLGetData(SQLStatementHandle, 3, SQL_C_DEFAULT, &valuesfromdb, sizeof(valuesfromdb), NULL);

                    Datapoint datapointsql;
                    datapointsql.Date = datefromdb;
                    datapointsql.Name = namesfromdb;
                    datapointsql.Value = valuesfromdb;
                    data.push_back(datapointsql);
                    delete[] namesfromdb; // 释放内存，避免泄漏
                }
            }

        } while (FALSE);

        // 先断开连接（使用连接句柄，而非语句句柄）
        if (SQLConnexionHandle != NULL) {
            SQLDisconnect(SQLConnexionHandle);
        }
        // Free the statement handle
        if (SQLStatementHandle != NULL) {
            SQLFreeHandle(SQL_HANDLE_STMT, SQLStatementHandle);
        }
        // Free the connection and environment handles
        if (SQLConnexionHandle != NULL) {
            SQLFreeHandle(SQL_HANDLE_DBC, SQLConnexionHandle);
        }
        if (SQLEnvHandle != NULL) {
            SQLFreeHandle(SQL_HANDLE_ENV, SQLEnvHandle);
        }
        delete[] SQLQuery; // 释放查询字符串内存

        return data;
    }
};

// main.cpp
using namespace std;
#include "stdafx.h"
#include <iostream>
#include <ctime>
#include <list>
#include <sqlext.h> // main SQLAPI++ header
#include <sqltypes.h>
#include <Windows.h>
#include <sql.h>
#include <cstring>
#include <string>
#include "Class_Datapoint.h"

int main()
{
    list<Datapoint> data = Datapoint::Timeseries("CCC");

    return 0;
}

核心问题与解决步骤 #

1. 连接字符串的转义错误 #

C++字符串中，单个反斜杠\是转义字符，无法被正确识别为服务器名的一部分。必须使用双反斜杠\\表示实际的反斜杠：
错误写法：Server=DESKTOP-FT3KNQR\MSSQLSERVER01
正确写法：Server=DESKTOP-FT3KNQR\\MSSQLSERVER01

2. Windows身份认证的正确写法 #

Windows身份认证不需要指定UID，应使用Trusted_Connection=Yes或Integrated Security=SSPI来启用集成认证，避免手动指定用户导致的身份验证冲突。

3. 代码中的资源释放错误 #

• SQLDisconnect需要传入连接句柄（SQLConnexionHandle），而非语句句柄（SQLStatementHandle），原代码中的调用会导致无效操作。
• 未初始化retcode，可能导致后续逻辑判断出错，需初始化为0。
• 动态分配的内存（namesfromdb、SQLQuery）未释放，存在内存泄漏风险。

4. 额外排查点 #

• 确认SQL Server配置管理器中，Named Pipes协议已启用（错误提示为Named Pipes相关，需确保该协议处于运行状态）。
• 尝试用localhost\MSSQLSERVER01或127.0.0.1\MSSQLSERVER01替换服务器名，测试是否能正常连接。

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