September 21, 2007 at 10:38 pm
Hello.
-We are running Active Directory where all servers and clients are part of our "MYDOMAIN.LOCAL" domain.
-We have a Win2k3 server running SQL 2000.
-We have in-house and remote clients, all running WinXP SP2 and all personnel are part of the "Power Users" sec grp.
-We have some older apps that require an ODBC connection. A System DSN was therefore pushed to each applicable WinXP client and is set to use Trusted Connection; the current user's credentials.
-There is a significant amount of latency between the central office (servers) and the remote locations (clients).
-All but one of our remote locations can access our SQL server using this ODBC connection. The problematic location receives the following error dialog window, after a minute or so passes while attempting to connect:
CONNECTION FAILED: SQLState: 'S1000' SQL Server Error: 0 [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
-Sometimes, this error is given:
Connection failed: SQLState: '28000' SQL Server Error: 18452 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with trusted SQL Server connection.
The only way around this issue was to create a SQL Account and reference those credentials (account and password) in the app's connection string. Unfortunately, the app's connection string is stored external to the app in an .INI file - yes; highly insecure.
Strangely, if the Domain Admin is logged into the same XP client, that same ODBC connection works without issue.
-Searching the System Event Logs, I also noticed the following errors:
Source: LSASRV, Event ID: 40960 "The security system detected an attempted downgrade attack for server MSSQLSvc/SQL123.mydomain.local:1433. The failure code from authentication protocol Kerberos was "There are currently no logon servers available to service the logon request. (0xc000005e)"
...which is followed Event ID: 49061 "The security system could not establish a secured connection with the server MSSQLSvc/SQL123.mydomain.local:1433. No authentication protocol was available."
Anyone else having a similar experience?
September 22, 2007 at 1:41 am
Have a look here:http://support.microsoft.com/kb/811889
[font="Verdana"]Markus Bohse[/font]
September 23, 2007 at 3:24 pm
Hello. Thanks for the feedback.
The SPN listed in the error message is correct, as is the results of ping-ing the SQL server by NetBIOS name OR IP with -a specified to return the NetBIOS name.
The most significant portion of that article, in regard to my situation, is:
"If the SQL Server driver forms an SPN that is valid but is not assigned to the appropriate container, it tries to use the SPN but cannot, causing a "Cannot generate SSPI context" error message."
Strangely, this issue is only happening at one of my remote locations AND to all of the clients at that location ... except when the domain admin is logged in. Perhaps that is just a coincidence.
Thanks again,
Paul
September 24, 2007 at 8:04 pm
As I originally suspected, the issue is due to latency between the central office (servers) and remote office (clients). In short, Kerberos is unable to send and/or receive authentication packets between the client and server.
By default, Kerberos travels over UDP. By changing the Windows Registry, one can instruct Kerberos to use TCP instead. UDP is session-less while TCP uses acknowledgments to ensure the data is delivered.
The remedy was to apply the following change at the Domain Controllers (the DNS servers that are referenced in the client computer's TCP/IP stack) as well as at each client experiencing the issues described in my original post. BE SURE TO REBOOT after making this change.
Microsoft KB article 244474:
http://support.microsoft.com/kb/244474
When a slow link is detected, Group Policy processing changes from its default behavior. Specifically, certain Client Side Extensions (CSE's) will not process when a slow-link is detected. The table below lists the default state of each CSE during a slow-link detection:
CSE............................. Processes on Slow Link?
=================== ====================
Security....................... Yes and cannot be disabled
IP Security................... Yes
EFS Recovery................ Yes
Wireless Network............ Yes
Administrative Templates.. Yes and cannot be disabled
Scripts.......................... NO
Folder Redirection............ NO
Software Installation........ NO
IE Maintenance............... Yes
By default, the slow link threshold is set to 500Kbps. This is the speed of the link between the computer that's processing Group Policy and the Domain Controller providing the policy. You can change this threshold by modifying the following two Administrative Template policies:
1. Computer Configuration\Administrative Templates\System\Group Policy\Group Policy slow link detection
2. User Configuration\Administrative Templates\System\Group Policy\Group Policy Slow link detection
Note that slow-link detection is performed per computer and per user. The process by which Windows uses to determine the link speed is the following:
A computer will ICMP ping the domain controller that is providing it policy with three pairs of pings. Within each pair, the first ping is of 0 bytes of data while the second contains some amount of data (usually 2048 bytes). The difference in response time between the first and second ping of each pair is averaged and, based on the amount of data that is sent, a link speed is derived. Note that if any ping returns in less than 10ms (milliseconds), then a fast link is automatically assumed.
Note that if you disable ICMP on your network, then Group Policy processing will fail ... unless you disable slow-link detection by setting the two aforementioned Administrative Template policies to 0. However, it is strongly recommended to ENABLE ICMP traffic over your trusted LAN.
If you're really interested, here's how a connection is determined to be "Slow":
Good luck,
Paul
InfinityRD.com
September 28, 2007 at 8:58 pm
As a follow-up, Microsoft engineers were kind enough to share this additional feedback with me:
The following Network Interface Cards are known to exhibit such issues on occasion:
- Broadcom Gigabit Adapter
- Intel Gigabit Ethernet PRO Adapter, Intel Pro/1000
- Intel 82544EI-based XT Gigabit Adapter (82540EM chipse)
- Compaq/HP NIC dual interface 10/100/1000 doing teaming (HP NC7170)
- Dell Inspiron laptops using an on-board Broadcom BCM4401 NIC
September 28, 2007 at 10:00 pm
Can we get their contact info??? That'd be nice to have in out contacts :w00t:.
September 28, 2007 at 10:09 pm
Unfortunately, that list (and I'm certain others like it) is unpublished publicly and only shared amongst M$ Support Engineers.
By the way, the engineers were in New Delhi, India. Very smart and courteous; always an opportunity to learn something from the experts.
September 28, 2007 at 10:14 pm
Well I'm as MS sql server engineer so I should be able to access that list ;).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply