• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Long delay when connecting to SQL 2005

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
MichaelFlanagan
Asked:
MichaelFlanagan
1 Solution
 
derekkrommCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MichaelFlanaganAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
MichaelFlanaganAuthor Commented:
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
 
MichaelFlanaganAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MichaelFlanaganAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
MichaelFlanaganAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
MichaelFlanaganAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do the legacy applications specify a default database in the connection string, or do they rely on the DSN configuration?
0
 
MichaelFlanaganAuthor Commented:
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
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now