Execute Serverless SQL Pool external table / Views from Synapse notebook using Python

Question

I have a Serverless SQL pool which contains external tables/Views write against ADLS Gen2 parquet files.

I want to call external table / views from synapse notebook using python.

FYI. Server less SQL Pool Identity is Azure Active directory.

Can you please let me know how can achieve this.

Using Scala it is working the below code. Need same in the python using active directory authentication.

// Define connection:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")

val hostname = "demo-da-synapse-test-ondemand.sql.azuresynapse.net"
val port = 1433
val database = "Demo" // If needed, change the database 
val jdbcUrl = s"jdbc:sqlserver://${hostname}:${port};database=${database}"

// Define connection properties:
import java.util.Properties

val props = new Properties()
props.put("user", "testuser")
props.put("password", "test202XXX!")

// Assign driver to connection:
val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
props.setProperty("Driver", driverClass)

val tsqlQuery ="select top 100 * from  [demo].[tblEmp]"

val df = spark.read.jdbc(jdbcUrl, s"(${tsqlQuery}) res", props)

df.show(10)

Thank you, Sunny

Answer 1

To connect Serverless SQL pool with Synapse python notebook with Azure AD authentication. you need to install ADAL4j library on your spark pool.

You can download ADAL4j library from here

After downloading it extract and upload it in workspace

Goto Manage >> Workspace packages >> Upload >>select jar files you downloaded.

Then add this package in your spark pool as below.

Go to Manage >> Apache Spark pool >> 3 dots beside saprk pool name >> packages.

Click on select from workspace packages:

select all packages and click apply the changes.

Then try below code in your synapse notebook:

jdbcHostname = "servername-ondemand.sql.azuresynapse.net"
jdbcDatabase = "dbname"
jdbcPort = "1433"
username = "Adusername"
password = "AD password"
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;authentication=ActiveDirectoryPassword".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
"user" : username,
"password" : password,
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
pushdown_query = "select * from ext1"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)

If you receive any authentication error error-code Make sure you disable Security Defaults by visiting > Azure Active Directory > Security > Security Defaults > Disable

If the error persists after disabling Security Defaults, Check if there's an MFA or any Conditional Access Policy applied to the user by visiting > Azure Active Directory > Security > Conditional Access > Remove Policy or exclude the user from the Policy.