topic Re: Can we use "Access Connector for Azure Databricks" to access Azure SQL Server? in Data Engineering

Can we use "Access Connector for Azure Databricks" to access Azure SQL Server?

GCera — Thu, 28 Dec 2023 16:39:14 GMT

Is it possible to avoid using Service Principal (and managing their secrets) via the Python MSAL library and, instead, use the "Access Connector for Azure Databricks" to access Azure SQL Server (just like we do for connecting to Azure Data Lake Storage)?

The end goal is twofold:

use spark.read.jdbc() to retrieve data from tables
use pyodbc to execute INSERT/UPDATE queries

Re: Can we use "Access Connector for Azure Databricks" to access Azure SQL Server?

Wojciech_BUK — Thu, 28 Dec 2023 21:15:14 GMT

You can't use databricks Access Connector or Managed Identity.

There is option to use managed Identity but I don't believe it is very secure.

In managed resource group you can find Managed Identity ( one per workspace) that is being attached to Clusters ( VM) , you can try to grant access for this identity to your DB and get token in code , then pass it to spark read jdbc.

I have heard that with Unity federation both Oauth + data write will be possible infuture but not who knows when it will happpen 😞

Re: Can we use "Access Connector for Azure Databricks" to access Azure SQL Server?

GCera — Fri, 05 Jan 2024 11:56:50 GMT

Unfortunately, I guess the answer is no (as for today, see @Wojciech_BUK reply).