MYSQL - Time Out Error (maybe)

[tonycummins4561 (TonyCummins)]

Hi Guys, I have a vendor trying to run their conversion tool to update / configure a SQL DB and they are claiming that the server / SQL on my side is the culprit. They are receiving the below error and are asking me to correct it. I’m not sure where i should be starting to look. I would appreciate any pointers / assistance in troubleshooting the “timeout / connection” issue.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: 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)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass2_0.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   --- End of inner exception stack trace ---
   at DataIntegrationWizard.WizardHostForm._ValidateDataFile(DataIntegrationWizardArgs args) in C:\PathToConversionTool\WizardHostForm.vb:line 5242
   at DataIntegrationWizard.WizardHostForm.IntegrationWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\PathToConversionTool\WizardHostForm.vb:line 1808

[Rod-IT]

If the vendor is using DNS, this could be a DNS issue.

Can they ping the server, can they telnet the port to confirm it’s not a firewall issue between them and you?

[ode2joy]

@tonycummins4561

error: 26 - Error Locating Server/Instance Specified)

Here’s a handy little guide I’ve used for troubleshooting this and other similar errors in the past:

A network-related or instance-specific error occurred - SQL Server

[tonycummins4561 (TonyCummins)]

Rod-IT, I should have mentioned they are physically connected to the server on my network when running their conversion tool.

Ode2joy - Thank you for the document link, ill look through it

[Rod-IT]

They can still test the port, but now they can confirm SQL is running in services and confirm they can get in to SSMS.

They should know how to do basic troubleshooting, especially if they are on the same box.

[craigrrr]

They’re not getting far enough for any actual errors for connection/login/timeouts. They’re straight up not finding the sqlsrvr instance at all. 1) Do they have the sql instance name correct, for the server? 2) does firewall allow connections or do they need added as permitted?

[somedude2]

Yes, that, they are talking to the wall…

@tonycummins4561

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

It’s the wrong name, or the wrong machine…

(or SQL is configured to not allow external access)

[tonycummins4561 (TonyCummins)]

So i got a little more info from vendor. They are claiming the following:
There is something that isn’t connecting in SQL because the tool is running and comparing the records up to a particular line or quantity in the .txt file. I have had a successful validation on the 1st 50k Records.
The tool had a connection but then at some point it no longer could find the server/instance. I checked the line in the .txt file where it fails, the data points are fine; so, is it not working after a certain number of lines bc the SQL connection reached a capacity of some sort? (With each line it connects to the database bc it is looking for records that currently exist or it will create a new record).

So basically saying the tool connects…starts and after xx time throws the cant find server / instance.

[craigrrr]

Maybe check the remote query timeout setting on the server level connection properties. Or the login timeout. But you’d think then it would just throw a timeout error…
Time to dig into the server logs.

[molan]

how long does it take the query to run? I remember having a similar issue years ago with SQL and a 3rd party vendor where the query ran so long that SQL server would simply end it (time out) before it could finish. this kind of sounds similar.

what is the vendor?

[craigrrr]

Hmmm… what is the connection pooling settings for the connection setup they are using?

[somedude2]

Yeah..pool depth..the tool may actually be making many many connections as it walks through the data set, exausting the available connections..

[craigrrr]

So basically, if connection pooling is off/false, set it to on/true. If it is already on/true, and the max connection pool is set to, say, 500; set it to 1000 and see if the job doesn’t get another 50k lines done before crapping out.

[tonycummins4561 (TonyCummins)]

Thanks for the pointers, let me do some digging around and report back.