Home / server / Questions / 14160
In Process
SQLGuyChuck
Asked: 2009-05-29 15:23:21 +0800 CST

Troubleshooting IIS to SQL 2005 connection issues in complex environment

  • 772

I have a complex environment, many highend cisco network boxes, firewalls, and routers. I have many (100-200) connection timeout errors (just authenticating, not running any queries) connecting from IIS to SQL server 2005 using windows authentication. Our network engineers have poured over tcp/ip dumps, we don't have AD experts in house, Kerberos doesn't seem to work in our db environment, so I thought you all might be able to point to documentation on how IIS makes a connection to SQL server where the domain of the servers are different from the domain of the user and the user's domain is housed in another state over dedicated leased lines. Both locations have redundant DC controllers and site metrics setup. Network geeks think that the time it takes kerberos to fail and default to NTLM could be issue.

More info: We brought in MS premier consultant again, and we found LSASS.exe sp? was consuming tons of CPU on AD boxes. Culprit was Barracuda SPAM box doing queries against AD for non-existant meta data columns. If I found out how he figured that out, I will post.

TIA, Chuck

sql-server active-directory

3 Answers

  1. I am not sure you will get a good answer unless you break up your problem into smaller chunks.

    I would start with these steps, undoing any changes that don't help. Please update your question as you obtain more information about the problem:

    • Check DNS -- make sure your DNS servers are responding promptly:

      • try using a hard-coded IP address in your data source, or
      • add an entry in the hosts file to eliminate name resolution latency as a problem.

    • Rule out NT auth as the problem by switching your web app to use SQL auth. Normally I would not even think of this but you mentioned some authentication problems in your question.

    • Verify that there is no packet loss between the web server and the DB server.

      • Try ping with a high packet size, even running several instances of the following ping command at once: ping -t -w 100 -l 5000 dbhost

    • Try altering your connection pooling settings.

    • 2

  2. Microsoft's KB319723 describes using Kerberos with SQL Server and IIS. It should not be a problem to authenticate to SQL servers in one domain using user accounts from another domain, so long as they're within the same AD forest. I have, however, seen issues connecting to named instances of SQL server from outside of the domain. You have to set up a SQL alias for the named instance.

    • 0

  3. Here is the tool Premier support used to find why lsass was using so much CPU: spa_v2.msi “Server Performance Advisor” Change drop down value to active directory and with the aid of HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\Diagnostics REG_DWORD change value from “0” to “5” then view Directory Services log for errors.

    This revealed the culprit lookup of OtherMailBox.

    He then pulled this out of his head, cmd window: Ldifde -F foo.txt –s corpdc01 –P subtree –d “DC=Corp,DC=redmond,DC=Local” –r “(OtherMailBox=*)”

    To determine if we were using this object in the AD. We were not. The log showed the barracuda’s IP addresses and NSLOOKUP did the rest. Handed that info to IT Admins and they were able to fix.

    • 0