I am trying to query a SQL Server 2016 database via a jupyter notebook in VSCode. To do this, I've followed these instructions to install the ODBC drivers.
This is the output of the odbcinst -j command:
unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/myname/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Then I run the following python code in VSCode:
import pyodbc
import pandas as pd
servername = 'emp-edwh01'
dbname = 'DWH_CORE'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+servername+';DATABASE='+dbname)
cursor = cnxn.cursor()
query = '''SELECT * limit 100
FROM dbp.tab_CMA_CMPositions
'''
df = pd.read_sql(query, cnxn, index_col='[primary_key]')
but the output I see is:
OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 (0) (SQLDriverConnect)')
I have done thorough research (went through this, too), deleted and re-installed drivers, double checked all links, etc. But still I can't connect.
In addition, the full log of when I run the connection file on VSCode is:
[ODBC][13195][1728651033.229336][SQLAllocHandle.c][469]
Entry:
Handle Type = 2
Input Handle = 0x7fdf0b3a6400
[ODBC][13195][1728651033.229385][SQLAllocHandle.c][585]
Exit:[SQL_SUCCESS]
Output Handle = 0x7fdeeb188e00
[ODBC][13195][1728651033.230136][SQLDriverConnectW.c][298]
Entry:
Connection = 0x7fdeeb188e00
Window Hdl = 0x0
Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=emp-edwh01;DATABASE=DWH_CORE][length = 74 (SQL_NTS)]
Str Out = 0x0
Str Out Max = 0
Str Out Ptr = 0x0
Completion = 0
Threading Level set from Driver Entry in ODBCINST.INI 99 from '99'
Threading Level set from [ODBC] Section in ODBCINST.INI 0 from '0'
UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'
[ODBC][13195][1728651033.367613][SQLDriverConnectW.c][870]
Exit:[SQL_ERROR]
[ODBC][13195][1728651033.367677][SQLGetDiagRecW.c][535]
Entry:
Connection = 0x7fdeeb188e00
Rec Number = 1
SQLState = 0x30a3f4824
Native = 0x30a3f4818
Message Text = 0x7fdf0c5f2e00
Buffer Length = 1023
Text Len Ptr = 0x30a3f4822
[ODBC][13195][1728651033.367732][SQLGetDiagRecW.c][596]
Exit:[SQL_SUCCESS]
SQLState = [08001]
Native = 0x30a3f4818 -> 0 (32 bits)
Message Text = [[Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see]
[ODBC][13195][1728651033.367851][SQLFreeHandle.c][324]
Entry:
Handle Type = 2
Input Handle = 0x7fdeeb188e00
[ODBC][13195][1728651033.367882][SQLFreeHandle.c][373]
Exit:[SQL_SUCCESS]
EDIT - CERTIFICATES
I use Kerberos to authenticate and connect. And this works successfully through Kerberos when I query directly the same SQL Server 2016 in VSCode.
I've tried running the python script to trust the server certificates:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+servername+';DATABASE='+dbname+';TrustServerCertificate=yes')
Now the error I see is:
OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')
Solved by following these steps.
To the python query, I've added TrustServerCertificate=yes and Trusted_connection=yes: the first one to trust the certificates from the server; the second one to authenticate via the Windows login/password, without giving them as input in the code.
The final connection string is now:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+servername+';DATABASE='+dbname+ \
';TrustServerCertificate=yes; Trusted_connection=yes')
After this, I have made sure to use the right OpenSSL library version. As I have read that the ODBC driver does not support OpenSSL3, I've switched back to OpenSSL1 and I fixed all links.
It didn't work straight after this step, but a reboot was required.