[Python] How to connect to Fabric SQL database from Notebook?

I'm trying to connect from a Fabric notebook using PySpark to a Fabric SQL Database via JDBC. I have the connection code skeleton but I'm unsure where to find the correct JDBC hostname and database name values to build the connection string.

From the Azure Portal, I found these possible connection details:

Hostname:

hit42n7mdsxgfsduxifea5jkpru-cxxbuh5gkjsllp42x2mebvpgzm.database.fabric.microsoft.com:1433


Database:

db_gold-333da4e5-5b90-459a-b455-e09dg8ac754c


When trying to connect using Active Directory authentication with my Azure AD user, I get:

Failed to authenticate the user name.surname@company.com in Active Directory (Authentication=ActiveDirectoryInteractive).


If I skip authentication, I get:

An error occurred while calling o6607.jdbc. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "company.com" requested by the login. The login failed.


My JDBC connection strings tried:

jdbc:sqlserver://hit42n7mdsxgfsduxifea5jkpru-cxxbuh5gkjsllp42x2mebvpgzm.database.fabric.microsoft.com:1433;database=db_gold-333da4e5-5b90-459a-b455-e09dg8ac754c;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=60;

jdbc:sqlserver://hit42n7mdsxgfsduxifea5jkpru-cxxbuh5gkjsllp42x2mebvpgzm.database.fabric.microsoft.com:1433;database=db_gold-333da4e5-5b90-459a-b455-e09dg8ac754c;encrypt=true;trustServerCertificate=false;authentication=ActiveDirectoryInteractive


I also provided username and password parameters in the connection properties. I understand these should be my Azure AD credentials, and the user must have appropriate permissions on the database.

My full code:

jdbc_url = ("jdbc:sqlserver://hit42n7mdsxgfsduxifea5jkpru-cxxbuh5gkjsllp42x2mebvpgzm.database.fabric.microsoft.com:1433;database=db_gold-333da4e5-5b90-459a-b455-e09dg8ac754c;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=60;")

connection_properties = {
"user": "name.surname@company.com",
"password": "xxxxx",
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

def write_df_to_sql_db(df, trg_tbl_name='dbo.final'):
spark_df = spark.createDataFrame(df_swp)

spark_df.write \
.jdbc(
url=jdbc_url,
table=trg_tbl_name,
mode="overwrite",
properties=connection_properties
)
return True
write_df_to_sql_db(df)


Have you tried to connect to SQL db and got same problems? I'm not sure if my conn string is ok, maybe I've overlooked something.

Continue reading...