Connect to Serverless SQL Pool using Synapse Notebook

Hi @Haoran Bai
The issue you're facing:

Cannot open database "db_presentation" requested by the login. The login failed.

is happening because the Serverless SQL pool does not support custom user databases like db_presentation. Serverless SQL only supports querying external tables, files (like CSV/Parquet), or views defined in metadata databases (like master or similar).

If db_presentation is part of a Dedicated SQL Pool, please update your JDBC URL to use the dedicated SQL pool endpoint, not the Serverless one. You can find this in your Synapse workspace under:

Synapse → SQL pools → [Your Dedicated Pool] → Connection strings

Example updated JDBC URL:

jdbc_url = "jdbc:sqlserver://<your-dedicated-pool>.sql.azuresynapse.net:1433;" \
           "database=db_presentation;" \
           "user=synapseadm@synapse-haoran-test-ws;" \
           "password=MY_PASSWORD;" \
           "encrypt=true;trustServerCertificate=false;" \
           "hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;"

Then your Spark code can connect successfully.

If you intended to use Serverless SQL:

You’ll need to remove database=db_presentation from the URL.

Ensure you're querying from a database that contains external tables or views supported in Serverless SQL.

• Example:

    .option("query", "SELECT * FROM [YourMetadataDb].[schema].[external_table]")
    
    
  

I hope this helps! Please let us know how it goes.

If you found this useful, consider upvoting the comment so it can help others in the community too.

Hi Krupal, the database db_presentation I'm using is a database I created using below code in serverless sql pool:

USE master;

CREATE DATABASE db_presentation;

CREATE SCHEMA deliveries_db;

And all tables in this database are external tables.

I changed to use below code and remove remove database=db_presentation from the URL:

.option("query", "SELECT * FROM [db_presentation].[deliveries_db].[test]")

It's shows below error:

Py4JJavaError: An error occurred while calling o4251.load. : com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in 'db_presentation.deliveries_db.test' is not supported in this version of SQL Server.

Hi @Haoran Bai
Thanks for the update and for sharing the details of your setup.

You're encountering the error:

Reference to database and/or server name in 'db_presentation.deliveries_db.test' is not supported in this version of SQL Server.

This occurs because Serverless SQL pool does not support cross-database queries, even within the same Synapse workspace. When the database parameter is omitted from the JDBC URL, the connection defaults to the master database. From master, it's not possible to reference db_presentation using three-part naming.

You need to explicitly set db_presentation as the target database in your JDBC URL.

Update your JDBC connection string to:

jdbc_url = "jdbc:sqlserver://<your-synapse-workspace>-ondemand.sql.azuresynapse.net:1433;" \
           "database=db_presentation;" \
           "user=synapseadm@<your-synapse-workspace>;" \
           "password=MY_PASSWORD;" \
           "encrypt=true;" \
           "trustServerCertificate=false;" \
           "hostNameInCertificate=*.sql.azuresynapse.net;" \
           "loginTimeout=30;"

Then, update your Spark query to:

.option("query", "SELECT * FROM [deliveries_db].[test]")

Even though db_presentation was created in Serverless SQL, you must connect directly to it in order to query its tables. Cross-database references like [db_presentation].[schema].[table] aren't supported.

Hi Krupal, I'm already doing this

jdbc_url = "jdbc:sqlserver://synapse-haoran-test-ws-ondemand.sql.azuresynapse.net:1433;" \
           "database=db_presentation;" \
           "user=synapseadm@synapse-haoran-test-ws;" \
           "password=MY_PASSWORD;" \
           "encrypt=true;" \
           "trustServerCertificate=false;" \
           "hostNameInCertificate=*.sql.azuresynapse.net;" \
           "loginTimeout=30;"

df = spark.read \
    .format("jdbc") \
    .option("url", jdbc_url) \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .option("query", "SELECT * FROM [deliveries_db].[test]") \
    .option("fetchsize", "1000") \
    .option("integratedSecurity", "false") \
    .option("authenticationScheme", "NTLM") \
    .option("trustServerCertificate", "true") \
    .option("encrypt", "true") \
    .load()

df.show()

And it's shows below error:

Py4JJavaError: An error occurred while calling o4273.load. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "db_presentation" requested by the login. The login failed. ClientConnectionId:1f6be465-64fb-4162-a0f2-7ec4df40f5c2