Cloud SQL MSSQL not connecting from dataproc & dataproc serverless using jDBC

[amitshrivastava]

The cloud sql MSSQL which i created , i am able to connect it from sql server management studio on my laptop (Which is not on google network) however to my surprise when i connecting it from dataproc or dataproc serverless by running same pyspark job which is running on my local and connecting same SQL sever is not able to connect sqlserver and giving the error .

Based on google search , I have enabled the firewall rules for ingress and egress on port 1433 to allow all 0.0.0.0/0 .

Could anyone please help what is this issue .

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host ***********, port 1433 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."

[ms4446]

Here are some steps to troubleshoot this issue:

1. Double-Check Your Connection Properties:

   • Ensure that the host name, username, and password in your PySpark job are correct.
   • Verify that you are using the correct port number (1433 by default) and specifying the database name in the connection string.
   • Make sure you are using a JDBC driver that is compatible with your specific MSSQL version in Cloud SQL.

2. Verify Connectivity from Dataproc/Dataproc Serverless:

   • Test the connection to the Cloud SQL instance using telnet or a similar tool on port 1433 from your Dataproc/Dataproc Serverless cluster, as pinging might not be reliable due to ICMP blocking.
   • Ensure that Dataproc/Dataproc Serverless clusters have appropriate network routes (public or private) to reach the Cloud SQL instance.
   • Enable Private Google Access for your project if necessary, to allow Dataproc/Dataproc Serverless to access internal Google services like Cloud SQL.

3. Check for Additional Firewall Rules and Network Policies:

   • Beyond the ingress and egress rules for port 1433, check for any other firewall rules or VPC Service Controls that might be blocking the connection.
   • Review any organizational policies that could restrict traffic between Dataproc/Dataproc Serverless and Cloud SQL.

4. Investigate Logs and Monitoring Tools:

   • Examine the Cloud SQL and Dataproc/Dataproc Serverless logs for error messages or clues.
   • Utilize Google Cloud's operations suite for network and application monitoring to analyze traffic and identify potential issues.
   • Check the SQL Server error logs within the Cloud SQL instance for indications of connection attempts and failures.

5. Consider Alternative Connection Methods:

   • If direct connectivity issues persist, consider using Cloud SQL Proxy for secure connections to Cloud SQL.
   • Explore options like Cloud VPN or creating a proxy server for alternative connection methods.