1

I am trying to query SQL Server from pyodbc using DSN with the following snippet:

import pyodbc

pyodbc.autocommit = True

conn = pyodbc.connect('DSN=SQLSERVER_CONN')
cursor = conn.cursor()
cursor.execute('select count(1) from jupiter.fact_load')
result = cursor.fetchall()

for row in result:
   print(row)

cursor.close()
conn.close()

My .odbc.ini looks:

[SQLSERVER_CONN]
Description=Connection to SQLSERVER UAT
DRIVER=/home/aiman/mssql-jdbc/9.2.0/libmsodbcsql-11.0.so.2270.0
SERVER=my.sqlserver.com,10501
DATABASE=jupiter
UID=aiman
PWD=xxxxx
Trusted_Connection=yes

And its giving me this following error:

Traceback (most recent call last):
  File "test_odbc.py", line 5, in <module>
    conn = pyodbc.connect('DSN=SQLSERVER_CONN')
pyodbc.Error: ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)")

In one post I read it happens when .rll file is not present, but I have both the files (driver, and .rll) present at the driver's path:

libmsodbcsql-11.0.so.2270.0
msodbcsqlr11.rll

Similar question was given here (echo "default:x:$uid:0:user for openshift:/tmp:/bin/bash" >> /etc/passwd), but I can't do this since it will overwrite the system account settings, and I am trying to run from my own ID.

aiman
  • 1,049
  • 19
  • 57
  • 1
    `libmsodbcsql-11.0.so.2270.0` was a thing back in 2013. Are you trying to connect to a very old and unsupported version of SQL Server? Why wouldn't you use the currently supported ODBC Driver 17 for SQL Server (or v18)? – AlwaysLearning Jun 20 '23 at 12:11
  • Aside... `Trusted_Connection=yes` implies that you're trying to use Active Directory/Kerberos/Windows authentication instead of the username+password based SQL Logon authentication. Is your Linux/Unix machine using Kerberos? Is it joined to the same authentication domain as the SQL Server to which you're trying to connect? – AlwaysLearning Jun 20 '23 at 12:13
  • @AlwaysLearning This version of SQL Server is being used by the upstream team, not under our control. And yes we are using Kerberos for authentication. Shall remove the UID and PWD ? – aiman Jun 20 '23 at 12:15
  • I tried both..using `UID` and `PWD` and removing `Trusted_Connection` from DSN, also the reverse. Same error – aiman Jun 20 '23 at 12:20
  • Given the `SQL_HANDLE_HENV` error message it sounds to me like you have a missing (or inaccessible) file somewhere. Check all the file references in your `/etc/odbcinst.ini`, `/etc/odbc.ini`, `/etc/ODBCDataSources` and `~/.odbc.ini` files to make sure those specific files exist and are accessible to the user running your Python script. If any of them are links then likewise make sure that their targets exist and are accessible to the user running your Python script. – AlwaysLearning Jun 20 '23 at 12:28
  • The documentation [here](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15&tabs=alpine18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline#11) says that "Installation puts the driver in `/opt/microsoft/msodbcsql/11.0.2270.0`. The driver and its support files must be in `/opt/microsoft/msodbcsql/11.0.2270.0`." It looks like your driver may not be installed correctly. – Gord Thompson Jun 20 '23 at 13:32

1 Answers1

0

As highlighted by @AlwaysLearning, this was incompatible driver issue.
got the compatible driver: lib64/libmsodbcsql-17.2.so.0.1.
working fine now.

aiman
  • 1,049
  • 19
  • 57