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
Answer
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