topic Re: Using Managed Identity Authentication in Unity Catalog using pyodbc in Data Engineering

Using Managed Identity Authentication in Unity Catalog using pyodbc

rushi29 — Fri, 09 Aug 2024 14:00:19 GMT

Hello,

I am having trouble using Managed Identity Authentication in Unity Catalog using pyodbc in Azure Databricks. The same code works on a "Legacy Shared Compute". The code snippet is below:

import pyodbc

jdbc_url = (

"DRIVER={ODBC 17 DRIVER PATH};"

"SERVER=AzureSQLServer.database.windows.net,1433;"

"DATABASE=AzureSQLDBName;"

"Authentication=ActiveDirectoryMsi;"

"TrustServerCertificate=no;"

"Encrypt=yes")

conn = pyodbc.connect(jdbc_url)

If I run this on a "Shared Compute" enabled for Unity Catalog, I get a connection timeout error:

('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

If I switch to using UID/PWD, then it works on the Unity Catalog as well. So, not sure why Managed Identity would not work on a Unity Catalog enabled cluster but work on the Legacy cluster.

Any thoughts would be appreciated.

Thanks,

Rushi

Re: Using Managed Identity Authentication in Unity Catalog using pyodbc

rushi29 — Fri, 09 Aug 2024 21:50:05 GMT

I wanted to provide an update on this issue and see if the added information can help resolve the problem. So, after doing some research, it turns out that the Unity Catalog cluster is unable to reach the Azure Instance Metadata Service (IMDS) possibly due to some network restrictions for unity catalog enabled clusters. This prevents the cluster from getting an access token that is needed to use Managed Identity. I used curl to hit the IMDS endpoint and it errors out in case of Unity Catalog.

%sh

curl -H Metadata:true http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://database.windows.net/

I have raised this with our Databricks administrators but in order to help them out, does anyone know where these networking settings might be for Unity Catalog where this IP/port can be whitelisted?

Thanks,

Rushi

Re: Using Managed Identity Authentication in Unity Catalog using pyodbc

mbenavent — Thu, 21 Nov 2024 18:05:23 GMT

Thank you very much!

I have spent an enormous amount of hours fighting with this and in the end it was the type of cluster... I hope that this problem will be solved in the future, because affects the developments when you use databricks-connect and share the cluster with your team 😞