How to connect Python's pyodbc to MS SQL using AD Universal Authentication?

Question

I'm connecting to two databases the first one uses 'SQL Server Authentication' the second which is the issue uses 'Active Directory Universal Authentication'.

Running this on Win 7, python 2.7.12, pyodbc v4.0.17, Microsoft Online Services Sign-in Assistant is installed and I normally access the database from MSSMS 2016.

For the first database I use the following, which works:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ourDatabase.database.windows.net;PORT=1433;DATABASE=ourDataBaseName;UID=USERID;PWD=PASSWORD')

The second database I've tried all of these, which don't work:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=.\ourDatabase.database.windows.net;DATABASE=ourDataBaseName;[email protected];PWD=MyPass;Trusted_Connection=yes')

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ourDatabase.database.windows.net;DATABASE=ourDataBaseName;[email protected];PWD=MyPass;Trusted_Connection=yes')

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ourDatabase.database.windows.net;DATABASE=ourDataBaseName;UID=user;PWD=MyPass;Trusted_Connection=yes')

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.\ourDatabase.database.windows.net;DATABASE=ourDataBaseName;[email protected];PWD=MyPass;Trusted_Connection=yes')

What am I missing here?

Have you seen this Microsoft Docs article?

Gord Thompson
– Gord Thompson

2017-06-28 18:42:46 +00:00
Commented Jun 28, 2017 at 18:42 — Gord Thompson
– Gord Thompson, Commented Jun 28, 2017 at 18:42

Jose Peña · Accepted Answer · 2017-07-23 10:31:48Z

8

Have you tried this:

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=YOURSERVER;DATABASE=YOURDATABASE;Trusted_Connection=yes')

Be sure to use a driver that is available in the computer you're using. To check which you can use:

pyodbc.drivers()

Also, chances are that if you're on a work computer, you already have the obdc administrator loaded with a connection pre-configured. To check you can search on the windows menu for 'odbc administration' if you open up and there's the connection you want, you can use it and then just call it:

cnxn = pyodbc.connect('DSN=YOURCONNECTIONNAME')

answered Jul 23, 2017 at 10:31

Jose Peña

813 bronze badges

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

Collectives™ on Stack Overflow

How to connect Python's pyodbc to MS SQL using AD Universal Authentication?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related