Cannot connect to SQL server from python using Active Directory Authentication

Question

I am using pymssql library to connect python to Sql Server. I can connect using windows/sql server authentication. I want to connect using Active Directory Authentication.

Tried the below connection string. But it fails with error :

unexpected keyword authentication


conn = pymssql.connect(server='adventureworks.database.windows.net', authentication = 'Active Directory Password',user='[email protected]',password='Enterpasswordhere', database='dbo')

Answer 1

Update 2019-11-01: The "sign-in assistant" appears to be part of Windows Essentials, which has been discontinued and is no longer available for download.

---

You can do this using ODBC Driver 13.1 for SQL Server and the Microsoft Sign-In assistant if you are on a Windows machine.

1. install ODBC Driver 13.1 for SQL Server
   https://www.microsoft.com/download/details.aspx?id=53339

2. install sign-in assistant (link broken)
   http://go.microsoft.com/fwlink/?LinkId=234947

3. test connection

   import pyodbc
   server = servername ending in windows.net
   database = database
   username = username
   password = password
   tablename = tablename

   driver= '{ODBC Driver 13 for SQL Server}’
   cnxn = pyodbc.connect('DRIVER=‘ + driver +
   ';PORT=1433;SERVER=‘ + server +
   ';PORT=1443;DATABASE=‘+database+
   ';UID='+username+
   ';PWD='+ password +
   ';Authentication=ActiveDirectoryPassword’)

   cursor = cnxn.cursor()
   cursor.execute("SELECT TOP 20 * from ” + tablename)
   row = cursor.fetchone()

   while row:

   print str(row[0]) + " " + str(row[1])
   row = cursor.fetchone()