3

I am trying to see if it's possible to connect to SQL Server via Python when the authentication type is:

Azure Active Directory - Universal with MFA support

I can connect to this data base using Azure. The lines below show the credentials needed when connecting through azure data studio:

connection type : Microsoft SQL Server
server : ***.net
Authentication Type : Azure Active Directory - Universal with MFA support
account : ***.***@mycompany.com
database : target
Server group : default

I have looked everywhere on the web, and every thing I see regarding connecting to SQL Server via Python is when the Authentication Type is SQL login. Please help if you know the answer.

Ben Nicholl
  • 33
  • 5

1 Answers1

4

Yes, you can. Refer to this document for more details.

(Windows driver only.) AAD Interactive Authentication uses Azure Multi-factor Authentication technology to set up connection. In this mode, by providing the login ID, an Azure Authentication dialog is triggered and allows the user to input the password to complete the connection. The username is passed in the connection string.

server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive;

I have tested this on my side and my test code is as below.

import pyodbc
server = 'tonytest920.database.windows.net'
database = 'tonytest'
driver = '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server +
                      ';PORT=1433;DATABASE='+database+';Authentication=ActiveDirectoryInteractive;UID=xx@xx.onmicrosoft.com')
cursor = cnxn.cursor()
cursor.execute(
    "select * from Persons")
row = cursor.fetchone()
while row:
    print(str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Note: You need to install adalsql.dll first. Remember to choose ENU\x86\adalsql.msi.

Tony Ju
  • 14,891
  • 3
  • 17
  • 31
  • I'm getting this error: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") – Ben Nicholl Sep 20 '19 at 15:29
  • @BenNicholl For this issue, take a look at this https://stackoverflow.com/questions/44527452/cant-open-lib-odbc-driver-13-for-sql-server-sym-linking-issue – Tony Ju Sep 23 '19 at 01:33