How to Connect to Azure SQL Database with MFA Using Python on MacOS/Linux

Photo by Artturi Jalli on Unsplash

TL;DR

In this blog post I demonstrate step-by-step how to connect to an Azure SQL Database with MFA using Python pyodbc on MacOS or Linux, by using an access token.

Introduction

Hello, dear friends, in May 2021 I provided an answer to a question on Stack Overflow that still keeps getting up-votes, so I decided to blog about it so perhaps I can help more people to solve the same problem. The image below shows some of the feedback I received for this answer.

In this blog post, we’ll explore an alternative approach to authenticate and connect to Azure SQL Database with MFA using pyodbc and an access token on MacOS or Linux.

Requirements

Before we dive into the process, ensure you have the two following requirements in place:

1. Azure CLI: Install the Azure CLI tool, which enables you to authenticate and interact with Azure services from the command line.
2. Microsoft ODBC Driver for SQL Server (Linux-MAC): Install the appropriate ODBC driver for your MacOS or Linux system to enable database connectivity.

Cutting to the Chase

Below, I have consolidated all the steps mentioned in the next section “Step-by-Step” into a single script. You can simply copy and try it in your environment. If you want to see a more detailed explanation of the code or to how to troubleshoot it, please keep reading. 😉

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

Step-by-Step Guide

Authenticate with Azure CLI

• Open the command line interface and run the command az login to authenticate with Azure CLI.

Set Up the Python Environment

• Install the azure-identity library by running pip install azure-identity in your Python environment.
• Import the necessary modules in your Python script:

from azure.identity import AzureCliCredential
import struct
import pyodbc

Configure Connection Parameters

• Specify the connection details for your Azure SQL Database:

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

Obtain the Access Token

• Create an instance of the AzureCliCredential class to acquire the access token:

credential = AzureCliCredential()
databaseToken = credential.get_token('https://database.windows.net/')

Convert the Token to the Required Format

• Convert the token obtained into a structure that can be used in the connection string:

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 the Connection String

• Construct the connection string, including the acquired access 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})

Perform a Sample Query

• To ensure the connection is successful, execute a 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