Databricks integration to Azure Synapse Serveless SQL Pool

Vishnu Tupe 20

Hi,

What is the way to connect to Databricks to Azure Synapse serverless SQL pool

when I am trying to connect using serverless SQL endpoint getting an error as -

Py4JJavaError: An error occurred while calling o388.load. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the CREDENTIAL 'https://xxxxxxx.dfs.core.windows.net/xxxxxxxxx/deltalake/custgroup_partitioned', because it does not exist or you do not have permission.

Note: xxxx represents actual values.

Below is the Python code used.

Thanks.

# Create a Spark session
from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("Synapse SQL Connection") \
    .getOrCreate()

# Set the Synapse SQL endpoint and credentials
url = "jdbc:sqlserver://xxxxxxxxxxxxxxx-ondemand.sql.azuresynapse.net:1433;database=xxxxxxxxxxxxxxxxxxxxxxxx"
user = "xxxxxxxxxx"
password = "xxxxxxxx"

df = spark.read.format("jdbc") \
    .option("url", url) \
    .option("user", user) \
    .option("password", password) \
    .option("database", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx") \
    .option("dbtable", "dbo.custgroup") \
    .load()

BhargavaGunnam-MSFT 28,111 Microsoft Employee

Hello Vishnu Tupe,

Welcome to the Microsoft Q&A forum.

Can you please confirm if the credentails actually been created?

If not, please follow the below document:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-overview?tabs=impersonation#permissions

-- Optional: Create MASTER KEY if not exists in database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Very Strong Password>'
GO
CREATE DATABASE SCOPED CREDENTIAL [SasToken]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1KEIq78%3D';
GO
CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>',
          CREDENTIAL = SasToken
)

I hope this helps.

Vishnu Tupe 20 Reputation points

2024-05-22T09:29:24.85+00:00

Hi,

It is Serverless database and not able to create External Data Source. getting below error.

Operation CREATE EXTERNAL DATA SOURCE is not allowed for a replicated database.

Please advice.

Thanks.

Regards,

Vishnu
Vishnu Tupe 20 Reputation points

2024-05-22T09:30:02.1066667+00:00

Hi,

It is Serverless database and not able to create External Data Source. getting below error.

Operation CREATE EXTERNAL DATA SOURCE is not allowed for a replicated database.

Please advice.

Thanks.

Regards,

Vishnu

0 commentsNo commentsReport a concern
BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-22T16:43:39.5866667+00:00

Hello Vishnu Tupe,

It seems like you are using lake database instead of Serverless database while creating the External datasource. Please use the serverless db and you shouldn't get this error.
BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-22T16:45:43.1433333+00:00

Hello

Please see the below reference document.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#operation-isnt-allowed-for-a-replicated-database
Vishnu Tupe 20 Reputation points

2024-05-23T08:42:27.5466667+00:00

Thanks Bhargava,

However our requirement is export a D365 data using Synapse Link into Synapse workspace (serverless/Lake database) and access this data into Databricks.

So we dont have dedicated/SQL database to create the External Data source.

Please let me know if it is possible to connect to Synapse Serverless/Lake database from Databricks and if yes, how can we achieve that.

Many Thanks in advance.
Vishnu Tupe 20 Reputation points

2024-05-23T08:47:00.5866667+00:00

Thanks Bhargava,

However our requirement is to export Dynamics D365 data into Synapse worksapce (Serverless/LAke database) using Azure Synapse Link and access this data into the Databricks.

So we do not have Dedicated/SQL database to create External data source.

Please let me know in above scenario is it possible to connect Databricks to Azure Synapse Data (Serverless / Lake database) and if yes, how can we achieve this.

Many thanks in advance.
BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-23T20:51:59.5+00:00

Lake database is different from Serverless database. Lake databases doesn't support

creating external data source as they are replicated database. You need to create a serverless SQL db.
BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-29T15:31:19.7266667+00:00

Hello Vishnu Tupe,

I am checking to see if you have any further questions here.
Vishnu Tupe 20 Reputation points

2024-05-29T17:28:40.8933333+00:00

Hey Thanks Bhargava,

I think its clear now that to access Lake Database directly from the Databricks is not possible. No other questions now.

Thanks for your help.
BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-29T17:47:54.3+00:00

Hello Vishnu Tupe,

Thank you for the confirmation. I will post this answer as a solution, so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others

I'll repost your solution in case you'd like to accept the answer

Accepted answer

BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee

2024-05-29T17:47:56.5933333+00:00

Hello Vishnu Tupe,

Thank you for the confirmation. I will post this answer as a solution, so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others

I'll repost your solution in case you'd like to accept the answer

Issue:

Unable to connect to Databricks to Azure Synapse serverless SQL pool/Lake databases

Error:

Operation CREATE EXTERNAL DATA SOURCE is not allowed for a replicated database.

Solution:

Acess to Lake Database directly from the Databricks is not possible.

Lake databases doesn't support creating external data source as they are replicated database.

This is only supported for serverless SQL db.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

1 additional answer

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Databricks integration to Azure Synapse Serveless SQL Pool

1 additional answer