CODEWITHSUNDEEP
azureodbcazure-sql-databasepyodbcmulti-factor-authentication
dmi_
dmi_

Reputation: 1257

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

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?

Upvotes: 18

Views: 29864

Answers (3)

Everton Oliveira
Everton Oliveira

Reputation: 950

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

Upvotes: 31

Alexandre Jaou&#235;n
Alexandre Jaou&#235;n

Reputation: 31

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;

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16411

ODBC driver support the MFA authentication, but windows only: enter image description here

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. enter image description here

Hope this helps.

Upvotes: 23

Related Questions