If trying to connect Azure SQL Server database and getting below Error:
Error
Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. .
Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in prelogin response.
Initial Investigation
The error message you’re encountering when testing an ODBC connection to Azure SQL Server indicates that there’s a problem with the connection or a timeout issue. Here are some steps you can take to diagnose and resolve this issue:
- Check Network Connectivity: Ensure that your computer or server has network connectivity to the Azure SQL Server. Verify that there are no network issues, firewalls, or security groups blocking the connection.
- Verify Azure SQL Server Configuration: Check the Azure SQL Server’s configuration to make sure it’s configured to accept connections from your IP address or range. You can configure this in the Azure portal under the server’s firewall settings.
- SQL Server Configuration: Ensure that the SQL Server on Azure is up and running without any issues. Check the server’s status and make sure it’s accessible.
- Correct Server Name and Port: Verify that you are using the correct server name and port number in your ODBC connection string. You can find this information in the Azure portal.
- Firewall Rules: Confirm that the IP address from which you are attempting to connect is added to the Azure SQL Server’s firewall rules. The firewall must allow incoming connections from your IP address.
- SQL Server Authentication: Ensure you are using the correct login credentials (username and password) for SQL Server authentication. If you’re using Azure AD authentication, ensure that the credentials are correct.
- Network Latency: Check for any network latency or delays that might be causing the timeout. This can sometimes be due to network issues, so testing the connection from a different network might help diagnose the problem.
- ODBC Driver Version: Make sure you are using a compatible ODBC driver version. Sometimes, updating or reinstalling the ODBC driver can resolve connection issues.
- Server Load: Check if the Azure SQL Server is under heavy load, which can cause slow responses. Monitor the server’s resource utilization.
- TLS/SSL Configuration: Ensure that your ODBC driver is configured to use the correct TLS/SSL settings, especially if your Azure SQL Server requires an encrypted connection.
- Client Timeout Settings: You can adjust the connection timeout settings in your ODBC connection string, but it’s typically better to address the underlying issues causing the timeout rather than simply extending the timeout.
Possible Root Cause 1
Possible Root Cause: Unstable Network connectivity to database server or problems with a server in the route
Steps Taken To Confirm Root Cause: – Add the source ip address in firewall rules
Possible Root Cause 2
Possible Root Cause: Issues connecting to Active Directory Server
Steps Taken To Confirm Root Cause: Tried connecting via a SQL id using SSMS & SQLCMD encountered the same issue.
Possible Root Cause 3
Possible Root Cause: Issues connectiving via IPv4 or IPv6 address
Steps Taken To Confirm Root Cause:
Checked TCP/IP properties in configuration manager on database server; server was listening to all IP’s.
Tried connecting manually using the IPv4 addresses as well as IPv6 addresses; Encountered the same error in both, so issue was not related to this cause.
C:\Users\sameer\Desktop>sqlcmd -S DBServerName.domainname.com -E
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: Timeout error [258].
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Unable to complete login process due to delay in prelogin response.
C:\Users\sameer\Desktop>sqlcmd -S fe40::d0f8:6600:8b2:625b%14 -E
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Possible Root Cause 4
Possible Root Cause: TCP/IP Port Exhaustion
Steps Taken To Confirm Root Cause:n netstat -ao on the database server while connecting to it from a client server. Consistently observed a TCP connection was being established between both servers, even when the SQL error message was occuring. This means the TCP connection is fine, and there is no TCP/IP port exhaustion.