Using managed identities to access SQL server - how?

[Espenol1]

Hello! My company wants us to only use managed identities for authentication. We have set up Databricks using Terraform, got Unity Catalog and everything, but we're a very small team and I'm struggling to control permissions outside of Unity Catalog. Basically we want to be able to write to an external Azure SQL Database from Databricks notebook using managed identity, we are not allowed to use username+password for SQL server. Only some of us super users should have the permission to write to SQL server, and some of our service principals.

How do we go about doing this? I tried using DefaultAzureCredential in the azure-identity Python library, but it gives me an error message. Maybe I need to set something up on the cluster? Maybe I should just put a bunch of secrets in key vault and do access control on those secrets through secret scopes?

I'm also not even sure if we want to be writing to SQL server. The thing we are doing is ingesting CDF data from a SQL server using ADF, then we want to make the data available in a different SQL database after being processed through our data platform. The final destination is the SQL database which will be queried by a public API. It seems the easiest to write directly from Databricks -> SQL database instead of for example using ADF to ingest CDF data from our delta lake. But with this identities issue, that is increasingly seemingly like a better option.

[daniel_sahal]

@Espenol1 
I see two ways of doing that:
- There's a "dbmanagedidentity" in databricks managed resource group, which should be automatically tied to DefaultAzureCredential(). Needs to be tested if it actually works,
- You can create a service principal, store object_id and secret in a secrets scope, then connect to the database through jdbc.

[Espenol1]

Thanks for a really quick response! Ths dbmanagedidentity, I have seen it in our resource group but not interacted with it in a long time. That would surely be for account level or workspace level permissions? I need permissions management per user/service principal, and it seems too be too "wide" permissions if I grant permissions to that identity.

Regarding creating a service principal, thanks, that is what I'm looking for. Do you know if that is possible for managed identities as well? Can we create user-assigned managed identity and store its secrets in key vault? I don't think our IaC guy wants to have many service principals that are not managed identities, but I will for sure ask him of the limitations.

[daniel_sahal · Accepted solution]

@Espenol1 
dbmanagedidentity is a workspace level and I totally agree that it's not a perfect way of dealing with the authentication.

With managed identities - they are designed to communicate with services without using secrets at all, so it's impossible. The only way at the moment is to use service principal.

[Espenol1]

Thanks a lot. Then I guess we will try to use dbmanagedidentity for most of our needs, and create service principals +secret scopes when there are more specific needs, such as for limiting access to sensitive data. A bit of a hassle to scale, probably, but hopefully the Databricks will improve support for managed identities before too much time passes.