Inconsistent Connections to Azure SQL Managed Instance with Fabric Notebook

[avin_45]

I'm trying to connect to an Azure SQL Managed Instance with Microsoft Fabric Notebook using PySpark code. When I attempt to connect, I encounter the following error:

Py4JJavaError: An error occurred while calling o4722.load.

: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host db-sql-mi.public.e789gjshb.database.windows.net, port 3342 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

However, if I try connecting 15 times, it occasionally succeeds and returns the output data frame for the specified table. On the other hand, my teammate, who is located in the US (where the SQL instance is also located), can connect and get the output within 2 seconds every time. He is the architect of this project.

I am not very familiar with networking, so I am wondering if location-specific access or network speed is affecting my connection (I am based in India). My architect has whitelisted my public IP address (which I found via a Google search) and I worked the entire day on that specific Wi-Fi connection, but I still experienced issues. It did connect once, but then failed to connect again for the next two hours, though it worked one out of 15 times after that.

If there were an access issue, would it be possible to connect even those 2 times out of 15?

from pyspark.sql import SparkSession
# spark = SparkSession.builder.appName("Python Spark SQL Server Example").getOrCreate()
username = 'username'
password = 'password'
server_type = 'sqlserver'
jdbc = 'db-sql-mi.public.e789gjshb.database.windows.net:3342'
dbname = 'db-01'
driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
table_name = 'tbl_name'
schema = 'schema_name'

jdbc_df = spark.read \
    .format("jdbc") \
    .option("url", f"jdbc:{server_type}://{jdbc};databaseName={dbname}") \
    .option("dbtable", f"DBO.{table_name}") \
    .option("user", username) \
    .option("password",password) \
    .option("driver", f"{driver}") \
    .option("hostNameInCertificate","*e789gjshb.database.windows.net") \
    .option("encrypt",True) \
    .option("trustServerCertificate",False) \
    .option("loginTimeout",180) \
    .load()

display(jdbc_df.limit(10))

[Anonymous]

Hi @avin_45 ,

Based on the information you have provided, there are a number of potential factors and solutions to consider:
1. Network latency due to geographic distance may be a factor.
2. Ensure that no firewall rules or network security groups (NSGs) are blocking the connection. Even if your IP is whitelisted, there may be other network security settings affecting the connection.
3. Try increasing the connection timeout value in your PySpark code. This sometimes helps with intermittent connection problems.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.