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')
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.
install ODBC Driver 13.1 for SQL Server
https://www.microsoft.com/download/details.aspx?id=53339
install sign-in assistant (link broken)
http://go.microsoft.com/fwlink/?LinkId=234947
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()
Note that pymssql.connect does not have an 'authentication' parameter. You are passing it that as a named arg, which is invalid, and the reason you see your error.
See this example for connecting using windows authentication:
conn = pymssql.connect(
host=r'dbhostname\myinstance',
user=r'companydomain\username',
password=PASSWORD,
database='DatabaseOfInterest'
)
