Below are some frequent errors/scenarios you may encounter when trying to connect directly to your database using a SQL server client. For initial setup and troubleshooting, see Direct SQL Access.
Direct SQL access is a complex resource provided on an as-is basis and is generally outside the scope of the Support Desk. Troubleshooting a complex resource of this sort requires patience and precise attention to detail. If you’re unable to resolve your issues on your own, we recommend reviewing the many support resources available to you and your team.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This error means that the server did not return a response during the allotted timeout window. For most SQL clients, this window is set to 15 seconds by default. This is most frequently the result of a misconfigured user account. Rather than alert the user trying to connect that their credentials are invalid, SQL server will simply not respond. This results in a timeout error.
To resolve this error, try the following steps:
Confirm your username and password pair are correct.
Ensure you are connecting from an allowed network. For security, connections must come from a proactively allowed network. One common mistake is to add the IP address of your machine but then attempt to connect from a SQL client hosted at another IP. Contact your institutional IT Department / Vendor to confirm the IP address from which you will be connecting.
Confirm the correct hostname, port pair, or syntax. A hostname and port pair is required to route your login attempt to the correct server. Review the Hostname and Port Table in the Knowledge Base Article on Direct SQL Access to ensure you are using the correct port for your database. Make sure you are using the correct Microsoft SQL Server hostname,port syntax (with a comma between the hostname and port) in your connection string rather than the common hostname:port syntax (with a colon between hostname and port).
System.Data.SqlClient.SqlException (0x80131904)
Full error text:
System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was...
This is a more verbose version of the “timeout expired” error above and indicates that no server response was received from the database you are attempting to connect to. Follow the same troubleshooting steps as above for the ‘Timeout expired’ error.
Connection works in production but fails in Test Environment
It is possible to connect to your Test Environment directly from your SQL client. For connection attempts to be successful, the account configured for direct SQL access in Test much exactly match an account configured for direct SQL access in Production. This match includes allowed networks: if a network is marked as ‘allowed’ in Test, but not allowed for the same account in Production, the connection will fail.
To troublehsoot:
Ensure that the direct SQL user account’s information is identical between Production and Test. This includes the password and allowed networks.
If no differences are apparent between Production and Test, refresh your Test environment to ensure all user account data matches what is in your Production database.