Windows Authentication With SQLAlchemy and pymssql

Question

I want to create trusted connections to a SQL Server database, using pymssql. Given that that code is going to be shared though, I don't want users entering their usernames, or passwords. I've found documentation that has conflicting information about how to achieve this.

What I want to be able to do is something like

engine = create_engine('mssql+pymssql://<Server>/<Database>?trusted=True')

Some things have said use trusted_connection=yes, others say use trusted=True. Neither of these options is working for me. Each time I try to use the engine I get an error saying that trusted or trusted_connection is an unexpected keyword argument.

I'm using SQLAlchemy version 1.0.9, and pymssql version 2.1.1.

Answered here with pyodbc stackoverflow.com/a/7399585/3406189 — grofte
– grofte, Commented Jan 16, 2019 at 12:34

Gord Thompson · Accepted Answer · 2016-07-19 17:31:44Z

6

I just tested this on a Windows machine with

Python 2.7.11, SQLAlchemy 1.0.11, and pymssql 2.1.1, and
Python 3.5.1, SQLAlchemy 1.0.14, and pymssql 2.1.2 (with FreeTDS 0.95.83 DLLs)

and it worked fine for me:

from sqlalchemy import create_engine
conn_str = r'mssql+pymssql://(local)\SQLEXPRESS/myDb'
engine = create_engine(conn_str)
connection = engine.connect()
result = connection.execute("SELECT SYSTEM_USER AS me")
row = result.fetchone()
print(row['me'])

And, even when the SQL Browser service was stopped on that machine, this connection string worked

conn_str = r'mssql+pymssql://localhost:52865/myDb'

So, at least on Windows machines, simply omitting the username and password will allow connections to the SQL Server via Windows authentication.

edited Jul 19, 2016 at 17:31

answered Jul 19, 2016 at 16:54

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:09:32Z

0

In case anyone else stumbles into this one:
It seems like its no longer possible to use "Trusted", or windows auth, with pymssql.

I just attempted this and found a bunch of threads with dismayed users from the last couple of years. For example: pymssql windows authentication.

Edit: Resolved after enabling TCP/IP for the local SQL Server Express

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Jul 19, 2016 at 12:14

Jens

4671 gold badge5 silver badges13 bronze badges

4 Comments

Gord Thompson Over a year ago

Maybe things have changed since those comments were written, but when I tested it just now it worked for me. See my answer for details.

Jens Over a year ago

I've tried following your approach but I cant get it to work. I'm not using SQLAlchemy though. ex: conn = pymssql.connect(server='localhost', database='TestDB' )

Gord Thompson Over a year ago

I just tested cnxn = pymssql.connect(server='localhost', port='52865', database='myDb') and it worked for me with Python 3.5.1 and pymssql 2.1.2.

Jens Over a year ago

Okay! Now it I got it working with Python 2.7. I was using Sql Server Express and needed to enable TCP/IP.

Collectives™ on Stack Overflow

Windows Authentication With SQLAlchemy and pymssql

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related