question

manojd-7499 avatar image
0 Votes"
manojd-7499 asked ErlandSommarskog answered

connecting to sql server failing intermittently with different different errors

We have an app which is hosted on an on-premise infrastructure. The app takes around 20 calls per sec. There are 40 servers on which the app is installed in a load balanced fashion. Each servers hosts other 8 to 10 app on a shared basis. Out of all apps only one app is having SQL Server connectivity issues. The connectivity issues is intermittent and appears with many different different error messages. The app is taking 20 calls per sec and each call in turn might make around 10 SQL Server connections and runs simple select and insert queries.

We checked SSL settings on client and server and there is no issue. No DB table locks are on the DB Server. We checked DB server, network and everything is fine. No memory or CPU issue on any of the server. No single issue is observed for any other app installed on the same application servers.

The code running SQL queries is pretty simple:

 private List<values> GetItemsFromDB(string queryString, SqlParameter[] sqlParams)
 {
     var listOfItems = new List<Item>();
    
     using (SqlConnection connection = new SqlConnection(_provider.ConnectionString))
     {
         SqlCommand command = new SqlCommand(queryString, connection);
    
         try
         {
             connection.Open();
             command.Parameters.AddRange(sqlParams);
    
             SqlDataReader reader = command.ExecuteReader();
    
             while (reader.Read())
             {
                 var item = new Item()
                         {
                             Id = Convert.ToInt64(reader["Id"]),
                             SerializedConfigItem = reader["SerializedItem"] != DBNull.Value
                                 ? reader["SerializedItem"].ToString()
                                 : null,
                             SerializedItem2 = reader["SerializedItem2"] != DBNull.Value
                                 ? reader["SerializedItem2"].ToString()
                                 : null,
                             Created = reader["Created"] != DBNull.Value
                                 ? Convert.ToDateTime(reader["Created"])
                                 : DateTime.MinValue
                         };
                 listOfItems.Add(item);
             }
    
             reader.Close();
         }
         catch (Exception ex)
         {
             _logHandler.Error(ex.Message, "Error while trying to get items from DB");
         }
         finally
         {
             connection.Close();
         }
    
         return listOfItems;
     }
 }
    
 private long WriteItemToDb(string queryString, SqlParameter[] sqlParams)
 {
     long id = 0;
    
     using (SqlConnection connection = new SqlConnection(_provider.ConnectionString))
     {
         SqlCommand command = new SqlCommand(queryString, connection);
    
         try
         {
             connection.Open();
    
             command.Parameters.AddRange(sqlParams);
    
             var obj = command.ExecuteScalar();
             id = Convert.ToInt64(obj);
         }
         catch (Exception ex)
         {
             _logHandler.Error(ex.Message, "Error while trying to insert/update/delete items to DB");
         }
         finally
         {
             connection.Close();
         }
    
         return id;
     }
 } 


Below are some of the different error messages we get randomly and intermittently:

"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 non-recoverable error occurred during a database lookup.)"

"The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE))"

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The handle is invalid.)"

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The handle is invalid.)"

"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: SQL Network Interfaces, error: 9 - Associating port with I/O completion mechanism failed)"

sql-server-generaldotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered manojd-7499 commented

Hi manojd-7499,

Welcome to Microsoft Q&A.
Could you please check if there are any error message in the event log?
What the version of SQL Server are you using? Please try to install the latest CU or SP for SQL Server.

Best Regards,
Amelia

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi AmeliaGu-msft,

Thanks for responding.
We worked with network and db server team but no issue is being observed anywhere. The connection request is not even reaching to the db server. I had to agree with dba team as all other apps are facing no issue at all which are installed on the same servers where problematic app is.
We checked the db connection string and it is same as other apps.
"Server=[fully qualified servername];Database=[db name];Integrated Security=SSPI;encrypt=true;TrustServerCertificate=true;Connection Timeout=180"

We also checked event logs iis logs but no related error anywhere. Another thing is we are not able to repro the same issue in any lower environments. Its appearing only in prod env on all servers randomly, and the error is occurring in thousands.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Those errors are generic "could not connect" errors. They don't really help diagnose the real issue.

However, you mentioned load balancing. Depending on how your network is setup, you will get errors like this when you connect to server1 and then try to use that connection to server2. When load balancing SQL Server, you must use "sticky sessions" to guarantee the connection is always made to the same SQL Server. Otherwise you will get these kinds of errors.

This is almost certainly a networking issue of some kind.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I like to start with saying that my experience is that when you run into things that seem like a complete mystery, things are not really what you think they are. I have been involved in more than one thread where the cause for a mysterious problem has been something the poster did not tell me. Or the poster has insisted on something being X, when it in fact was Y. So there are all reason to double- and triple-check the information you give above.

But let's say that you are correctly relating the situation. You only have one application which is misbehaving. Then it must be something in that application. The connection string is the same, or at least that is the intention. But what if the connection string varies randomly? That could happen if you have code that writes to memory it should not write to. Now, this seems to be .NET, and one would like to think that memory-scribbling cannot happen in managed code. But maybe this application also has components written in unmanaged C++? Then again, one could argue if that if this would be the case, you would see random crashes all over the place. But maybe the memory-scribbling is confined to a location so that it only affects the connection string.

Maybe you should add logging to this application that traps and logs these errors together with the connection string being passed.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.