pymssql windows authentication

问题:

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:

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.

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.

---

According to my research, including the links above, there are two ways to use windows authentication with the pymssql module that are supposed to work.

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 _mssql module.

---

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

---

Other questions on the topic:

pymssql: How to use windows authentication when running on a non-windows box

Unable to connect using pymssql with windows authentication

https://stackoverflow.com/questions/27692366/mssql-python-windows-authentication

回答1:

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:

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

回答2:

Seems it work now. Python 3.6, Windows 10.

conn = pymssql.connect(server='(local)', database='DbName')

回答3:

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.

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.

回答4:

If on RHEL, set the FREETDSCONF environment variable. Pymssql looks in the wrong places by default:

os.environ["FREETDSCONF"] = "/etc/freetds.conf"

回答5:

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 SQLCMD tool from Microsoft, combined with PsExec.

The PsExec I executed with the elevated (-h) and load profile (-e) flags. Using the full user name DOMAIN\USERNAME.

The SQLCMD I executed with the trusted connection -E flag.

The rest is up to you.