Pyodbc 在旧版本的 sql 驱动程序中出现异常,但在最新版本中失败 [英] Pyodbc giving exception with older version of sql driver but failing with latest version
问题描述
我正在尝试使用 pyodbc 连接 SQL 服务器.在该服务器中禁用 SSL3.0,启用 TLS 1.1 和 1.2.
当我使用最旧的驱动程序 {SQL Server} 时.我正在处理以下异常.
pyodbc.connect('DRIVER={SQL Server};SERVER=XX;DATABASE=XX;UID=XX;PWD=XX;'
('08001', '[08001] [Microsoft][ODBC SQL Server 驱动程序][DBNETLIB]SSL 安全错误 (18) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server 驱动程序][DBNETLIB]ConnectionOpen (SECDoClientHandshake()). (772)')
但是,作为一种解决方法,当我使用最新的 odbc 驱动程序 {ODBC Driver 17 for SQL Server}
pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=XX;DATABASE=XX;UID=XX;PWD=XX;'
即使使用使用基类 Exception 处理异常的 try catch 块,python 脚本也会崩溃.
以下是崩溃的详细信息:
问题事件名称:APPCRASH应用程序名称:pythonw.exe应用版本:3.8.150.1013应用时间戳:5da4cb37故障模块名称:msodbcsql17.dll故障模块版本:2017.175.2.1故障模块时间戳:5e5e976f异常代码:c0000005异常偏移:00024005操作系统版本:6.1.7601.2.1.0.272.7语言环境 ID:2057附加信息 1:a7aa附加信息 2:a7aa91f17ea749d42a4de3b390fa5b3d附加信息 3:a7aa附加信息 4:a7aa91f17ea749d42a4de3b390fa5b3d
我还通过 ODBC 数据源管理启用了跟踪,并找到了以下跟踪:
<预><代码>pythonw.exe -c a40-1154 输入 SQLSetEnvAttrSQLHENV 0x00000000SQLINTEGER 201 <SQL_ATTR_CONNECTION_POOLING>SQLPOINTER 2 <SQL_CP_ONE_PER_HENV>SQLINTEGER 4pythonw.exe -c a40-1154 退出 SQLSetEnvAttr 并返回代码 0 (SQL_SUCCESS)SQLHENV 0x00000000SQLINTEGER 201 <SQL_ATTR_CONNECTION_POOLING>SQLPOINTER 2 <SQL_CP_ONE_PER_HENV>SQLINTEGER 4pythonw.exe -c a40-1154 输入 SQLAllocHandleSQLSMALLINT 1当通过 cmd 使用 python.exe 运行时.在 cmd 中没有发现任何痕迹,但再次崩溃并显示以下详细信息:
问题签名:问题事件名称:APPCRASH应用程序名称:python.exe应用版本:3.8.150.1013应用时间戳:5da4cb35故障模块名称:msodbcsql17.dll故障模块版本:2017.175.2.1故障模块时间戳:5e5e976f异常代码:c0000005异常偏移:00024005操作系统版本:6.1.7601.2.1.0.272.7语言环境 ID:2057附加信息 1:a7aa附加信息 2:a7aa91f17ea749d42a4de3b390fa5b3d附加信息 3:a7aa附加信息 4:a7aa91f17ea749d42a4de3b390fa5b3d
尝试从SQL Server"更改驱动程序参数到SQL Server Native Client 11.0".这为我带来了成功的连接.
导入pyodbcconn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=<您的服务器名称>;Trusted_Connection=yes')
就我而言,SQL Server"驱动程序参数在某些机器上有效,但在其他机器上无效.我怀疑这是服务器端的 TLS 禁用问题,因为其他机器可以与 pyodbc 连接.我很好奇是否有人知道还有什么可能是原因.
I am trying to connect a SQL server using pyodbc. In that server SSL3.0 is disabled and TLS 1.1 and 1.2 is enabled.
When I am using the oldest driver {SQL Server}. I am getting the below exception handled.
pyodbc.connect('DRIVER={SQL Server};SERVER=XX;DATABASE=XX;UID=XX;PWD=XX;'
('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error (18) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECDoClientHandshake()). (772)')
However, as a workaround when I used the latest odbc driver {ODBC Driver 17 for SQL Server}
pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=XX;DATABASE=XX;UID=XX;PWD=XX;'
The python script is crashing even with a try catch block using baseclass Exception to handle the exception.
Below is the details of crash:
Problem Event Name: APPCRASH
Application Name: pythonw.exe
Application Version: 3.8.150.1013
Application Timestamp: 5da4cb37
Fault Module Name: msodbcsql17.dll
Fault Module Version: 2017.175.2.1
Fault Module Timestamp: 5e5e976f
Exception Code: c0000005
Exception Offset: 00024005
OS Version: 6.1.7601.2.1.0.272.7
Locale ID: 2057
Additional Information 1: a7aa
Additional Information 2: a7aa91f17ea749d42a4de3b390fa5b3d
Additional Information 3: a7aa
Additional Information 4: a7aa91f17ea749d42a4de3b390fa5b3d
I also enabled trace through ODBC Data source Administration and found the below trace:
pythonw.exe -c a40-1154 ENTER SQLSetEnvAttr
SQLHENV 0x00000000
SQLINTEGER 201 <SQL_ATTR_CONNECTION_POOLING>
SQLPOINTER 2 <SQL_CP_ONE_PER_HENV>
SQLINTEGER 4
pythonw.exe -c a40-1154 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x00000000
SQLINTEGER 201 <SQL_ATTR_CONNECTION_POOLING>
SQLPOINTER 2 <SQL_CP_ONE_PER_HENV>
SQLINTEGER 4
pythonw.exe -c a40-1154 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x047FEC6C
pythonw.exe -c a40-1154 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x047FEC6C ( 0x029B1BE0)
pythonw.exe -c a40-1154 ENTER SQLSetEnvAttr
SQLHENV 0x029B1BE0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 4
pythonw.exe -c a40-1154 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x029B1BE0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 4
pythonw.exe -c a40-1154 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x029B1BE0
SQLHANDLE * 0x0041F4B0
pythonw.exe -c a40-1154 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x029B1BE0
SQLHANDLE * 0x0041F4B0 ( 0x029B1C58)
pythonw.exe -c a40-1154 ENTER SQLDriverConnectW
HDBC 0x029B1C58
HWND 0x00000000
WCHAR * 0x04848B34 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x04848B34
SWORD -3
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
When run with python.exe through cmd. No traces found in cmd but again that crashed with below details:
Problem signature:
Problem Event Name: APPCRASH
Application Name: python.exe
Application Version: 3.8.150.1013
Application Timestamp: 5da4cb35
Fault Module Name: msodbcsql17.dll
Fault Module Version: 2017.175.2.1
Fault Module Timestamp: 5e5e976f
Exception Code: c0000005
Exception Offset: 00024005
OS Version: 6.1.7601.2.1.0.272.7
Locale ID: 2057
Additional Information 1: a7aa
Additional Information 2: a7aa91f17ea749d42a4de3b390fa5b3d
Additional Information 3: a7aa
Additional Information 4: a7aa91f17ea749d42a4de3b390fa5b3d
Try changing the driver parameter from "SQL Server" to "SQL Server Native Client 11.0". This led to a successful connection for me.
import pyodbc
conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=<your server name>;Trusted_Connection=yes')
In my case, the "SQL Server" driver parameter was working on some machines but not others. I'm doubtful that it was TLS disablement issue on the server side, since the other machines could connect with pyodbc. I am curious if someone knows what else may be the cause.
这篇关于Pyodbc 在旧版本的 sql 驱动程序中出现异常,但在最新版本中失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!