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

Sep 12, 2022, 3:09 PM

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 90,591 Reputation points

Sep 19, 2022, 7:04 AM
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

Jan 3, 2024, 6:07 PM

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

Accepted answer

1 additional answer

Your answer

PRADEEPCHEEKATLA 90,591 Reputation points

Sep 19, 2022, 7:04 AM

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

Jan 3, 2024, 6:07 PM

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

Answer 1

Nandan Hegde 34,696 MVP

Sep 12, 2022, 5:14 PM

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

Sep 14, 2022, 1:13 PM

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 34,696 Reputation points MVP

Sep 14, 2022, 6:38 PM

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 34,646 MVP

Sep 12, 2022, 3:56 PM

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

Sep 13, 2022, 7:10 AM

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.

The future is yours

Share via

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

1 additional answer

Your answer