Cant establish connection to on-prem mssql server.

[MiloRo1ey]

Hello guys, 

I am trying to establish a connection to an on-prem mssql server - however I get this error in data fusion:

Failed to explore connection. Error: "Failed to create connection to database via connection string: jdbc:sqlserver://xx.x.x.xx:1433 and arguments: {user=xxxxx}. Error: SQLServerException: The TCP/IP connection to the host xx.x.x.xx, 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.".."

I have checked all the ports and firewall, and they are open. What can be the issue?

[ms4446 · Accepted solution]

The error message indicates that Data Fusion is unable to establish a TCP/IP connection to the on-prem MSSQL server due to a timeout. To address this, consider the following enhanced troubleshooting steps:

1. Hostname and Port Verification: Ensure the hostname and port number in the connection string are correct. Check both the internal and external IP addresses if the SQL Server is behind a NAT or similar setup.

2. SQL Server Configuration: Confirm that the MSSQL Server instance is running and configured to listen for TCP/IP connections on port 1433. This can be verified using SQL Server Configuration Manager. Also, ensure that the SQL Server Browser service is operational, especially for named instances.

3. Test Connection with Another Tool: Try connecting to the MSSQL server using a different tool like SQL Server Management Studio (SSMS) from a machine in the same network environment as Data Fusion. This helps replicate the original connection conditions more accurately.

4. Network Connectivity: Investigate any potential network issues. Perform basic network tests (e.g., ping or telnet) to the SQL Server's IP address and port from the Data Fusion environment to confirm network connectivity. Check for firewalls, network security policies, or routing issues that might be blocking the connection.

5. Data Fusion VPC Settings: If using a VPC with Data Fusion, ensure that the network routes and firewall rules within the VPC allow both outbound traffic to and inbound responses from the on-prem server's IP address on port 1433.

6. SQL Server Logs: Review the SQL Server error logs for any signs of incoming connection attempts or related errors. These logs can provide valuable insights into the nature of the connection issue.

7. Authentication Method: If using SQL Server Authentication, double-check the login credentials. Also, verify that the SQL Server is configured to permit this type of authentication.