使用“Windows 凭据"在 Linux 上使用 python 连接到 MS SQL Server [英] Connecting to MS SQL Server using python on linux with 'Windows Credentials'

问题描述

有什么方法可以使用 Windows 域凭据在 Linux 上通过 python 连接到 MS SQL Server 数据库吗?

我可以使用 Windows Credentials 从我的 Windows 机器完美连接，但尝试从带有 pyodbs + freetds + unixodbc 的 linux python 中执行相同操作

>>import pyodbc>>conn = pyodbc.connect("DRIVER={FreeTDS};SERVER=servername;UID=username;PWD=password;DATABASE=dbname")

导致此错误:

class 'pyodbc.Error'>: ('28000', '[28000] [unixODBC][FreeTDS][SQL Server]登录错误.(20014) (SQLDriverConnectW)')

我确定密码写对了，但我尝试了许多不同的用户名组合:

域\用户名域\用户名

甚至

UID=用户名；DOMAIN=域

无济于事.有什么想法吗?

解决方案

正如其中一条评论所指出的，这个答案现在已经很陈旧了.我经常使用 GSSAPI 从 Linux 到 SQL Server 2008 R2 进行身份验证，但主要使用 EasySoft ODBC 管理器和(商业)EasySoft ODBC SQL Server 驱动程序.

在 2009 年初，我和一位同事设法使用 GSSAPI(Kerberos 凭据)通过 DBB::Perl 通过与特定版本的 MIT kerberos 库链接的 FreeTDS 构建连接到了来自 Solaris 10 的 SQL Server 2005 实例.诀窍是——这有点难以相信，但我已经通过查看 FreeTDS 源代码验证了它——指定一个零长度用户名.如果 user_name 字符串的长度为 0，则 FreeTDS 代码将尝试使用 GSSAPI(如果该支持已被编译).我无法通过 Python 和 pyodbc 执行此操作，因为我无法找到让 ODBC 传递零长度用户名的方法.

在 perl 代码中 .. 有多种机会损坏 wrt 配置文件，例如 .freetds.conf 等.我似乎记得主体必须是大写的，但我的笔记似乎不同意这一点.

<前>$serverprincipal = 'MSSQLSvc/foo.bar.yourdomain.com:[e-mail]';$dbh = DBI->connect("dbi:Sybase:server=THESERVERNAME;kerberos=$serverprincipal", '', '');

您必须知道如何使用 setspn 实用程序才能让 SQL Server 服务器使用适当的安全主体名称.

我对 Kerberos 方面一无所知，因为我们的环境是由一位彻头彻尾的 Kerberos 大师设置的，并且在 SQL Server 运行的 AD 域和我的客户端运行的 Kerberos 域.

有一些代码 http://code.google.com/p/libsqljdbc-auth/ 执行从 Linux 到 SQL Server 的 GSSAPI 身份验证，但它只是 Java.作者(似乎知道他的东西)也为 jTDS 项目提供了一个类似的补丁，该补丁适用于内置 GSSAPI 的更新的 Java 版本.

所以所有的部分都在那里，只是试图让它们一起工作是一个大混乱.我发现 pyodbc 到 unixODBC 到 FreeTDS odbc 到 TDS 的集成很难跟踪/调试.perl 的东西，因为它是 CT-Lib 顶部的一个非常薄的包装器，所以更容易使用.

Is there any way to connect to an MS SQL Server database with python on linux using Windows Domain Credentials?

I can connect perfectly fine from my windows machine using Windows Credentials, but attempting to do the same from a linux python with pyodbs + freetds + unixodbc

>>import pyodbc
>>conn = pyodbc.connect("DRIVER={FreeTDS};SERVER=servername;UID=username;PWD=password;DATABASE=dbname")

results in this error:

class 'pyodbc.Error'>: ('28000', '[28000] [unixODBC][FreeTDS][SQL Server]Login incorrect. (20014) (SQLDriverConnectW)')

I'm sure the password is written correctly, but I've tried many different combinations of username:

DOMAIN\username
DOMAIN\\username

or even

UID=username;DOMAIN=domain

to no avail. Any ideas?

解决方案

As pointed out in one of the comments, this answer is quite stale by now. I regularly and routinely use GSSAPI to authenticate from Linux to SQL Server 2008 R2 but mostly with the EasySoft ODBC manager and the (commercial) EasySoft ODBC SQL Server driver.

In early 2009, a colleague and I managed to connect to a SQL Server 2005 instance from Solaris 10 using GSSAPI (Kerberos credentials) using DBB::Perl over a FreeTDS build linked against a particular version of the MIT kerberos libraries. The trick was -- and this is a little bit difficult to believe but I have verified it by looking through the FreeTDS source code -- to specify a zero-length user_name. If the length of the user_name string is 0 then the FreeTDS code will attempt to use GSSAPI (if that support has been compiled in). I have not been able to do this via Python and pyodbc as I could not figure out a way of getting ODBC to pass down a zero-length user_name.

Here in the perl code .. there are multiple opportunities for breakage wrt configuration files such as .freetds.conf etc. I seem to recall that the principal had to be in uppercase but my notes seem to be in disagreement with that.

$serverprincipal = 'MSSQLSvc/foo.bar.yourdomain.com:[e-mail]';
$dbh = DBI->connect("dbi:Sybase:server=THESERVERNAME;kerberos=$serverprincipal", '', '');

You will have to know how to use the setspn utility in order to get the SQL Server server to use the appropriate security principal name.

I do not have any knowledge of the kerberos side of things because our environment was set up by an out and out Kerberos guru and has fancy stuff like mutual trust set up between the AD domain that the SQL Server is running in and the Kerberos domain that my client was running in.

There is some code http://code.google.com/p/libsqljdbc-auth/ which does GSSAPI authentication from Linux to SQL Server but it is Java only. The author (who seems to know his stuff) also has contributed a similar patch to the jTDS project which works with more recent versions of Java that have GSSAPI built in.

So the pieces are all there, it is just a big tangled mess trying to get them all to work together. I found the pyodbc to unixODBC to FreeTDS odbc to TDS integration pretty hard to trace/debug. The perl stuff because it was a pretty thin wrapper on top to CT-Lib was much easier to get going.

这篇关于使用“Windows 凭据"在 Linux 上使用 python 连接到 MS SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！