Solved

SQL Login timeout on 2008 R2 VM Server

Posted on 2013-01-25
9
2,860 Views
Last Modified: 2013-05-20
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
0
Comment
Question by:HICT
  • 4
  • 4
9 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38825693
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
 

Author Comment

by:HICT
ID: 38827352
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 38827730
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:HICT
ID: 38828058
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
 

Expert Comment

by:bremboap
ID: 38843788
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
 

Author Comment

by:HICT
ID: 38844125
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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38865214
Have you had a chance to try using SQL Profiler to see if that reveals anything odd?
0
 

Author Comment

by:HICT
ID: 38930525
Hi Ryan ...

in the end we rebuilt the sql installation.

thanks
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38935811
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question