Microsoft ODBC cannot create valid handle

Question

I'm using Microsoft's ODBC driver to connect a C++/Linux application to a SQL Server database running remotely. When I try to connect to the database with SQLDriverConnect(), the call fails with a return code error of SQL_INVALID_HANDLE.

Reading through their documentation, I find this:

SQL_INVALID_HANDLE Function failed due to an invalid environment, connection, statement, or descriptor handle. This indicates a programming error. No additional information is available from SQLGetDiagRec or SQLGetDiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is passed for an argument that requires a connection handle.

Fair enough, but none of the preceding function calls to create the environment and handles before the SQLDriverConnect()function call return an error result. For the second argument to SQLDriverConnect(), their documentation says I can pass in a null pointer if there is no desktop window (as is the case on this linux console application).

Here is a MVCE, adapted from Microsoft's example program:

#include "sql.h"
#include "sqlext.h"
#include "msodbcsql.h"
#include <iostream>
#include <string>

int main(int, char**)
{
  using std::cerr;
  using std::endl;
  SQLHENV henv;  
  SQLHDBC hdbc;
  HWND dhandle = nullptr; // no desktop handle in linux
  SQLHSTMT hstmt;  
  SQLRETURN retcode;  
  SQLCHAR OutConnStr[255];  
  SQLSMALLINT OutConnStrLen;

  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLAllocHandle (environment) failed " << retcode << endl;

  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLSetEnvAttr failed " << retcode << endl;

  retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLAllocHandle (connection) failed " << retcode << endl;

  retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);  
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLSetConnectAttr failed " << retcode << endl;

  std::string dsn = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=*.*.*,1433;DATABASE=***;UID=***;PWD=***";
  retcode = SQLDriverConnect(hdbc, dhandle, (SQLCHAR*)dsn.c_str(), dsn.length(), OutConnStr, 255, &OutConnStrLen, SQL_DRIVER_PROMPT);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    cerr << "SQLDriverConnect failed " << retcode << endl;

  // cleanup code redacted for brevity

  return 0;
}

The program outputs SQLDriverConnect failed -2, which is SQL_INVALID_HANDLE. I'm stumped. hdbc is clearly the right type, and examining it in the debugger shows me it is not null.

It may be worth noting that the exact same connection string works in a python program using pyodbc. It seems that the C++ program isn't even getting as far as looking at that string, though. It just doesn't like the handle I'm sending into the connect call.

Microsoft's documentation clearly says they provide no additional information. If anyone can provide any direction on how to diagnose/debug this, I'd appreciate it greatly.

This application uses gcc 4.9.1 on Centos 7.

Answer 1

After two weeks of digging, this turned out to be some kind of versioning problem.

Eventually, this program will be doing some BCP uploads via Microsoft's extensions in libmsodbcsql.so. It turns out that library also has implementations of many of the SQL* functions, which are the ones that are failing in this test program. When I change the order of the link so that libodbc.so is before the MSFT extensions library so that the loader finds those implementations first, the program works fine.

I'm curious why this is, and it probably points to something else I'm doing wrong that may bite me down the road. But for now, at least, I am able to get connected to the database and do basic queries and updates.

Thanks to those who helped.

Answer 2

I have exactly the same error using similar code (that was working in Ubuntu 18.04, but not with a update to 20.04)

cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
UsageCount=1

using this connection string

DRIVER=ODBC Driver 17 for SQL Server;SERVER=127.0.0.1, 1433;UID=SA;PWD=password;DATABASE=my_database;

this is my library link order

if(UNIX)
  find_program(LSB_RELEASE_EXEC lsb_release)
  execute_process(COMMAND ${LSB_RELEASE_EXEC} -is OUTPUT_VARIABLE LSB_RELEASE_ID_SHORT OUTPUT_STRIP_TRAILING_WHITESPACE)
  message(STATUS "Building in " ${LSB_RELEASE_ID_SHORT})
  if("${LSB_RELEASE_ID_SHORT}" STREQUAL "Ubuntu")
    message(STATUS "Linking with SQL-Server library")
    set(lib_dep ${lib_dep} msodbcsql-17)
  endif()
  set(lib_dep ${lib_dep} pthread odbc dl)
endif()

As noted in the solution above, changing the link order fixed the problem

set(lib_dep ${lib_dep} pthread odbc dl msodbcsql-17)