如何使用Windows身份验证使用Python从另一个域中的Windows工作站连接到MS SQL Server [英] How to use windows authentication to connect to MS SQL server from windows workstation in another domain with Python

问题描述

我正在尝试使用Python 3.4和pyodbc模块连接到Windows Server 2008 R2上从Windows Server 2008 R2安装在Windows server 2003上的SQL server 2000.这些服务器位于不同的AD域中. Windows only在SQL Server上启用了身份验证，我无法更改它.

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.

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)")

存在一些问题，例如 ，建议在pyodbc连接中添加trusted_connection='yes'参数以支持Windows身份验证，但是在这种情况下，这无济于事，因为使用此选项时，将使用本地凭据，而我需要显式提供凭据，因为原始工作站位于一个不同的AD域.

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.

使用SQL Server驱动程序在ODBC Data Source Administrator中在ODBC Data Source Administrator中创建User DSN失败，出现上述相同的错误.

Creation of User DSN in ODBC Data Source Administrator with SQL Server driver fails with the same error mentioned above.

有没有办法使这项工作成功?

Is there a way to make this work?

与此同时，我从 http://sourceforge.net/projects/freetdswindows/安装了Windows的FreeTDS驱动程序和使用tsql实用程序的连接测试确实有效:

Meanwhile I installed FreeTDSdriver for Windows from http://sourceforge.net/projects/freetdswindows/ and connection test using tsql utility does work:

tsql -S sql.my-domain.local -U my-domain.local\testuser -P password

但是ODBC Data Source Administrator中没有FreeTDS驱动程序. FreeTDS驱动程序通常与unixODBC一起使用.可以在Windows环境中将pyodbc与该驱动程序一起使用吗?

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?

更新:

事实证明，上面提到的FreeTDS二进制文件也包含unixODBC.按说明进行freetds.conf，odbc.ini和odbcinst.ini的配置，例如，

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:

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

Pyodbc仅了解ODBC Data Source Administrator中可用的驱动程序:

Pyodbc only knows about drivers available in ODBC Data Source Administrator:

有两种前进的方式.第一种选择是使ODBC Data Source Administrator了解FreeTDS驱动程序.为实现此目的，需要在注册表项HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers中创建一个名称为FreeTDS且值为Installed的新值.然后，在HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中创建一个新的键FreeTDS，并将FreeTDS驱动程序的设置设置为此注册表项中的字符串值.

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.

此过程完成后，FreeTDS驱动程序在ODBC Data Source Administrator中可用，但连接仍然失败.尝试用FreeTDS在ODBC Data Source Administrator中创建User DSN失败，并显示错误代码193，这是由于64位ODBC Data Source Administrator和32位版本FreeTDS的不兼容而引起的.我没有64位版本的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.

另一个选择是使pyodbc使用另一个驱动程序管理器(unixODBC)代替ODBC Data Source Administrator.尚不知道该如何解决.

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.

推荐答案

我最终使用了 pymssql 版本2.1.3，安装有从 http://www获得的滚轮.lfd.uci.edu/〜gohlke/pythonlibs/#pymssql .它包含FreeTDS，并且可以直接使用:

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:

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')

这篇关于如何使用Windows身份验证使用Python从另一个域中的Windows工作站连接到MS SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！