0

I maintain a system of automated data processing machines which run processes written in C# that are controlled by an SQl Server 2014 Enterprise Edition Database. Several times a day all of the processes on one machine will start receiving the following error:

An error has occurred. Details of the 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Unable to connect to SQL Server database.

All processes on the machine will experienc the same error when attempting to contact the database. After 10 or 15 minutes the machine will be able to connect again. While this is happening other machines on the same network will be able to connect no problem. We have checked the database - SQL Server is running - it is not blocked it is accepting connection, the instance name is correct. This is an intermittant problem that does not affect all machines connecting to the database but when it affects one - all processes experience the error. I have searched on this message and all the answers I have found are for constant failures - not intermittant like I am experiencing.

UPDATE

Found this on another question - Am testing now looks good:

The problem was that SQL was configured to allow both TCP and Named Pipes. Sometimes it would try and connect with the latter which doesn't use the standard SQL port. The solution was to prefix the data source/server in the connection string with Data Source=tcp:xxx.xxx.xx.xxx to always ensure it connects via TCP

Patrick Monagin
  • 1
  • 1
  • Can you check for IP address collisions on the network? – Crowcoder Apr 25 '18 at 12:53
  • Is there nothing at all in the eventlog and SQL log of your sql server to go with these times? or the machine that errors? – BugFinder Apr 25 '18 at 12:54
  • I would use PING to help determine the cause of the issue. You may have a bad Ethernet Cable or router. So checking if ping also fails will help isolate where the issue is occurring. I would also keep task manager open on Server to see if any anomalies are occurring. Also check Event Viewer to see if there are any errors when the issue occurs. – jdweng Apr 25 '18 at 13:26

1 Answers1

0

The problem was that SQL was configured to allow both TCP and Named Pipes. Sometimes it would try and connect with the latter which doesn't use the standard SQL port. The solution was to prefix the data source/server in the connection string with Data Source=tcp:xxx.xxx.xx.xxx to always ensure it connects via TCP

Patrick Monagin
  • 1
  • 1