CODEWITHSUNDEEP
sql-server
dwonisch
dwonisch

Reputation: 5795

Sporadic SQL Connection Timeouts

We have some applications deployed on a machine that are connecting to a remotely installed MSSQL 2012 instance. Except of one application, they are running as Windows service under local administrator account. The client is running on Windows 7 32-bit and the database on Windows Server 2008 R2 64-bit.

I get this error on one out of four connection attempts:

SqlException (0x80131904): 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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   bei System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
   bei System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
   bei System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   bei System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   bei System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   bei System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   bei System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   bei System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   bei System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   bei System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   bei System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   bei System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   bei System.Data.SqlClient.SqlConnection.Open()

I use this connection string from within all applications:

Data Source=192.168.0.101\NAMEDINSTANCE;Initial Catalog=database;Integrated Security=False;Persist Security Info=False;User ID=user;Password=password

I checked the network connection with infinite ping requests but it was OK when the error occured. First I got a similar error message, while NamedPipes were enabled, but after deactivating NamedPipes (we are not using it), the above error occurs in the same frequency. The same setup is working on other machines. The error started occuring a few weeks ago, before it was running for about 3 months successfully. This is an offline setup, the system wasn't updated since deployment (but I can't guarantee that). Firewalls are off on both machines. There isn't a hint on Windows nor SQL-Server event log. I also replaced network cables to exclude a cable break.

I'm a little bit stuck now, because I don't know where to go next. Maybe there are some tweaks of the connectionstring available, or I could change the some configuration server or client side.

UPDATE: With granadaCoders help I found out that a tcp call might be filtered out by something. I used PortQry to check that.

portqry -n 192.168.0.101 -e 1433 -p tcp
TCP port 1433 (ms-sql-s service): FILTERED

If this would happen all the time I would say that a firewall is blocking my call, but in this case it could be some sort of system overload that causes loosing my request. The next things to go for are definitely check if it is an SQL Server Browser fault, by connecting on a specific port. And then replacing the switch.

UPDATE (2013-07-03): I wrote a test application that opens a connection every second and run it on the faulted system. It worked. I got the error on the other applications twice within a few connections, but didn't managed to get it once within 500 connections within my test app.

UPDATE (2013-07-05: I forgot that there is another network connection to this server. That was connected a few weeks ago. It looks like this was the root cause for this problem.

Upvotes: 2

Views: 2302

Answers (4)

dwonisch
dwonisch

Reputation: 5795

The server and the client are both connected via two separated networks. When I send a connection request over network A it seems that SQL Server might answer it over network B.

The network priorities were set correctly in Windows (A over B).

What solved my problem was changing the two network cables on the server. Now there are no timeouts anymore and everything works as expected. The server has two connectors on the main board, but seems to ignore Windows' priorization settings.

The network B was disabled in SQL Server TCP configuration, so it shouldn't use it anyway. It is a strange behavior for me as I can't find a reason why this might happen.

Upvotes: 0

granadaCoder
granadaCoder

Reputation: 27884

  1. Read this.
  2. You can define the network library to use in the connection string. dbmssocn is my go-to.
  3. Maybe add the port to the connection string.

When you use the IP address, specify the port number and give it the network library, that's as "very very specific" as you can get I think.

Check out the Connect via IP address example.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

Check that your developers always use either a try/finally or a using block for their connections. Very often you'll see code like this:

SqlConnection con = ... ;
con.Open();

// ... use the connection ...

con.Close();

That code is wrong. If an exception happens in the middle of that code, the con.Close(); line is never executed. This results in connections being left open, and if that happens often enough you max out the number of allowed active connections on your server, lock yourself out of the server, and get exception traces similar to the one you posted.

Instead, you need to always wrap your connections with either a using block or try/finally:

using (SqlConnection con = ... )
{
    con.Open();

    // ... use the connection ...
}

Notice that I didn't even need to call con.Close() here, as the using block handled it for me. The other option:

SqlConnection con = ... ;
try 
{    
    con.Open();

    // ... use the connection ...
}
finally
{
    con.Close();
}

Upvotes: 0

TToni
TToni

Reputation: 9391

Hard to say where this comes from, but is using the port in the connection string instead of the named instance an option?

If you use a named instance, the first request goes to the SQL Server Browser on the target system (operating on port 1434) to get the port associated with the named instance. Only then the real connection is made to the specified port. If you use the port directly, this roundtrip is eliminated and if you use the port everywhere you also can reduce the attack surface by shutting down the SQL Server Browser.

Upvotes: 0

Related Questions