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.

Answer 1

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.