Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Long delay when connecting to SQL 2005

Posted on 2007-07-23
15
Medium Priority
?
251 Views
Last Modified: 2012-05-05
I have just recently added a SQL 2005 server to our server rack. The issue I am having is that is takes a rediculous amount of time to connect to the server either via code or even just creating a DSN.

When I go to one of the web servers and create a new DSN to the server it takes over a minute to connect to the server once I enter the username and password to connect via SQL authentication. Any other server that I connect to returns in a second or two.

Anyone have an idea as to why this is happening?
0
Comment
Question by:MichaelFlanagan
14 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 19549992
Check for any IP conflicts. I've had a problem similar to this before where someones desktop was trying to steal the server's IP address
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19550012
you will have to identify which part of the connection makes the connection slow:
* sql server itself -> how fast is a local connection?
* dns resolution -> ping servername , how much time does it take to start the ping, ie the time to resolve the name into the ip address?
                 -> try to connect via the IP address instead of the server name
                 -> try to connect via the full dns name (servername.domain.com for example) instead of only servername
* network speed -> ping servername, how fast are the ping packets coming back?
0
 

Author Comment

by:MichaelFlanagan
ID: 19550851
Ping of servername is instantanious.
Packets are returned in <1ms

Creating a DSN using the server name or the IP address from my workstation takes 5 minutes to establish the connection.

Now what is very fast is connecting to the server via SQL Server management server is very quick.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MichaelFlanagan
ID: 19555785
More info...

When I create the DSN using SQL Server Authentication it takes 4-5 minutes to establish the connection and allow me to continue to create the DSN.

When I create the DSN using Windows Authentication it is instantaneous.

So it seems that there is something going on with the SQL authentication process. Is there any setting on the SQL server side that could be causing it to take so long to authenticate the user/password combination?
0
 

Author Comment

by:MichaelFlanagan
ID: 19558337
OK. Here is even more info:
 We captured the traffic to and from the SQL server as we created an ODBC connection. What happens is that the Acknowledgement from the SQL server comes back right away and then a few packets that contain and enumeration of the databases on the server. After a few minutes another group of packets are sent. Once all the packets are received it appears that we have a complete list of all the databases on the server that we have access to. So the more databases I have on a server, the longer it takes to make the connection.

I have now confirmed that it is not the new server I just put in place - it is any server that has a large number of databases (600-700 for example).

So I am thinking it has something to do with the ODBC driver and how it decides what the connection has permission to access??

Anyone have an idea on what this might be? I do not remember this ever happening in the past even on servers that have lots of databases.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19558361
600-700 database.... that's quite alot.

now, dsn means ODBC, do you actually really want to use ODBC to connect to SQL Server 2005, where the Sqloledb provider is much more efficient?
0
 

Author Comment

by:MichaelFlanagan
ID: 19558463
We have a number of legacy apps written in C++ that still use the ODBC for connection. It is a good point and one that I will test to determine if our code that uses Sqloledb are seeing the same results.

Our product is written such that each client has their own copy of the application database. The are not large in most cases and everything appears to be running smoothly once they have the connection.

Does it make sense that the ODBC provider is authenticating against each and every database on the server before the connection is completed? Sounds like that is what is happening...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19558931
>Does it make sense that the ODBC provider is authenticating against each and every database on the server before the connection is completed?
it is actually not authenticating against all the databases, but getting the list of the databases, and the mapped user information for the login you specified...
0
 

Author Comment

by:MichaelFlanagan
ID: 19559557
OK. I'll accept that. But why would it take 4 minutes to create the connection using SQL authentication and only a second to complete the connection with Windows Authentication. Again - the more databases on the server, the longer it takes.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19559593
>But why would it take 4 minutes to create the connection using SQL authentication and only a second to complete the connection with Windows Authentication.
that sounds interesting indeed. do you use a sql login with many databases granted permissions to?
0
 

Author Comment

by:MichaelFlanagan
ID: 19560070
Yes. All of the databases have the same sql login assigned to them. Our web application uses the single DSN for its connection. I am going to change the connections everywhere that I can to the Sqloledb to eliminate the problem but now it is more of a curiousity to me. I also still have to deal with the legacy apps that I cannot change from ODBC.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19560080
do the legacy applications specify a default database in the connection string, or do they rely on the DSN configuration?
0
 

Author Comment

by:MichaelFlanagan
ID: 19565958
The connection string includes the default database. The DSN is set up with "master" as the default database, so we override that with the connection string...
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22422713
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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