Python pymssql Windows 身份验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29349607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pymssql windows authentication
提问by Inbar Rose
The pymssql module used to support windows authentication. Now it seems it does not. Though in some places it still shows that it should work. I have been unable to find a definitive answer to this problem, nor a solution. Most relevant link:
用于支持 Windows 身份验证的 pymssql 模块。现在好像不行了。尽管在某些地方它仍然表明它应该起作用。我一直无法找到这个问题的明确答案，也没有解决方案。最相关的链接：
https://groups.google.com/forum/#!topic/pymssql/QDMLTGBNeU0
https://groups.google.com/forum/#!topic/pymssql/QDMLTGBNeU0
pymssql 1.0 supported it because it made use of and depended on the MS-provided DLL which was part of the SQL Server client stack. This stack was in charge of handling all of the NTLM negotiation and such. This meant, among other things that it was a Windows-only solution.
pymssql 1.0 支持它，因为它使用并依赖于 MS 提供的 DLL，它是 SQL Server 客户端堆栈的一部分。该堆栈负责处理所有 NTLM 协商等。这意味着，除其他外，它是一个仅限 Windows 的解决方案。
I can simulate many sorts of network environments so I have tried many different setups. I am trying to be able to use this script to connect to a remote MSSQL server using windows authentication. And this is the problem.
我可以模拟多种网络环境，因此我尝试了许多不同的设置。我试图能够使用此脚本通过 Windows 身份验证连接到远程 MSSQL 服务器。这就是问题所在。
According to my research, including the links above, there are two ways to use windows authentication with the pymssql module that are supposedto work.
根据我的研究，包括上面的链接，有两种方法可以通过 pymssql 模块使用 Windows 身份验证，它们应该可以工作。
First Method:Using the current users credentials:
第一种方法：使用当前用户凭据：
pymssql.connect(server='server')
# credentials come from active windows session
# some research shows that a "trusted=True" keyword should be provided.
Second Method:Using a given users credentials:
第二种方法：使用给定的用户凭据：
pymssql.connect(server='server', user=r'domain\user', password='pass')
# credentials are given in code and somehow converted to a
# windows authentication in the background
# some research shows that a "trusted=True" keyword should be provided.
The same goes for using the _mssqlmodule.
使用_mssql模块也是如此。
NOTES:
笔记：
- Python version: 2.7.8
- Version of pymssql I am using: 2.1.1
- Version of pymssql that used to support windows authentication: 1.x
- I have tested with (All 64 bit):
- windows 7 professional
- windows 7 home premium
- windows server 2012
- windows server 2012R2
- Python版本：2.7.8
- 我使用的 pymssql 版本：2.1.1
- 曾经支持windows认证的pymssql版本：1.x
- 我已经测试过（所有 64 位）：
- Windows 7 专业版
- Windows 7家庭高级版
- 视窗服务器 2012
- 视窗服务器 2012R2
Other questions on the topic:
关于该主题的其他问题：
pymssql: How to use windows authentication when running on a non-windows box
pymssql：在非 Windows 机器上运行时如何使用 Windows 身份验证
Unable to connect using pymssql with windows authentication
无法通过 Windows 身份验证使用 pymssql 进行连接
https://stackoverflow.com/questions/27692366/mssql-python-windows-authentication
https://stackoverflow.com/questions/27692366/mssql-python-windows-authentication
采纳答案by Inbar Rose
So, I figured I should answer my own question (it's been a few months) with the method that I ultimately used to solve this problem.
所以，我想我应该用我最终用来解决这个问题的方法来回答我自己的问题（已经几个月了）。
Short answer: I used something else.
简短的回答：我用了别的东西。
Longer answer: For testing windows authentication (other than the currently logged on windows user, which does work) I started using SQLCMDtool from Microsoft, combined with PsExec.
更长的答案：为了测试 Windows 身份验证（除了当前登录的 Windows 用户，它确实有效）我开始使用SQLCMDMicrosoft 的工具，结合PsExec.
The PsExec I executed with the elevated(-h)and load profile(-e)flags. Using the full user name DOMAIN\USERNAME.
我使用elevated(-h)和load profile(-e)标志执行的 PsExec 。使用完整的用户名DOMAIN\USERNAME。
The SQLCMD I executed with the trusted connection-Eflag.
我使用trusted connection-E标志执行的 SQLCMD 。
The rest is up to you.
剩下的就看你了。
回答by Patrick Falvey
I had this same challenge recently. I was also using Python 2.7 and windows authentication at first. The only way I was able to connect was by using IronPython and importing the clr module. I'm not sure why it worked and would appreciate an explanation from someone that is knowledgeable on the subject. Some differences are that my server was local and the database inside was formed with 'Entity framework-Code first'. Here is the code that finally connected me to the server.
我最近遇到了同样的挑战。起初我也使用 Python 2.7 和 Windows 身份验证。我能够连接的唯一方法是使用 IronPython 并导入 clr 模块。我不确定它为什么起作用，并且希望了解该主题的人的解释。一些不同之处在于我的服务器是本地的，里面的数据库是用“实体框架代码优先”形成的。这是最终将我连接到服务器的代码。
import clr
clr.AddReference('System.Data')
from System.Data.SqlClient import *
Conn_string = 'data source=Server_Name; initial catalog=Database_Name; trusted_connection=True'
ScheduleConn = SqlConnection(Conn_string)
ScheduleConn.Open()
If this doesn't solve your problem I hope it gets you closer to your solution.
如果这不能解决您的问题，我希望它能让您更接近您的解决方案。
回答by Ed G
I was able to resolve this using python 2.7.11 64 bit, pymssql 2.1.1 win 64, windows 10, sqlserver 2012 with windows authentication by:
我能够使用python 2.7.11 64位，pymssql 2.1.1 win 64，windows 10，sqlserver 2012和windows身份验证解决这个问题：
conn = pymssql.connect(server = 'EDDESKTOP', database = 'baseballData')
And enabling the tcp/ip connection in Sql Server Configuration Manager > Sql Server Network Configuration -> Protocols for MSSQLSERVER-> TCP/IP Enabled
并在 Sql Server Configuration Manager > Sql Server Network Configuration -> Protocols for MSSQLSERVER-> TCP/IP Enabled 中启用 tcp/ip 连接
回答by Igor Tkachenko
Seems it work now. Python 3.6, Windows 10.
似乎现在可以工作了。Python 3.6，Windows 10。
conn = pymssql.connect(server='(local)', database='DbName')
回答by runamoker
If on RHEL, set the FREETDSCONF environment variable. Pymssql looks in the wrong places by default:
如果在 RHEL 上，请设置 FREETDSCONF 环境变量。Pymssql 默认查找错误的位置：
os.environ["FREETDSCONF"] = "/etc/freetds.conf"
