Search code examples
azure-synapseazure-synapse-analyticsazure-notebooks

Access our built-in serverless SQL pool from our notebook

We are currently trying to access our built-in serverless SQL pool from our notebook. Our goal is to be able to drop views from the notebook, which is something we need to put into our pipeline as per our business requirements. We have attempted several methods but have not yet succeeded.

We initially tried using this Stack Overflow post for guidance: How can you connect to serverless sql pool using notebooks?

but encountered an error on the line regarding the token. It's important to note that we can only get an Azure Synapse Analytics (Artifact) linked service to work instead of an Azure SQL Database linked service. When we create a linked service for the Azure SQL Database, we receive the following message: "Check the linked service configuration is correct and make sure the SQL Database firewall allows the integration runtime to access." To address this, we attempted to change the SQL Database firewall settings. Although we found some methods online, they all seem to apply to dedicated pools rather than serverless. We also consulted with a Microsoft expert who believed it was possible to achieve our goal, but he had not done it before.

We have also tried the code below to connect the SQL Pool from our notebook.

%%spark

spark.conf.set("spark.sql.catalog.spark_catalog.connectionString",
               "Server=tcp:<Servername>.database.windows.net,1433;Database=<SQL database name>;Authentication=ActiveDirectoryMSI")

However when we try to query the view we get the error [TABLE_OR_VIEW_NOT_FOUND].

Honestly, we are not too sure on what the issue is or how to resolve, so any advice is greatly appreciated

Solution

  • Check the linked service configuration is correct and make sure the SQL Database firewall allows the integration runtime to access."

    This issue could be because of you are selecting dedicated SQL pool endpoint in Azure SQL linked service database instead of serverless SQL pool endpoint. That may be the reason to get above error. In Azure SQL database select Legacy version and Enter manually as account selection method, enter the serverless SQL endpoint as shown below:

    enter image description here

    Along with that add storage blob contributor role to your synapse workspace in gen2 account which is attached to serverless SQL pool.

    Publish the linked service, and use the following code to connect to the serverless SQL pool in a Synapse notebook:

    ```python
server = '<serverName>-ondemand.sql.azuresynapse.net'
Port = 1433
Database = "db"
jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("<linkedServiceName>")
query = "(SELECT * FROM <ViewName>) as tb"
conn_Prop = {
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"accessToken" : token
}

df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
display(df)
```

    The connection will be successful, and the table will be queried as shown below:

    enter image description here