6

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.

Community
  • 1
  • 1
Batman
  • 8,571
  • 7
  • 41
  • 80

2 Answers2

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
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

Community
  • 1
  • 1
Jens
  • 467
  • 1
  • 5
  • 13
  • 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. – Gord Thompson Jul 19 '16 at 16:56
  • 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' ) – Jens Jul 21 '16 at 10:17
  • 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. – Gord Thompson Jul 21 '16 at 12:14
  • 1
    Okay! Now it I got it working with Python 2.7. I was using Sql Server Express and needed to enable TCP/IP. – Jens Jul 25 '16 at 09:51