How can you connect to serverless sql pool using notebooks?

Question

I have been trying to establish a connection to a serverless SQL Pool to query a view through notebooks in Synapse, but so far, I have been unable to establish the connection. Below are the codes I have used in my attempt:

Option 1: I have tried to make the connection using the synapse library (I installed the azure-mgmt-synapse==2.0.0 package)

#SQL Connection
from azure.synapse import *
from azure.synapse.artifacts import *

serverless_sql_endpoint_name = "xxxxx-ondemand.sql.azuresynapse.net"
database_name = "dbxxxx"
username = "userxxx"
password = "Pwxxxx"

serverless_sql_endpoint = ServerlessSqlEndpoint(serverless_sql_endpoint_name)
connection_string = serverless_sql_endpoint.get_connection_string(database_name, username, password)

print(f"Connection String: {connection_string}")

but I've got this message: NameError: name 'ServerlessSqlEndpoint' is not defined

===================================================================

Option 2:

In this option I tried to use pyodbc

import pyodbc

server = 'xxxxx-ondemand.sql.azuresynapse.net'
database = 'dbxxxx'
authentication = "ActiveDirectoryInteractive"

connection_string = f"DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={server};DATABASE={database};Authentication={authentication};"

connection = None  

try:
    # try connection
    connection = pyodbc.connect(connection_string)
    print("Connection done")

    # query
    cursor = connection.cursor()
    cursor.execute('SELECT TOP 10 * FROM testview')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

except Exception as e:
    print(f"connection with errors: {str(e)}")

finally:
    # close connection
    if connection:
        connection.close()
        print("Connection closed")

but I've got this message:

Error de conexión: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I would like to be able to find a way to make a connection or if there is another way to query views or stored procedures

Answer 1

You can follow the procedure below to connect to a serverless SQL pool using the following steps:

1. Create an Azure SQL Database linked service with the serverless SQL pool endpoint, using system-assigned authentication as shown:

   

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

   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 <tableName>) 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)
   

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