Connection.open for hangs indefinitely, no exception is thrown

Question

When I try to do the following code, the program hangs indefinitely. I don't know why and there seems to be other unanswered topics on the matter. Although, if the IP\website cannot be reached, then it works as intended.

    private void DoStuff()
    {
        string connectionString = "Data Source=www.google.com;Connection Timeout=5";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open(); //Hangs here indefinitely
            Console.WriteLine("Test");
        }
    }

For example, if I set the connection string to

connectionString = "Data Source=www.nonexistentsite.com;Connection Timeout=5";

then it will throw an exception. How do I get it to throw an exception for an active site? ... Also google is just for testing purposes, obviously.

EDIT :

If I try to connect to an unreachable server name or IP address I WILL get this exception...

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

UPDATE :

After letting the program run for quite a while, it usually times out finally after 3-5 minutes and gives me the error I posted above. How can I get it to timeout quicker?

Indevout

Are you providing accurate credentials? Usually you would pass in a username and password in your connection string, unless you are absolutely sure that the database accepts anonymous connections. – Quip

The default timeout is 15 seconds, so it should hang for 15 seconds, not indefinitely. – Nikethamide

@SpikeX - My problem right now is that I am getting no exceptions whatsoever, I would like some right now so I know what to do. – Indevout

@krtrego - I set the timeout to 5 seconds. You can see it in my code. It does not throw an exception even quite well after that 5 seconds has elapsed. – Indevout

Did you define userid,password / Integrated Security and database name in your connection string? – Millisecond

@Coder - No, specifically because I want it to give me errors of some kind, not stall and hang the program. However, if I try to connect to a database here, it will throw an error of incorrect username\password. If I try to connect to a server that I cannot reach, then I will get an exception. – Indevout

@Indevout You want to connect to database server or web server? – Millisecond

@Coder - I let the user specify which database, username, and password in my original program. So a database. I just don't want them waiting 3-5 minutes to check if it can connect... Fyi I updated my original post. – Indevout

OK. I have the same problem. The remote SQL server is down (from 10PM to 6AM). It does not give no timeout error. How did you fix the problem? – Deitz

Answer 1

If you have set an FQDN (Fully Qualified Domain Name) for your Data Source such as example.com and the DNS server is unable to resolve this FQDN for a long time it is pretty obvious that your request will hang out. Make sure that the machine from which you are running your application can reach the SQL server and resolve it without any issues. Also you probably want to make sure that there is no firewall that might be blocking the request.

Another possible cause for those symptoms is if you have exhausted the connection pool of ADO.NET. This could happen if you have many slow SQL queries running in parallel, each of them taking a physical connection to the database. There is a limit in the number of available connections on this pool and when this limit is reached the next call to connection.Open() might wait for an available connection to be returned to the pool.

Remark: you might also need to specify in your connection string how you want to authenticate against the SQL server. Checkout connectionstrings.com for more examples.

All this is to say that there is absolutely nothing wrong in the C# code you have posted in your question. It looks more like a network related problem that you could bring to the attention of your network administrators.

Orange

Answer 2

To get the connection to exit after a specified amount of time without success, you can use the Connection Timeout parameter in the connection string. The number you specify is in seconds, so for example, Connection Timeout=240 is equal to 240 seconds\60 seconds = 4 minutes.

Sample connection string:

<add name="MyConnectionString"
connectionString="
Data Source=MyServer\MSSQL2017;
Initial Catalog=MyDatabase;
Integrated Security=True;
Connection Timeout=10;"/>

In the above connection string, the Open() command will timeout after 10 seconds.

Pertain