none
A network-related or instance-specific error occurred while establishing a connection to SQL Server, randomly RRS feed

  • Question

  • Hi,

    I seem to be having an issue every once in awhile in my .Net applications (4.5.2 framework) connecting to the production sql server randomly. I can go days without any issues and then between 1 - 4 applications at different times encounter the below error. Seconds later, there is no issue. Has anyone encountered this issue before? The sql server isn't logging any issues.

    Error: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)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at FlightPlanIngest.Service1.ProcessFile(String strFile, String strFullPath)
    Thanks!

    Tuesday, July 4, 2017 5:46 PM

All replies

  • You have SQL Server issues and not C# issues. Maybe you should post to the SQKL Server forum and possible ADO.NET issues.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    Tuesday, July 4, 2017 6:28 PM
  • Sounds like network issues to me... bad network connections between the machine(s) where your applications are and the SQL Server machine. You should have a try/catch around your connection attempt to catch the exception, and just retry it in a few seconds. Maybe have it in a while loop or something similar.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, July 5, 2017 1:23 AM
  • Hi MarsdenM,

    According to your question is more related to ADO, I will move it to ADO.NET Managed Providers form for suitable support.

    This forum discuss and ask the C# programming language, IDE, libraries, samples and tools.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 2:02 AM
  • Hi MarsdenM,

    Please check the following link, which provide a solution with 10 steps.

    Step 1

    Make sure you are able to ping the physical server where SQL Server is installed from the client machine.  If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance. 

    Step 2

    Make sure the SQL services are running  You can check the SQL Server services by using the SC command or SQL Server Configuration Manager. Many times you may find that the SQL Server instance is not running

    Step 3

    Check that the SQL Browser service is running.  If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port.  To resolve this you will need to have the SQL Browser service enabled and running

    Step 4

    Make sure you are using the correct instance name.  When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you should specify machinename\instancename

    Step 5

    Check that SQL Server is in the network. You can use the SQLCMD -L command to retrieve the list of SQL Server installed in the network.  Note that this will only return SQL Servers if the SQL Browser service is running.

    Step 6

    Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.

    Step 7

    Check to see if remote connections is enabled. Right click on the server name in SSMS and select Properties.  Go to the Connections tab and make sure Allow remote connection to this server is checked.  If you need to make a change, you must restart the SQL Server instance to apply the change.

    Step 8

    Check the error log for issues.  Locally connect to SQL Server and check the error log for the port entry.  You can execute XP_READERRORLOG procedure to read the errors or use SSMS.  You should see entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433.

    Step 9

    Configure the Windows Firewall for the SQL Server port and SQL Browser service.  Go to Control Panel -> Click on Windows Firewall -> Go to exception tab as shown below.  You can also read this tip for more information as well.

    Step 10

    If you are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer then execute the below to check the SPN.

    https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/

    Note:This response contains a reference to a third party World Wide Web site.Microsoft is providing this information as a convenience to you.

    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.

    There are inherent dangers in the use of any software found on the Internet, and Microsoft
    cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 8:24 AM
    Moderator