search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.95k stars 563 forks source link

InterfaceError: Data source name not found with pyodbc despite working with isql #1352

Closed AlexVialaBellander closed 6 months ago

AlexVialaBellander commented 6 months ago

Hello,

I am encountering an issue when trying to connect to an Azure SQL database using pyodbc in a container environment. The same connection string works perfectly when used with isql, but fails with pyodbc.

Environment:

Issue Detail: When attempting to connect to the Azure SQL database using pyodbc with the following code snippet:

self.conn = pyodbc.connect(config["db_connection_string"])

where config["db_connection_string"] is defined as:

"DRIVER={ODBC Driver 18 for SQL Server};SERVER=XXXXX,1433;DATABASE=XXXXX;UID=XXXXXX;PWD=XXXXXXX"

I receive the following error message:

pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Successful Connection via isql: The connection string works without issues when using:

isql -v -k '<connection string>'

Additional Details:

Could anyone please help identify why pyodbc is unable to find the data source name or the default driver, despite the same setup working with isql? Any suggestions or insights would be greatly appreciated.

Thank you!

gordthompson commented 6 months ago

Is your Python code running in any sort of special environment like Anaconda or similar?

AlexVialaBellander commented 6 months ago

Hi @gordthompson, negative. We pip install directly to the global python installation in the container.

For demonstration purposes:

FROM python:3.11

RUN get all the required linux packages, including ms odbc 18, etc, etc

RUN pip install --no-cache-dir --upgrade -r /code/requirements.txt

CMD start app
v-chojas commented 6 months ago

Compare an ODBC trace of the working and non-working case. I suspect something is different with the connection string (escaping special characters in passwords?)

AlexVialaBellander commented 6 months ago

Hi @v-chojas, I will gladly supply said traces. Could you instruct me or point me in the direction of how to get the types of traces that you are looking for?

gordthompson commented 6 months ago

https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log

AlexVialaBellander commented 6 months ago

Morning from Europe @gordthompson and @v-chojas ☀️ Thanks for sharing the troubleshooting page.

isql 
[ODBC][26][1715065290.944892][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae2575470
[ODBC][26][1715065290.944967][SQLAllocHandle.c][395]
Entry:
Handle Type = 2
Input Handle = 0xaaaae2575470
UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][26][1715065290.945041][SQLAllocHandle.c][531]
Exit:[SQL_SUCCESS]
Output Handle = 0xaaaae2576180
[ODBC][26][1715065290.945150][SQLDriverConnect.c][751]
Entry:
Connection = 0xaaaae2576180
Window Hdl = (nil)
Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=XXXXXX,1433;DATABASE=XXXXXX;UID=XXXXXX...][length = 149 (SQL_NTS)]
Str Out = 0xffffcaa1de18
Str Out Max = 2048
Str Out Ptr = (nil)
Completion = 0
[ODBC][26][1715065291.385037][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae25c6470
[ODBC][26][1715065291.385086][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae25c6470
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385095][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385108][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae25c6470
DIAG [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed database context to 'XXXXXXXX'.

[ODBC][26][1715065291.385144][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae2719370
[ODBC][26][1715065291.385155][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae2719370
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385168][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385180][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae2719370
DIAG [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed language setting to us_english.

[ODBC][26][1715065291.385268][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae27197c0
[ODBC][26][1715065291.385280][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae27197c0
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385289][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385299][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae27197c0
[ODBC][26][1715065291.385313][SQLDriverConnect.c][1809]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DRIVER=ODBC Driver 18 for SQL Server;SERVER=tcp:XXXXXXXX....]
[ODBC][26][1715065291.385323][SQLGetFunctions.c][151]
Entry:
Connection = 0xaaaae2576180
Id = SQLMoreResults
Supported = 0xaaaabc910014
[ODBC][26][1715065291.385332][SQLGetFunctions.c][209]
Exit:[SQL_SUCCESS]
Supported = 0xaaaabc910014 -> 1
pyodbc 
# cat odbctrace.log
[ODBC][36][1715065803.747851][__handles.c][499]
                Exit:[SQL_SUCCESS]
                        Environment = 0xaaab01c42a00
[ODBC][36][1715065803.747898][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0xaaab01c42a00
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = 4
[ODBC][36][1715065803.747906][SQLSetEnvAttr.c][381]
                Exit:[SQL_SUCCESS]
[ODBC][36][1715065803.748002][SQLDrivers.c][235]
                Entry:
                        Environment = 0xaaab01c42a00
                        Direction = 2
[ODBC][36][1715065803.748034][SQLDrivers.c][510]
                Exit:[SQL_SUCCESS]
[ODBC][36][1715065803.748041][SQLDrivers.c][235]
                Entry:
                        Environment = 0xaaab01c42a00
                        Direction = 1
[ODBC][36][1715065803.748054][SQLDrivers.c][510]
                Exit:[SQL_NO_DATA]
[ODBC][36][1715065803.748086][SQLAllocHandle.c][395]
                Entry:
                        Handle Type = 2
                        Input Handle = 0xaaab01c42a00
                UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][36][1715065803.748116][SQLAllocHandle.c][531]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0xaaab01c45ea0
[ODBC][36][1715065803.749377][SQLDriverConnectW.c][298]
                Entry:
                        Connection = 0xaaab01c45ea0
                        Window Hdl = (nil)
                        Str In = ["DRIVER={ODBC Driver 18 for SQL Server};SERVER=tcp:XXXXXXX,1433;DATABASE=XXXXXXX;UID=XXXXX...][length = 151 (SQL_NTS)]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = (nil)
                        Completion = 0
[ODBC][36][1715065803.749392][SQLDriverConnectW.c][658]Error: IM002
[ODBC][36][1715065803.749427][SQLGetDiagRecW.c][535]
                Entry:
                        Connection = 0xaaab01c45ea0
                        Rec Number = 1
                        SQLState = 0xffffda46b660
                        Native = 0xffffda46b63c
                        Message Text = 0xaaab01c585e0
                        Buffer Length = 1023
                        Text Len Ptr = 0xffffda46b63a
[ODBC][36][1715065803.749439][SQLGetDiagRecW.c][596]
                Exit:[SQL_SUCCESS]
                        SQLState = [IM002]
                        Native = 0xffffda46b63c -> 0 (32 bits)
                        Message Text = [[unixODBC][Driver Manager]Data source name not found and no default driver specified]
[ODBC][36][1715065803.749451][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0xaaab01c45ea0
[ODBC][36][1715065803.749458][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]

Some notable differences are the length of the connection string does seem to differ for some reason (which it shouldn't). Notably, the connection string seems to be enclosed with []. I have printed the connection string the row prior to connecting, such as:

print(config["db_connection_string"])
self.conn = pyodbc.connect(config["db_connection_string"])

The connection string is then printed in the expected format without the [].

Could this be it?

gordthompson commented 6 months ago

The square brackets are normal; they are how unixODBC formats the string values in the trace file.

However, the connection string is enclosed in double-quotes for pyodbc but not for isql:

isql

Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=XXXXXX,1433;DATABASE=XXXXXX;UID=XXXXXX...][length = 149 (SQL_NTS)]

pyodbc

Str In = ["DRIVER={ODBC Driver 18 for SQL Server};SERVER=tcp:XXXXXXX,1433;DATABASE=XXXXXXX;UID=XXXXX...][length = 151 (SQL_NTS)]

That's the difference.

AlexVialaBellander commented 6 months ago

I sincerely apologise for the oversight on my part. It was a foolish error that I should not have missed. Thank you for your patience and support 🙏🏽