How to connect from Azure Databricks to Azure SQL DB without sql authentication

Question

How to connect from Azure Databricks to Azure SQL DB without sql authentication

Paul Hernandez 686 Microsoft Employee

Hi everyone,

we were using SQL Authentication while developing a solutions using Azure Databricks Notebooks, which write into an Azure SQL Database.

We need to move forward to the next environment, where SQL Authentication is removed and only AAD authentication is enable.

What would be the best way to use the JDBC driver to interact with the database.

I am thinking in a Managed Identity with the appropriate permissions.

The notebook will be called from an ADF pipeline. Is it possible to use the ADF managed identity for authentication?

Best regards,
Paul

PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2022-09-19T07:04:18.907+00:00
Hello @Paul Hernandez ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Scott Lusk 5 Reputation points

2024-01-03T18:07:47.7566667+00:00

Hey @Paul Hernandez did you end up figuring this out?

Answer accepted by question author

1 additional answer

Your answer

PRADEEPCHEEKATLA 91,496 Reputation points Moderator

2022-09-19T07:04:18.907+00:00

Hello @Paul Hernandez ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Scott Lusk 5 Reputation points

2024-01-03T18:07:47.7566667+00:00

Hey @Paul Hernandez did you end up figuring this out?

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

Hey
I am not sure whether we can leverage Managed Identity to access Azure SQL database from Databricks notebook because one would need environment variables that do not exist in the Databricks instance, like IDENTITY_ENDPOINT and IDENTITY_HEADER in reference to below doc:

You can leverage service principal auth :
https://www.thedataswamp.com/blog/databricks-connect-to-azure-sql-with-service-principal

Paul Hernandez 686 Reputation points Microsoft Employee

2022-09-14T13:13:32.563+00:00

Hi @Nandan Hegde ,
thanks for your answer.
I just want to confirm that using a managed identity is not possible.

I reviewed the documentation of the JDBC driver and found this sentence:

Another option for connecting to SQL Server and Azure SQL Database is the Apache Spark connector. It can provide faster bulk inserts and lets you connect using your Azure Active Directory identity.

With this I can infer, the built-in JDBC driver only supports or is recommended for SQL authentication, and the Apache Spark connector is the way to go for AAD based authentication.

Then I reviewed the documentation of the Apache Spark connector:

Only Service Principal and Active Directory password are covered. In this case I can infer, no managed identity is supported, or at least without complicated workarounds.

Do you think is still worth to have a look at the msal or the Identity library for python and try to force the authentication using managed identities or this is just not recommended.

BR.
Paul Hernandez
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-09-14T18:38:32.223+00:00

Hey,

Based on my experience I wasn't able to do so and hence we switched to service principal auth as service account usage in our org is least secure method

Answer 2

Alberto Morillo 35,401 MVP Volunteer Moderator

You can definitely use a managed identity in Azure Data Factory to connect to an Azure SQL Database. See this documentation.

When you create the Linked Service to Azure SQL you can specify you can connect to Azure SQL using managed identity.

Paul Hernandez 686 Reputation points Microsoft Employee

2022-09-13T07:10:41.973+00:00

Hi Alberto,
thanks for your answer. I won't mark it as the answer because my main question is how to authenticate Databricks with Azure SQL DB. I just mentioned the ADF managed identity to evaluate if this can be used by Databricks as the user for authentication with the database, kind of impersonation. But what you described is 100% correct.

Share via

How to connect from Azure Databricks to Azure SQL DB without sql authentication

1 additional answer

Your answer