January 25, 2005 at 7:55 am
Hello All - We have an application, running on the client side, that connects to a dedicated SQL server (SQL 2000 SP3). The clients are spread out across the world but the SQL server is in North America (NA). The application group is claiming performance problems with users that reside outside of NA when using Windows Authentication. They've since changed the server to "SQL and Windows" mode, started using SQL Authentication for user connectins, and claim the problem has gone away. I wasn't involved in the diagnosis of the initial problem but now need to get to the bottom of this. I have a hard time believing this, but maybe I'm missing something.
Whan SQL server receives a Windows Authenticated client connection, does it go to a domain controller (DC) to verify? If so, does it go to the DC that is geographically the closest? Can you control what DC it goes to? Anyway to trace this?
Does it go to the DC that the client authenticated against?
Does it matter that the users outside NA are on another domain (trusts exist between the domains) and SQL needs to go back to a DC that is in the clients domain?
I can see that this would happen if SQL needs to contact the DC in the user's domain, as it would be extra trips across the network.
Anything else that would explain this behavior? Or ways to avoid it?
Thanks!
January 25, 2005 at 8:29 am
What is the connection method ? ADO, ADO.Net ?
Maybe post 2 examples of connection strings, 1 that is slow, 1 that is responsive.
January 25, 2005 at 8:39 am
Jason, are you using Named Pipes or TCP/IP? I believe TCP/IP is greatly preferred as the default protocol in a bandwidth constrained WAN. See BOL for a detailed explanation.
Ryan
January 25, 2005 at 8:39 am
Not sure, I'll find out, but it is a vendor application that I don't have the code for.
I would think the connection strings would use the same method except one would be trusted and the other would have and ID and password.
Would SQL handle a Windows Authentication connection differently based on the connection method?
January 25, 2005 at 9:38 am
SQL Server hitting the network for NT account verification has got to be a factor in here somewhere. I am by no means conversant with the subject, but if you're granting SQL Login access to NT security groups, SQL has to be going out to the network to check what groups a login attempting to establish a connection is a member of. Heck, it would probably have to do that if you granted access rights to any groups, just to make sure all rights are properly applied.
(Presumably, if you permit direct access to NT users and only NT users, the NT security token presented to SQL Server would be sufficient authentication--but that is a presumption.)
Darn good questions relating to the DCs. Any network gurus out there?
Philip
January 25, 2005 at 9:39 am
There is a piece of the environment that I forgot to mention, sorry. The clients are making a TCP/IP connection from their workstations through .NET Webservices to an application server. The application server is then making a TCP/IP ADO.NET connection to the SQL server. No users make direct connections to SQL, they all go through the application servers.
January 25, 2005 at 10:08 am
Can you pull the connection string from the web.config file of the web service ?
Is it by chance using an IP address instead of a name ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;300420
January 25, 2005 at 10:33 am
I'm not sure of the name, but I know there is a tool that will tell you which DC you are connecting to. It's possible that the clients or the server are connecting to a remote DC for some reason.
January 25, 2005 at 10:42 am
Philip has come close to hitting this issue on the head. Depending on how you connect to SQL either via SQL login or Windows Auth tells the server which security layers to apply.
I remember reading about this when I was able to study for MCDBA (hope to take a test someday). IF you use SQL auth. the connection goes directly to SQL server for allow/deny BUT if windows auth. is used it stops at the server-level (possibly DC along the way) and then gets transferred to SQL....
How much of an overhead this additional step could mean I can't say, just remember reading about it...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 25, 2005 at 11:19 am
PW - I'll see if I can get the connection string from the web.config file, but I don't have access to that server so there may be a delay.
Steve - If you think of that tool's name to find the DC that is used for Authentication, I would love to know.
Any other places I should look for this type of info? Or is it a call to MS Support?
July 12, 2005 at 3:57 am
Did you ever get to the bottom of your problem?
I have encountered the same thing when connecting from Delphi consoles (via BDE) to SQL Server over a network.
Thanks,
July 12, 2005 at 6:23 am
Sorry to say we never got to the bottom of it. After spending a fair amount of time on it we decided to continue using SQL Authentication.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply