question

sakuraime avatar image
0 Votes"
sakuraime asked AimanMdUslim-6823 commented

Azure databricks to Azure synapse Service Principal Authentication

80555-image.png


The screen capture mention it's in the public preview . And I am testing . while I pass .option("enableServicePrincipalAuth","true") \

It still through the error from JDBC

80603-image.png




what's the .option("url", ......) going to be ??



azure-databricks
image.png (104.2 KiB)
image.png (37.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @sakuraime,

How to find the URL?

Go to Dedicated SQL Pool => Under settings => Connection Strings => Select JDBC (SQL authentication)

80608-image.png

Sample code:

 # Otherwise, set up the Blob storage account access key in the notebook session conf.
 spark.conf.set(
   "fs.azure.account.key.<your-storage-account-name>.blob.core.windows.net",
   "<your-storage-account-access-key>")
    
 # Get some data from an Azure Synapse table.
 df = spark.read \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("tempDir", "wasbs://<your-container-name>@<your-storage-account-name>.blob.core.windows.net/<your-directory-name>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .load()
    
 # Load data from an Azure Synapse query.
 df = spark.read \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("tempDir", "wasbs://<your-container-name>@<your-storage-account-name>.blob.core.windows.net/<your-directory-name>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("query", "select x, count(*) as cnt from table group by x") \
   .load()
    
 # Apply some transformations to the data, then use the
 # Data Source API to write the data back to another table in Azure Synapse.
    
 df.write \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .option("tempDir", "wasbs://<your-container-name>@<your-storage-account-name>.blob.core.windows.net/<your-directory-name>") \
   .save()

Tested from my end:

80588-image.png

For more details, refer Azure Databricks - Azure Synapse Analytics and Write Data from Azure Databricks to Azure Dedicated SQL Pool(formerly SQL DW) using ADLS Gen 2.

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I believe you are using SQL authentication instead of Service Principal Authentication

0 Votes 0 ·

Hello @sakuraime,

For Service Principal method, please check the below answer.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered AimanMdUslim-6823 commented

Hello @sakuraime,

Connecting Azure Synapse using OAuth 2.0 with a service principal for authentication is available from Databricks Runtime 8.1 and above.

Below are the steps to connect Azure Synapse using OAuth 2.0 with a service principal for authentication:

Step1: Provide service Principal – permissions to Azure Synapse Analytics and storage account.

Azure Synapse Analytics: Go to workspace => Under settings => SQL Active Directory admin => Click on Set admin => Add registered application => Click on save.

81441-image.png

Azure Storage temp account: Go to Storage account => Access Control (IAM) => Add role assignment => Select Role: Storage Blob Data Contributor Select: register application => Click on save.

81318-image.png

Step2: Define Service Principal credentials for the Azure Synapse Analytics and storage account.

Python Code:

 # Defining the service principal credentials for the Azure storage account
 spark.conf.set("fs.azure.account.auth.type", "OAuth")
 spark.conf.set("fs.azure.account.oauth.provider.type",  "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")
 spark.conf.set("fs.azure.account.oauth2.client.id", "<application-id>")
 spark.conf.set("fs.azure.account.oauth2.client.secret", "<service-credential>")
 spark.conf.set("fs.azure.account.oauth2.client.endpoint", "https://login.microsoftonline.com/<directory-id>/oauth2/token")
    
 # Defining a separate set of service principal credentials for Azure Synapse Analytics (If not defined, the connector will use the Azure storage account credentials)
 spark.conf.set("spark.databricks.sqldw.jdbc.service.principal.client.id", "<application-id>")
 spark.conf.set("spark.databricks.sqldw.jdbc.service.principal.client.secret", "<service-credential>")

81402-image.png

Step3: Set the enableServicePrincipalAuth option in the connection configuration.

Sample URL: "url", "jdbc:sqlserver://<workspacename>.sql.azuresynapse.net:1433;database=<databasename>;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30"

Load data from an Azure Synapse query.

Python Code:

 df = spark.read \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("tempDir", "abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .load()

81422-image.png

Write data to the table in Azure Synapse.

Python Code:

 df.write \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .option("tempDir", "abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>") \
   .save()

81397-image.png

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (113.9 KiB)
image.png (153.1 KiB)
image.png (69.8 KiB)
image.png (62.7 KiB)
image.png (103.9 KiB)
· 13
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @sakuraime,
Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Take care & stay safe!

0 Votes 0 ·
sakuraime avatar image sakuraime PRADEEPCHEEKATLA-MSFT ·

cool let me try later on

0 Votes 0 ·

still can't

81810-image.png


0 Votes 0 ·
image.png (103.4 KiB)
Show more comments

Hi I have a follow up question for these procedures. I am trying to implement permission separation by only allowing Service Principals (SP) to write to only certain schemas in the database. Can this be done in this way? If it can, how would I do it?

0 Votes 0 ·