Interactive Azure AD Authentication with Python

Happy New Year! Hope everyone have a great and healthy year. First of January is just a new day, new day with new challenges. Recently, I’m working on data engineering, science and machine learning platform with Azure Databricks. One interesting challenge is about the authentication methodology from Databricks Notebook (Python) to Azure SQL Database.

From of official documentation, the most easiest way is using PySpark with SQL User ID and Password for the authentication via JDBC driver. Azure SQL Database is also supported Azure AD (Users, Groups, Service Principles and Managed Identities) based authentication. In Azure Databricks, most straightforward way is using Azure AD Service Principle to perform authentication with Azure SQL Database. You can configure Secret Scope (with Azure Key Valut-backed scope in advanced use case) to store your Service Principle’ secret values. And definitely, you also need to configure Secret Access Control to control/limit to secret scope access. But it make administration and operation overhead, and Service Principle maybe shared with multiple users in Databricks unless you’re going to make 1 scope for 1 user (for security audit purpose on handing sensitive data). For Databricks Jobs, it is still fine, because we know who & what is running as scheduled. But for interactive usage from data analyst or scientist via Databricks Notebook, it would be nightmare.

To make our life easier, I’m thinking for how to make interactive Azure AD authentication from Databricks Notebook (Python). And this story will tell how to make it and this is also applicable to generic notebook or python programs. Let’s kick started. Before going to share the code, let’s talk about the flow.

1. Data analyst or scientist use web browser to interact with Azure Databricks Notebook.
2. Initiate interactive Azure AD authentication (with device code) from Notebook. Open up browser (https://microsoft.com/devicelogin) to perform interactive authentication with Azure AD.
3. After login successful, Notebook user session will get the user access token from Azure AD.
4. Leverage collected user access token to perform token-based authentication with Azure SQL Database via pyodbc library and MS-SQL driver, load SQL query into Panda…