SQL Server, PASS, and other data mishaps
Allen Kinsel
This user hasn't shared any biographical information
Homepage: http://allenkinsel.com
Posts by Allen Kinsel
Shadows Rock, Filtering Platform not so much!
Aug 16th
RDP remote control (shadowing) of multiple sessions is a great way to allow geographically separated teams to work on the same Server console. You can do this from task manager
Control your co-workers!
Today I had a new install of windows 2008 that was rejecting the attempts at remote control, the error was ”remote control failed”, nothing was logged in the System or Application event logs. In the Security event log was only one error: “The Windows Filtering Platform has blocked a bind to a local port”
After plenty of fiddling and making sure there was no ”firewall” or reason for the filtering platform to be enabled, I came across this command I never knew existed “shadow”
Apparently whatever had the filtering platform angry and blocking access was ok with that simple command. So in this case going to a command window and running “shadow 3” worked perfectly, I could once again see both terminals and the windows filtering platform allowed me to actually work, instead of impeding me at every turn.
The Windows Filtering Platform on Server 2008 and 2008 R2 has been the culprit more times than I can count lately when the “gremlins” are inhabiting our servers, If only there were a way you could turn it off totally, but I guess that’s sort of like Internet Explorer, it cant be unbundled from the OS.
Dont forget to vote for the PASS Summit Community Choice sessions
Aug 12th
If you are a PASS member, on August 3rd you should have gotten an email inviting you to vote on four of the sessions to be presented at the 2010 PASS Summit.
There are some great sessions up for selection, You can review them here.
If you havent voted yet, be on the lookout today for a reminder. You should get an email with personal links to vote, it will only take a few minutes of your time and your opinion counts!
If you’ve already voted, I want to thank you, if not please take this time to do so. If we can get enough community interest in this process I can see it growing and morphing into something more so while your opinion counts on the actual selections, your vote can also help shape the future way PASS does session selections.
More on PASS Summit Community Choice sessions & a general PASS update
Jul 30th
More on PASS Summit Community Choice sessions
Last week I posted about letting the PASS membership select 4 of the Community sessions to be presented at this year’s North American Summit. As with most things I’ve touched lately, the devil is in the details. I have been working with PASS HQ quite a lot to get this process all ironed out. As of today, We are expecting the emails with all of the details to go out in the first part of next week. One of the last minute *details* we’ve had to account for is related to asking community members to vote in the 4 different categories, it turns out we aren’t able to require it to work that way. The short reason why is because of the way the PASS voting solution is designed (remember, its actually designed for voting in the upcoming BOD election) We cant have 4 separate categories and allow you to vote only in 1 each. While we are still separating the sessions into categories and asking you to vote on each one separately, we wont be able to enforce it. As it turns out, its not that big of a change and even if our members choose to cast their votes all in the same category, their votes will simply be cancelled out since we are going to choose the highest vote getter in each of the categories. So start watching those email boxes for more detailed information!
Other PASS goodness
Summit Selection Process follow-up meetings, since shortly after the community selection process finished, I have been on phone conferences with the selection teams for feedback about the entire selection process. These meetings have been going great, and overall the feedback was extremely positive, both about the process and the volunteer experience in general. That’s not to say the team members weren’t critical of a few hiccups we experienced but overall it was good. In these meetings nothing was off-limits, and I got some GREAT new ideas that should really improve a few of the key procedural pieces of the process. Many of the technological limitations and issues we knew about going into the selection but, with all the timelines, we just couldn’t get changes made quick enough. Since PASS is building a summit management tool for 2011, we should have many of those technology issues behind us for the start of next year’s selection process. All in all, It always helps us to get feedback about the selection as soon as its over while its still fresh in everyone’s minds. the #1 piece of feedback that kept recurring is: Communication is key. What I have taken from this is that even if you think everyone’s on the same page, its often ok to ask again if there are any questions, better safe than sorry.
Microsoft Speaker selections, Ive been doing this for the last 4 years and this year was the first year where I felt like we (the community team) have been actively engaged with the Microsoft Selection process, we’re still trying to figure out how all the different pieces fit together and where we can add value and community feedback into their processes. So far things are working better than I expected but not as good as I’d like. Seems to be the story of my life these days!!! All of this is great news for the community since it will produce a better Summit but, its been bad news for myself and the other volunteers working on it. For me, being the pseudo ring leader, Its just 1 more thing added to an already full plate this time of year. From the day the call to speakers is announced to the date the summit actually starts, is when things get pretty hectic with the Program Committee, there are always a few “regularly scheduled items” that have to be completed by certain deadlines not to mention the things that go BOOM. The regular scheduled stuff isnt a big deal, the BOOM’s on the other hand, lets just say Disaster Recovery planning isn’t just for Databases or Computers, it is valuable in most any important process.
Community Choice Sessions at the PASS Summit 2010
Jul 21st
Its Still Alive
Yes this blog is still alive! I recently had some hard decisions to make with regards to what I needed to drop in order to get at least 3 hours of sleep in a night. And unfortunately for the 3 of you that read my blog, it was the blog that lost that battle. I should be getting back in the habit of writing now that the biggest crunch time for the program committee at the PASS Summit is over.
What happened?
Short answer : The PASS Summit speaker selection process. (Program Committee Manager role)
Longer yet short answer: The PASS Summit program committee leadership position is a very involved volunteer position, taking more hours of my time than I can count (Just ask my wife!!). For the last month the 4 amigo’s (Jeremiah Peschka (Blog|Twitter) , Lori Edwards(Blog|Twitter), Elena Sebastiano (PASSHQ), and myself) have been working tirelessly towards the first goal of announcing the community sessions. With that out of the way the PASS work doesn’t stop, we get to do other “fun” stuff. Innovate with PASS if you like, because that’s what we’re going to try to do!!
Community Choice Sessions
Ive talked about this before and I’m happy to say, after quite a bit of work in ironing out the details, it is going to be a reality. We’re in the process of building the pages, but I can already say that 20 Speakers are going to get a second chance at being selected to present one of their sessions at the Summit. In order to make this happen, we went over the summit track selections, looking at alternates first, then other sessions that were ranked highly by the review teams. We collected 20 sessions that we thought could fill in gaps in the educational offering, or that we thought deserved another look. Essentially, we took the work the selection teams did and distilled it down to give the community 5 session choices in each of 4 different groups
We split up the sessions into 4 groups, 1 each for the tracks AD/BI/DBA and then added a fourth as a sort of menagerie containing sessions across all 4 tracks. We plan on using the “PASS voting booth” to facilitate the voting in each category, so each “registered PASS Member” will have a vote for a session in each of the categories. At the end of the voting period, the session in each category that has the most votes will be confirmed & put in the lineup to be presented at the 2010 PASS Summit in Nov.
Next week we should have all of the details finalized and Ill write more about it then but, for now be on the lookout for an email from PASS with more details about how you can help directly shape the sessions at the 2010 Summit.
Sql Server and SSPI handshake failed error hell
Jun 17th
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.
The solution
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!
