SQL Login timeout on 2008 R2 VM Server

Hi guys…

Would really appreciate your help on an issue.
We have a virtual 2008 R2 server running some software called Exact. This is a dutch ERP system.

The Software is installed on the server, and we several users have clients installed on their laptops to connect to the server.

Several times a day the users cannot connect using the client on their laptops, and get an sql error message like the below.

The VM has 2 virtual sockets, with 2 cores per socket, so 4 cores in total.
It has 6GB of RAM.

We only have 1, maximum 2 clients connecting to the server at a time.

Could anyone advise how to solve this issue. It isn't consistent. Some times the user can connect, other times they cannot. The server is running SQL server 2008.

Source: C:\dev\E4SLAYER.DLL\edb.cpp (line 3960)
Cannot connect with 'DRIVER={SQL Server};Server=EXACT-SERV;Database=002;TRUSTED_CONNECTION=YES'.

[Microsoft][ODBC SQL Server Driver]Login timeout expired
SQL State: S1T00
EDL error: An error has occurred in the execution of the ODBC function 'SQLDriverConnect'

Thanks
HICTAsked:
Who is Participating?
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
Who is running that event set you're seeing? Do you have access to SQL Profiler so you can trace it and see what's going on? Also, do these events in the log correspond at all to periods of the issue you're seeing?

Flushing the plan cache shouldn't have any impact on connectivity at all - it just dumps the compiled execution plans. However, it requires the "ALTER SERVER STATE" permission set, and unless you've granted this explicitly (or it's a scheduled system job), that indicates to me that your application server might have sysadmin-level permission, which is a bit concerning. While some application vendors say they require sysadmin rights for their application server logins, but that gives the server the ability to make all kinds of on-the-fly configuration changes, like these.

If you run SQL Profiler, you may be able to see if anything else is being run by the application server at those times. While this statement you've shown gets logged in the application log, not all configuration statements do.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
Is there anything else running that connects to the SQL Server frequently, and if so, does it experience the same timeout issue with about the same frequency (or in the same timeframes as you're seeing here)? Also, have you seen any utilization logs and/or graphs from either the VM or the underlying physical hardware? Also, I don't see what VM platform you're using - is it Hyper-V, VMWare, or something else? Also, how often do the interruptions happen, and are they on a regular schedule that happens to coincide with any other network activity you're aware of?

We've had temporary interruptions in connectivity to our ESX virtual servers when the underlying physical hardware or storage channels are at capacity - it's brief, but it's obvious if the software isn't built to tollerate hiccups like that. I'd encourage you to check the utilization of the underlying physical hardware and ensure that it's within reason. Virtualization adds a layer of complexity - even though your VM may internally appear to be performing well within its capacity, the host can be a completely different story, causing lag and unpredictible interruptions when things appear fine on the VM.
0
 
HICTAuthor Commented:
Hi Ryan ...

We are using VMware, on ESXi 5.0 currently.
The errors seem to occur randomly. The user can be in the client working for a few hours, then suddenly they will get the timeout error.
There is nothing else utilising this SQL db other that the Exact software.
The SQL instance, and the Exact software are on the same VM.

I've had a look in the Application event log, and see the following 'information' events :

Configuration option 'remote query timeout (s)' changed from 0 to 30. Run the RECONFIGURE statement to install.

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Configuration option 'remote query timeout (s)' changed from 30 to 0. Run the RECONFIGURE statement to install.

Are these of any help?

Thanks
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HICTAuthor Commented:
Hi Ryan...

You'll have to forgive me, I'm not particularly au fait with SQL at all.
This instance was installed by software provider, and their support team seems to have given up on this issue. I have therefore taken it upon myself to try to solve the matter.

The events came from the server, and seemed to be generated shortly after the error.
The server is logged in as an administrator. And the clients are administrators on their own computers.

I'm not familiar with SQL Profiler i'm afraid.

Any instructions you can offer, I will have to follow parrot fashion, but will be gratefully received.

Thanks
0
 
bremboapCommented:
Hello,

Exact samen issue here. Exact installed on server (Server 2003 32B) and local. have 2 identical HP Probook 5320m Windows & 64b laptops with Exact Globe installed on. DB installed on SQL 2005 32b.

Use the same user on both laptops both only one allows connection to the DB.
Seems to me that is hase something to do with Windows updates, Service Pack 1.

Other laptop shows below error.


Source: C:\dev\E4SLAYER.DLL\edb.cpp (line 3960)
Cannot connect with 'DRIVER={SQL Server};Server=EXACT-SERV;Database=002;TRUSTED_CONNECTION=YES'.

[Microsoft][ODBC SQL Server Driver]Login timeout expired
SQL State: S1T00

Any solutions known?
0
 
HICTAuthor Commented:
Hello Bremboap ... so far we have not been able to find a solution.
I will try SQL Profiler, and see if anything shows up.

Thanks
0
 
Ryan McCauleyData and Analytics ManagerCommented:
Have you had a chance to try using SQL Profiler to see if that reveals anything odd?
0
 
HICTAuthor Commented:
Hi Ryan ...

in the end we rebuilt the sql installation.

thanks
0
 
Ryan McCauleyData and Analytics ManagerCommented:
And this resolved the issue? we're having intermittent connectivity problems with a SQL VM Cluster here, and I'd like to know what resolved it, or what you tried that didn't resolve it, as we're at a loss. I have to admit, attempting to help solve your problem was a bit self-serving :)
0
All Courses

From novice to tech pro — start learning today.