Using Managed Identity Authentication in Unity Catalog using pyodbc

[rushi29]

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

[rushi29]

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://databa...

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

[mbenavent]

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 😞