Connecting Azure Synapse from Azure Databricks using User Assigned Managed Identity

Question

Unable to connect to Azure Synapse from Azure Databricks - Notebook (PySpark) using JDBC, Using teh following JDBC url, getting exception:

jdbc:sqlserver://.sql.azuresynapse.net;database=;User Id=Authentication=ActiveDirectoryMSI;Use Encryption for Data=true;

Exception:

Caused by: java.lang.IllegalArgumentException: No JDBC credentials provided. You must specify JDBC credentials in either the URL or as user/password options, use Credential Passthrough, or set Service Principal Oauth2.0 authentication.

Code:

df.write \
      .format("com.databricks.spark.sqldw") \
      .option("url", "jdbc:sqlserver://<synapse server name>.sql.azuresynapse.net;database=<dedicated pool name>;User Id=<UserAssignedManageIdentity>Authentication=ActiveDirectoryMSI;Use Encryption for Data=true; 
") \
      .option("useAzureMSI","true") \
      .option("dbtable", "table_name") \
      .option("truncate","false") \
      .option("tempDir", "abfss://<azure_storage_name>@<container_name>.dfs.core.windows.net/tempDir") \
      .mode("overwrite") \
      .save()

What is the correct connection string to connect to Synapse using User Managed Service Identity.

Background:

User Managed Service Identity (umsi_synapse_user) been created
Assigned Contributor role to User Managed Service Identity to access Synapse
Created External user with User Managed Service Identity (umsi_synapse_user)
Assigned 'db_owner' role to umsi_synapse_user

try 'jdbc:sqlserver://sqlsrv_name.azuresynaspe.windows.net:1433;database=sqldw_name;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.azuresynaspe.windows.net;loginTimeout=30;' You using the Azure Databricks Synapse Connector way of doing it learn.microsoft.com/en-us/azure/databricks/external-data/… mrjonlunn.com/2023/05/26/azure-databricks-synapse-connector — Jon
– Jon, Commented Oct 3, 2023 at 14:21

Alex Ott · Accepted Answer · 2023-10-01 16:43:42Z

0

There is no support for managed identity on Azure Databricks clusters (yet). So you need to use other authentication methods.

answered Oct 1, 2023 at 16:43

Alex Ott

88.1k10 gold badges110 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connecting Azure Synapse from Azure Databricks using User Assigned Managed Identity

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related