0

Recently I started to see intermittent errors in log files of my web application. Raising from different places of the application the error is always about failed connection to DB which is located on another server:

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)

----

System.ComponentModel.Win32Exception (0x80004005): Access is denied

OR

The underlying provider failed on Open.

----

System.Data.SqlClient.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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): Access is denied
   ....
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
ClientConnectionId:00000000-0000-0000-0000-000000000000

I'm using Microsoft SQL Server Standard Edition (64-bit) 10.50.4000.0 on Win Server 2008 R2

I checked:

1 Database Autoclose is set to OFF

2 I have checked event logs of both APP and DB server and didn't find anything related to sqlserver or network errors. Event log however contains Schannel errors, but from I googled it is not related.

frm
  • 101
  • 3

1 Answers1

1

It would be great if at the exact moment your website has the error, you could attempt to connect to the DB another way (SSMS perhaps). If you cannot, can you connect to the server the DB resides on? If you can connect at the same time that the website cannot, are you using the same user? It is important to determine if the website cannot connect to the DB because of a DB issue (stopped itself or is too bogged down to respond), a network error connecting to the DB server, or because of a user authentication error to the working DB. What course of action to take depends on which problem you have.

As for how to accomplish that, you might be able to add some web code to catch the error and fire off an email to you. Then you can check the DB at exactly that time. If that's not possible, perhaps you can write a small command line application to connect to the DB and write a datetime stamp to a file along with any errors that occur. Then setup a scheduled task to run the program every 5 minutes or so. You could even put that command line application on the web server and another server to get additional information (you could also try running the application as different users).

For example, if the DB is fine, the network connection is fine, and the user is sometimes not able to connect, then perhaps the user is occasionally getting locked out due to a lockout policy from too many invalid login attempts from someone/something else on the network. Or, maybe the DB is not responding at all at certain times of day due to a scheduled backup that is utilizing all available resources on the DB server. Etc.

TTT
  • 736
  • 4
  • 10
  • thanks for the detailed response. I managed to nail down the source of the problem. The DB had mirroring configured and it was SUSPENDED. Since mirroring was suspended I thought this can not be the cause, however when I removed mirroring completely - the problem gone. Maybe someone can explain why this was happening? – frm Jul 15 '15 at 22:00