The infamous SSPI Failed error strikes again!
One of our SQL servers was generating these errors for “some” Windows logins but not all.
Error: 17806, Severity: 20, State: 2.
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.1]
Error: 18452, Severity: 14, State: 1.
Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.1]
After exhausting all of the normal troubleshooting for this error (accounts locked, disabled, Sql Service accts, bad connection strings, SPN’s, etc.) I spent the next few hours learning more about the way SQL handles authentication requests than I had ever wanted to know.
The Scenario –
A couple of separate individual Windows ID’s started generating these errors while attempting connections, all other windows logins were working properly. The connections were initially happening through applications, but also occurred through sqlcmd. When logged in to the server locally with the offending ID’s the connections to SQL would succeed.
The Troubleshooting process –
Check all the regular SSPI issues, I wont bore you with the details as they are easily searchable
- A relatively easy way of checking the “easy” authentication issues If possible/appropriate is to log into the SQL Server locally with the offending ID and fire up sqlcmd and connect to the server via sqlcmd –Sservername,port –E (by specifying the port you force TCP/IP instead of LPC, thereby forcing the network into the equation)
Verify whether the login is trying to use NTLM or Kerberos (many ways to do this but simplest is to see if there are any other KERBEROS connections on the machine)
- SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections
- If Kerberos is in use, there are a few additional things to verify related to SPN’s, since only NTLM was in use on this server I skipped that
Determine if the accounts were excluded from connecting to the machine through the network through a group policy or some other AD setting
After all of these checked out OK, I began to try and figure out what the error code 0x8009030c meant, turns out, its fairly obvious what the description is : sec_e_logon_denied. This description was so helpful I thought about making this server into a boat anchor but, luckily for my employer the server room is located many miles away and has armed guards.
Since I knew we could logon locally to the SQL Server with the ID that SQL was rejecting with logon denied something else was trying to make my life miserable.
We didn’t have logon failure security auditing turned on so, I had no way of getting a better error description, As luck would have it though this would prove instrumental in finding the root cause. To get a better error message, I found this handy KB article detailing steps needed to put net logon into debug mode.
Say hello to my new best friend! — nltest.exe
After downloading nltest & using it to enable netlogon debugging on the SQL Server, I got this slightly better message in the netlogon.log file
06/15 14:15:39 [LOGON] SamLogon: Network logon of DOMAIN\USER from Laptop Entered
06/15 14:15:39 [CRITICAL] NlPrintRpcDebug: Couldn’t get EEInfo for I_NetLogonSamLogonEx: 1761 (may be legitimate for 0xc0000064)
06/15 14:15:39 [LOGON] SamLogon: Network logon of DOMAIN\USER from Laptop Returns 0xC0000064
The error code 0XC0000064 maps to “NO_SUCH_USER”
Since I was currently logged in to the server with the ID that was returning no such user, something else was obviously wrong, and luckily at this point I knew it wasn’t SQL.
Running “set log” on the server revealed that a local DC (call it DC1) was servicing the local logon request.
After asking our AD guys about DC1 and its synchronization status, as well as whether the user actually existed there, everything still looked OK.
After looking around a bit more I discovered this gem of a command for nltest to determine which DC will handle a logon request
C:\>nltest /whowill:Domain Account
[16:32:45] Mail message 0 sent successfully (\MAILSLOT\NET\GETDC579)
[16:32:45] Response 0: DC2 D:Domain A:Account (Act found)
The command completed successfully
Even though this command returned “act found” it was returning from DC2. (I dont exactly understand why the same account would authenticate against 2 different DC’s based on a local desktop login or a SQL login but it apparently can)
After asking the AD guys about DC2 the light bulbs apparently went off for them as that server actually exists behind a different set of firewalls, in a totally different location. While DC2 would return a ping, the console wouldn’t allow logons for some reason. After a quick reboot of DC2, and some magic AD pixie dust (I am not an AD admin, if it wasn’t totally obvious from my newfound friend nltest) the windows Id’s that were having trouble started authenticating against DC3 and our SSPI errors went away.
Interesting tidbit — During troubleshooting, I found that this particular SQL Server was authenticating accounts against at least 5 different DC’s. Some of this might be expected since there are different domains at play but, I haven’t heard a final answer from the AD guys about whether it should work that way.
Reboot the misbehaving DC, of course there may be other ways to fix this by redirecting requests to a different DC without a reboot but, since it was misbehaving anyway, and the AD experts wanted to reboot so we went with that. A reboot of SQL would have likely solved this problem too but, I hate reboot fixes of issues, they always seem to come back!
I made my way to Dallas to take part in the SQL Saturday #35 that the NTSSUG was sponsoring. After reading the excellent reviews from Jonathan Gardner, AJ Mendo, and Bill Fellows. I thought Id keep this very short and say “I concur!” –Profound aint it?
Ok, Ill dig a little deeper–
I saw some great Sessions, met some great people, and caught up with some old friends. All In all a great way to spend a Saturday!
Having had more than a little experience with putting on large SQL events, I think the volunteers and organizers of the event really need to pat themselves on the back, they pulled off a wonderful event and did it with style. In my opinion, one of the best measures of success when trying to tell how well an event “worked” is to gauge what the event looked like to a normal attendee. By that measure Id say the even the toughest critic would say it was a resounding success. Everything looked buttery smooth, and this “normal attendee” didnt see any obvious distractions. It looks like the SQL Saturday model appears to be maturing at a very rapid pace, and this is excellent news! In talking with the organizers at the after party, they were relaying some of their more interesting challenges, I’m not sure I said it at the time (maybe too many beers??) so Ill repeat it here for posterity: I hope the organizers aren’t too hard on themselves because no matter what level of prep, thought, or organization you put into an event, something is ALWAYS going to slip through the cracks and/or not work quite as expected. How these “challenges” opportunities are handled is what separates the avg from the great, and in this case everything was great.
1 general observation I do have –> The after party of the event was, as usual, one of the great benefits of this event and I made plenty of contact with others at Humperdinks and while it was obvious that a few of the speakers, and a most of the volunteers showed up, I suspect I was one of less than 10 “regular” attendees that attended. This is a tragic situation and something that we in the SQL Community need to figure out how to increase participation. The information&contact info gained from the social pieces of community events is an aspect that most attendees are obviously overlooking. I’ve often wondered if it was apathy or just lack of communication about these events and this time it was obviously not communication, It was pretty tough for anyone to overlook the after party event at #sqlsat35, I saw the after party mentioned in at least 3 different places in the guide book, not to mention verbally at the closing reception. I’m not sure how we can encourage people to attend these events but, they are a core part of the “connect” piece of the equation.
I’m happy that I finally figured out how to clear my schedule enough to get to Dallas and soak up some free SQL Server Training
If you happen to be at the SQL Saturday #35 in Dallas this weekend, please introduce yourself. Ill be there and as always wearing my (Ugly)green SQLPASS 2006 laptop bag.
I really wish PASS still gave out real laptop bags, you’d be surprised how many people will stop and ask you about it if they’re SQL Server related. Its too bad they decided to do away with this ice breaking networking tool in favor of a 1 time use, throw it in the trash poly bag.
I’m excited that I’ll finally be able to experience a SQL Saturday firsthand. Sure its about a 6ish hour drive but I’m sure the experience will be worth it. Not to mention being able to catch up with a few friends who I’ve spied on the attendance list
Expect a post after the event with my first SQL Saturday impressions
Want to learn SQL from a master (or even better a pair of masters?) Have a free evening? Live within a reasonable drive of Houston? You wont want to miss this presentation. Over the years I’ve had the opportunity to listen to hundreds of different SQL speakers and 2 people who would make my short list of “don’t miss presenters” happen to be presenting at a HASSUG meeting this month.
If your not in Houston, I’d recommend using the LiveMeeting link!!
The following Info is from the http://houston.sqlpass.org site
Special Evening Meeting in THE WOODLANDS!
When: Tuesday, May 18, 2010 – 6:30pm-8:30pm
Where: Woodforest National Bank
25231 Grogans Mill, Suite 550
The Woodlands, TX 77380
Topic: Essential Database Maintenance
Presenters: Kimberly Tripp & Paul Randal, SQLSkills
LiveMeeting Link for May 18 presentation –
Online portion of meeting to begin at 7pm
Conference Call for audio – 1-888-320-3585 (passcode 76027128)
Hey, I bet you’ve heard by now that the PASS Call for speakers is now open.
No matter what your skill level as a public speaker, I would encourage you to submit an abstract. Even if you think you’re not good enough or don’t have enough speaking experience, go ahead and submit an abstract. It may be your lucky day and your abstract will be selected, at worst it will be practice in the art of composing a good abstract.
Why will this be good experience? After looking through thousands of abstract submissions for years I’ve decided that composing a good abstract is an art. It requires equal parts black magic and pixie dust to make a good abstract, and even when you have that it takes a bit of luck to get it accepted at a national conference.
In order to help potential speakers understand why their abstracts weren’t selected, last year I asked the review teams to tag each unaccepted abstract with a reason that it couldn’t be accepted. As you might expect this created quite a bit of extra work for the teams but, for new speakers there should be good value in knowing whether the reason for no selection was the competition, a poor abstract, lack of interest in the topic, or some other reason noted. The system isn’t perfect by any means because we don’t have the means to provide a detailed reason for no selection but, hopefully its useful.
One final reason I think everyone reading this should submit an abstract—> if during the submission process you select the “speaker bureau” checkbox, your info will be used by other PASS events needing speakers so you will have more potential exposure that will help you get the experience needed to speak at the Summit.
Important pages I would use if I were submitting an abstract to ANY SQL related event
I love examples, and learning from examples is what works for me so the currently submitted abstract list is a wonderful resource for learning how to write an abstract, not to mention scoping out the competition http://sqlpass.eventpoint.com/submittedsessions
The definition page – Obviously you cant submit a session if you don’t know the rules http://sqlpass.eventpoint.com/Definitions
Pre/Post Conference prerequisites – Already an expert? ready to present a full day and get the recognition you deserve? this one is for you http://www.sqlpass.org/Community/SpeakerResource/PrePostConferencePrerequisites.aspx
Understanding the selection process – Want to know how your abstract will be evaluated? This is the place! http://www.sqlpass.org/Community/SpeakerResource/AbstractSelectionProcess.aspx
The speaker resource page – lots of good info here, don’t be afraid to dig around http://www.sqlpass.org/Community/SpeakerResource.aspx