CODEWITHSUNDEEP
pythonsql-server
HadoopAddict
HadoopAddict

Reputation: 225

Cannot connect to SQL server from python using Active Directory Authentication

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')

Upvotes: 5

Views: 8127

Answers (2)

Marissa Saunders
Marissa Saunders

Reputation: 191

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()

Upvotes: 7

enderland
enderland

Reputation: 14145

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'
)

Upvotes: 0

Related Questions