In Python, Is it possible to connect Azure SQL Server using Active Directory Password Authentication?

Question

I have tried to connect sql server using pyodbc library,

driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+host+';PORT=1443;DATABASE='+database+';UID='+user+';PWD='+ password+';Authentication=ActiveDirectoryPassword')

In the above code, I have choosen ActiveDirectoryPassword(AAD) as my authentication type as I have AAD access only.But getting the following error message.

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

When I changed the driver to ODBC Driver 13, getting different error message.

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

This time I was getting the following error message.

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: The Microsoft Online Services Sign-In Assistant could not be found. Install it from http://go.microsoft.com/fwlink/?LinkId=234947. If it is already present, repair the installation. [2].  (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection (2); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Invalid connection string attribute (0)')

Is it possible to connect azure sql server using pyodbc with AAD authentication? if possible what is the proper way to authenticate sql server with AAD?

Solution

Firstly, according to the subsection Additional considerations of the offical document Use Azure Active Directory Authentication for authentication with SQL, as below, pyodbc could connect Azure SQL Database with AAD authentication.

ODBC version 13.1 supports Azure Active Directory authentication however bcp.exe cannot connect using Azure Active Directory authentication because it uses an older ODBC provider.

Then, there is an offical document for SQL Server Using Azure Active Directory with the ODBC Driver introduced how to and some limitations if not on Windows.

The Microsoft ODBC Driver for SQL Server with version 13.1 or above allows ODBC applications to connect to an instance of SQL Azure using a federated identity in Azure Active Directory with a username/password, an Azure Active Directory access token, or Windows Integrated Authentication (Windows driver only). For the ODBC Driver version 13.1, the Azure Active Directory access token authentication is Windows only. The ODBC Driver version 17 and above support this authentication across all platforms (Windows, Linux and Mac).

And it

accomplished through the use of new DSN and connection string keywords, and connection attributes.

You need to follow the document to configure some settings to enable new DSN, then to connect SQL Azure with the connection string.

Driver={ODBC Driver 13 for SQL Server};Server={server};UID=myuser;PWD=myPass;Authentication=ActiveDirectoryPassword

and note this.

To connect using an Azure Active Directory account username and password, specify Authentication=ActiveDirectoryPassword in the connection string and the UID and PWD keywords with the username and password, respectively.

Meanwhile, as other resources, these seems to be old, but a blog introduced in details for configuration Multi-Factor Authentication in SQL Azure, and a markdown doc introduced for Connecting to SQL Database By Using Azure Active Directory Authentication.

Additionally, for Linux and Mac, there is a blog apply for another solution which may be helpful to build the connection of SQL Server.

Hope it helps.

Comments

[user1023514]

Thanks for the more information on AAD authentication. I have tried creating the DSN file on my local system. But getting same error message as above. SQLState: '08001' SQL Server Error: 2 [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: The Microsoft Online Services Sign-In Assistant could not be found. Install it from http://go.microsoft.com/fwlink/?LinkId=234947. If it is already present, repair the installation. [2].

[user1023514]

Through SSMS i was able to access the database using AAD authentication, but not from pyodbc

[user1023514]

after installing windows sign-in assistant the error is gone, and now able to get the connection object. here is the reference i found link

[user1023514]

But not sure exactly why i need that additional plugin