I'm trying to connect to SQL server 2000 installed on Windows server 2003 from Windows Server 2008 R2 using Python 3.4 and pyodbc module. Those servers are in different AD domains. Windows only authentication is enabled on SQL server and I can't change that.
我正在尝试使用Python 3.4和pyodbc模块从Windows Server 2008 R2连接到Windows Server 2003上安装的SQL Server 2000。这些服务器位于不同的AD域中。 SQL服务器上启用了Windows仅身份验证,我无法更改。
drv = '{SQL server}'
svr = 'sql.my-domain.local'
usr = 'my-domain.local\testuser'
pwd = 'password'
db = 'testdb'
pyodbc.connect(driver=drv, server=svr, user=usr, password=pwd, database=db)
The connection above fails with the following error:
上面的连接失败,出现以下错误:
pyodbc.Error: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQLServer]
Login failed for user 'svx-iroot.local\\sqlexecutive'.
Reason: Not associated with a trusted SQL Server connection. (18452) (SQLDriverConnect)")
There are some questions, for example this one, suggesting to add trusted_connection='yes' argument to pyodbc connection for support of windows authentication but in this case it does not help because with this option local credentials are used and I need to provide credentials explicitly because originating workstation is in a different AD domain.
有一些问题,例如这个问题,建议在pyodbc连接中添加trusted_connection ='yes'参数以支持Windows身份验证,但在这种情况下它没有帮助,因为使用此选项使用本地凭据并且我需要明确提供凭据因为始发工作站位于不同的AD域中。
Creation of User DSN in ODBC Data Source Administrator with SQL Server driver fails with the same error mentioned above.
在ODBC数据源管理器中使用SQL Server驱动程序创建用户DSN失败,并出现上述相同的错误。
Is there a way to make this work?
有没有办法让这项工作?
Meanwhile I installed FreeTDSdriver for Windows from http://sourceforge.net/projects/freetdswindows/ and connection test using tsql utility does work:
同时我从http://sourceforge.net/projects/freetdswindows/安装了FreeTDSdriver for Windows,使用tsql实用程序进行连接测试确实有效:
tsql -S sql.my-domain.local -U my-domain.local\testuser -P password
But FreeTDSdriver is not available in ODBC Data Source Administrator. FreeTDS driver is traditionally used with unixODBC. Is it possible to use this driver in Windows environment with pyodbc?
但是ODBC数据源管理器中没有FreeTDSdriver。 FreeTDS驱动程序传统上与unixODBC一起使用。是否可以在Windows环境中使用此驱动程序与pyodbc?
Update:
It turns out FreeTDS binaries mentioned above include unixODBC as well. Configuration of freetds.conf, odbc.ini and odbcinst.ini was made like described, for example, here. But at this point I don't have understanding how pyodbc is supposed to know that FreeTDS driver exists. And indeed connection attempt with FreeTDS driver fails with the following error:
事实证明,上面提到的FreeTDS二进制文件也包括unixODBC。 freetds.conf,odbc.ini和odbcinst.ini的配置就像在这里描述的那样。但在这一点上,我不知道pyodbc应该如何知道FreeTDS驱动程序存在。事实上,与FreeTDS驱动程序的连接尝试失败,并出现以下错误:
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Pyodbc only knows about drivers available in ODBC Data Source Administrator:
Pyodbc只知道ODBC数据源管理器中可用的驱动程序:
There are 2 ways to move forward. First option is to make ODBC Data Source Administrator aware of FreeTDS driver. To achieve that a new value needs to be created in registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers with name FreeTDS and value Installed. Then a new key FreeTDS is created in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI and settings for FreeTDS driver are set as string values in this registry key.
有两种方法可以向前发展。第一个选项是使ODBC数据源管理员了解FreeTDS驱动程序。为此,需要在名为FreeTDS且值为Installed的注册表项HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ ODBCINST.INI \ ODBC驱动程序中创建新值。然后在HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ ODBCINST.INI中创建一个新的密钥FreeTDS,并将FreeTDS驱动程序的设置设置为此注册表项中的字符串值。
After completion of this procedure FreeTDS driver became available in ODBC Data Source Administrator but connection still failed. Attempt to create User DSN in ODBC Data Source Administrator with FreeTDS fails with error code 193 which is caused by incompatibility of 64 bit ODBC Data Source Administrator and 32 bit version FreeTDS. I don't have 64 bit version of FreeTDS available. Potentially it could be possible to compile it from source.
完成此过程后,FreeTDS驱动程序在ODBC数据源管理器中可用,但连接仍然失败。尝试在ODBC数据源管理器中使用FreeTDS创建用户DSN失败,错误代码193由64位ODBC数据源管理器和32位版本FreeTDS的不兼容性引起。我没有64位版本的FreeTDS。可能有可能从源代码编译它。
Another option is to make pyodbc use another driver manager (unixODBC) instead of ODBC Data Source Administrator. Don't know how to approach that yet.
另一个选择是使pyodbc使用另一个驱动程序管理器(unixODBC)而不是ODBC数据源管理器。不知道如何处理。
1 个解决方案
#1
2
I ended up using pymssql version 2.1.3 installed with a wheel obtained from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql. It has FreeTDS included and it worked right out of the box:
我最终使用了从http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql获得的*安装的pymssql 2.1.3版。它包含FreeTDS,开箱即用:
import pymssql
conn = pymssql.connect(
host=r'sql.my-domain.local',
user=r'my-domain.local\testuser',
password='password',
database='testdb'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM testtable')
#1
2
I ended up using pymssql version 2.1.3 installed with a wheel obtained from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql. It has FreeTDS included and it worked right out of the box:
我最终使用了从http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql获得的*安装的pymssql 2.1.3版。它包含FreeTDS,开箱即用:
import pymssql
conn = pymssql.connect(
host=r'sql.my-domain.local',
user=r'my-domain.local\testuser',
password='password',
database='testdb'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM testtable')