Solved

SQL Login timeout on 2008 R2 VM Server

Posted on 2013-01-25
9
2,815 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now