Connect to Azure SQL in Python with MFA Active Directory Interactive Authentication without using Microsoft.IdentityModel.Clients.ActiveDirectory dll

Question

To connect to Azure SQL Database using MFA (which is in SSMS as "Active Directory - Universal") Microsoft recommends and currently only has a tutorial on connecting with C# using Microsoft.IdentityModel.Clients.ActiveDirectory

Setting Authentication='Active Directory Interactive'; in a regular ODBC connection string from Python or Powershell results in the error

Cannot find an authentication provider for 'ActiveDirectoryInteractive'

This seems to be because per Microsoft's example code at https://learn.microsoft.com/en-us/azure/sql-database/active-directory-interactive-connect-azure-sql-db you need to explicitly create your own auth provider class when creating the connection:

        public static void Main(string[] args)
        {
            var provider = new ActiveDirectoryAuthProvider();

            SC.SqlAuthenticationProvider.SetProvider(
                SC.SqlAuthenticationMethod.ActiveDirectoryInteractive,
                //SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated,  // Alternatives.
                //SC.SqlAuthenticationMethod.ActiveDirectoryPassword,
                provider);

            Program.Connection();
        }

I want to connect with pyodbc, so I can't implement the ActiveDirectoryInteractive provider.

Is there any way to generically acquire a token using OAuth and use it in the connection string, or otherwise implement the ActiveDirectoryInteractive provider without using .NET?

Mckale

Answer 1

ODBC driver support the MFA authentication, but windows only:

I tested in Python pyodbc and it also works.

Here is my pyodbc code which connect to my Azure SQL database with AAD MFA authentication:

import pyodbc
server = '***.database.windows.net'
database = 'Mydatabase'
username ='****@****.com'
Authentication='ActiveDirectoryInteractive'
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER='+driver+
                      ';SERVER='+server+
                      ';PORT=1433;DATABASE='+database+
                      ';UID='+username+
                      ';AUTHENTICATION='+Authentication
                      )

print(conn)

It works well in my windows environment.

Hope this helps.

Curriculum

Depending on your setup you might not need to provide the password in your connection string. If I do, I get the following error: `[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. You simply insert the password intot he Windows pop-up. – Surfboat

Answer 2

Authenticating using AAD MFA from MacOS / Linux

I was facing the same problem but on MacOs. As described above, the ODBC option using 'ActiveDirectoryInteractive' is only available for Windows.

If you want to see a more detailed version of this post, please check out my post on Medium, otherwise keep reading. ;)

Requirements

In order to connect to the database using AAD MFA, I also used pyodbc but with an access token. To get the token there are a few things that you'll need to do:

1. Azure CLI

2. Microsoft ODBC Driver for SQL Server (Linux-MAC)

Instructions

Before you run the code below, you must authenticate using azure cli, to do so run from cmd : az login

from azure.identity import AzureCliCredential
import struct
import pyodbc 

# input params
server = '<your server address>'
database = '<database name>'
query = 'SELECT * from dbo.Address;'

# Use the cli credential to get a token after the user has signed in via the Azure CLI 'az login' command.
credential = AzureCliCredential()
databaseToken = credential.get_token('https://database.windows.net/')

# get bytes from token obtained
tokenb = bytes(databaseToken[0], "UTF-8")
exptoken = b'';
for i in tokenb:
 exptoken += bytes({i});
 exptoken += bytes(1);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;

# build connection string using acquired token
connString = "Driver={ODBC Driver 17 for SQL Server};SERVER="+server+";DATABASE="+database+""
SQL_COPT_SS_ACCESS_TOKEN = 1256 
conn = pyodbc.connect(connString, attrs_before = {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct});

# sample query
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Troubleshooting

Some people might experience different behavior using the code above depending on the version of the ODBC driver and MacOS.

1. Make sure to always use the latest version of the ODBC driver
2. If your connection returns an error similar to 'SSL Provider: [error:0A000086:SSL routines::certificate verify failed:unable to get local issuer certificate] (-1)'. Adding TrustServerCertificate=Yes; to the connection string can help.

References

https://pypi.org/project/azure-identity/

https://github.com/AzureAD/azure-activedirectory-library-for-python/wiki/Connect-to-Azure-SQL-Database

Omnivorous

This is the only method I found on the whole Stackoverflow that actually works. Thank you. – Divinity

You are a true hero! However, I had to add .default to the URL database.windows.net so it becomes database.windows.net/.default – Automate

Everton, your reply solved a problem that I'd been working on for two years! Thank you so much! – Fogy

A rare case of something that just works after you copy and paste it directly into your editor. Hats off to Mr. Barciela! – Bushwhacker

This is excellent. Solves the problem I've been having with db connections in Docker perfectly. Thank you Everton. – Michell

Note for anyone using pandas, to avoid warnings re sqlalchemy, you can use urllib and sqlalchemy to create the engine with params = urllib.parse.quote(connString) engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={0}".format(params), connect_args={'attrs_before': {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct}}) – Michell

You Sir, are an absolute wizard! Thank you for the code as well as the clear, concise explanation. – Kermanshah

I am getting the following error. Any suggestions? pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – Marquet

@tiagoams, are you able to connect to this instance via azure data studio? If so, are you using Mac M2 Chip? – Omnivorous

Thanks for the suggestion Everton, but I think the problem I am having is that I need to install "ODBC Driver 17 for SQL Server" in windows. – Marquet

Yea this is wild that this is the only documented way to do this. See Alexandre Jaouën's answer below for a more concise version. I am connecting successfully on Mac silicon using this solution. – Veranda

Answer 3

If you encode directly in UTF-16 LE, you don't need to use a loop as you did in your example.

Therefore, this will work too.

# get bytes from token obtained
tokenb = bytes(databaseToken[0], "UTF-16-LE")
tokenstruct = struct.pack("=i", len(tokenb)) + tokenb;

Role