28 January 2015

Finding SID Mismatches from SQL Server to Active Directory

So, I had an issue yesterday where a user couldn't log in to a few specific SQL Server instances; they were getting the following error:


The actual recorded login error in the logs was this:
Error: 18456, Severity: 14, State: 11.
Login failed for user 'Dom\User'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.


Eventually I came upon some rather useful resources for this cryptic error, and took some of the existing scripts and rolled it into a new script that takes all the domain user logins, gets the binary SID stored in SQL, maps it to SDDL format, and pulls the equivalent Windows SID via xp_cmdshell and WMI. Then it compares, and outputs the mismatches. This script isn't particularly well tested but it seemed to go over reasonably well on 2005, 2008R2, 2012, and 2014 instances.

Once you find that you do have a SID mismatch, the unfortunate solution is dropping and recreating the login, whereupon you'll have to make sure all permissions and memberships are retained. Also, one thing I found is that even when there is a mismatch of a SID, it doesn't mean that user can't connect, necessarily. I've yet to fully understand the whole process, but if the user is getting 18456 login failures AND there is a mismatch of SIDs, recreating the login will likely resolve that issue.

Classic disclaimer, don't ever run code you find on the internet on your servers as it will likely truncate all your data, give your servers venereal disease, and contribute to Global Warming. So Caveat Emptor! Also, the SDDL formatting code was stolen borrowed from Todd Engen. The Code:

USE masterGODECLARE @DomName VARCHAR(50);SET @DomName = 'YourDomainGoesHere';IF OBJECT_ID('tempdb..#LoginSIDs') IS NOT NULL DROP TABLE #LoginSIDs;CREATE TABLE #LoginSIDs(
  
name NVARCHAR(128),
  
NTname NVARCHAR(128),
  
BinSID VARBINARY(100),
  
SQLSDDLSID VARCHAR(200) NULL,
  
WinSDDLSID VARCHAR(200) NULL
);
/* Get relevant logins and SQL SIDs */INSERT INTO #LoginSIDs (name, NTname, BinSID)SELECT
  
name,
  
REPLACE(name,@DomName+'\',''),
  
SUSER_SID(name)FROM master.sys.server_principalsWHERE [type] = 'U'  /* currently omitting NT groups */AND name LIKE @DomName+'\%'ORDER BY name ASC;/* Cursor through each to get reformatted SQL SID and Windows level SID */DECLARE @LogName NVARCHAR(128), @BinSID VARBINARY(100);DECLARE sidcursor CURSOR FOR SELECT NTname, BinSID FROM #LoginSIDsOPEN sidcursor   FETCH NEXT FROM sidcursor INTO @LogName, @BinSIDWHILE @@FETCH_STATUS = 0   BEGIN
  
/* Convert binary SID to SDDL format */
  
DECLARE
      
@StringSID VARCHAR(200),
      
@i INT,  @j INT,  @val BINARY(4),
      
@WMIcmd VARCHAR(200),
      
@WinSID VARCHAR(200);
  
SELECT @StringSID = 'S-'
      
+CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))));
  
SELECT @StringSID = @StringSID+'-'
      
+CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))));
  
SET @j = 9;
  
SET @i = LEN(@BinSID);
  
WHILE @j < @i
  
BEGIN
       SELECT
@val = SUBSTRING(@BinSID, @j, 4);
      
SELECT @StringSID = @StringSID + '-'
          
+CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))));
      
SET @j = @j + 4;
  
END;

  
/* Get Windows SID from WMI */
  
CREATE TABLE #cmdOutput (txt VARCHAR(255) NULL);
  
SET @WMIcmd = 'wmic useraccount where (name=''' + @LogName + ''' and domain='''+@DomName+''') get sid';
  
INSERT INTO #cmdOutput EXECUTE master..xp_cmdshell @WMIcmd;
  
SELECT @WinSID=REPLACE(txt,CHAR(13),'') FROM #cmdOutput WHERE txt LIKE 'S-%';
  
DROP TABLE #cmdOutput;

  
/* Update temp table */
  
UPDATE #LoginSIDs
  
SET
      
SQLSDDLSID = @StringSID,
      
WinSDDLSID = @WinSID
  
WHERE NTname = @LogName;

  
FETCH NEXT FROM sidcursor INTO @LogName, @BinSIDEND  
CLOSE
sidcursorDEALLOCATE sidcursor
--SELECT * FROM #LoginSIDs;

/* Output mismatches */
SELECT * FROM #LoginSIDs WHERE SQLSDDLSID <> WinSDDLSID;



test

25 January 2015

Ermahgerd It's Ermaha!

We decided to take a rather spontaneous trip up to Beautiful Omaha, Nebraska. I capitalize Beautiful to denote it is more a term of respect or courtesy than my own personal observation. Still, I hail from Kansas City which is not exactly...anyway, I'll let that line of thought die on the vine and move on to our trip. We came up as a mini-vacation for Debra's birthday. Saturday we went to the Omaha Zoo.


It's a neat zoo, and the geodesic dome for the desert exhibit is impressive.


A wallaby gazing down at us.


Some sort of pig variant, not the most sweet smelling chaps.


Pete is a good sport for the camera...as long as you immediately let him look at the picture on your phone afterward. I guess that's what made the Polaroid corporation all their money.


Spider crabs in the aquarium. Tasty perhaps, but a bit on the creepy side.


Turtle swimming over us in the aquarium tunnel...sharks, rays, tuna (I think), it was quite impressive.


Not the biggest sharks around, but it was nice to see them.


Gretchen watching the rays.


There were several different jellfish tanks, this was some variety of sea nettle.


Another ray happily showing the limitations of your average point and click iPhone photographer.


Gretchen encountering the octopus.


We had a light lunch (the receipt was most decidedly not light) at one of the basic hot dog/hamburger grill joints that seem identical at all zoos. Gretchen wanted to sit on these sea turtles but Random Other Kid seemingly was ready to do battle over it.


Then we headed over to the cats and monkeys area. Some howler monkeys...well, not howler monkeys but something profoundly loud, were bellowing, and Debra assumed they were amplified by a PA...not the case. This one was a beautiful cat.


Jaguar, or as the English call them, Jagg-ee-yarr.


This snow leopard looked irritatingly over at me with a "do you mind?" scowl, then I realized he or she was taking a leak.


Gretchen super excited to see the white tiger.


From another angle, our tiger friend decided to get up and stretch...we thought we were in for a show, and we were, in a sort of way...he laid rather a large present for the zookeepers, and then immediately started licking himself down...there...in classic cat fashion. I refrain from judging, as humans (well...Norwegians, anyway) do have lutefisk.


Malayan Sun bear climbing about.


Ahh, a Salmonella Petting Zoo! Lick your hands clean kids!


I tried challenging this black swan to a fistfight but backed off when he whipped out a flickknife.


Good ol' American black bear. The bear I'm most likely to run into in a social situation in the future, if I hunt out west.


Andean spectacled bear relaxing a bit.


Then we headed back to the hotel. We stopped at the gift shop for a promised inexpensive toy. Pete found his "Star Wars" (not really) binoculars straight away, he's a simple chap and not prone to shopping indecision. He wanted to reenact the Tatooine "Luke searches for R2D2 with binoculars" scene I think. Gretchen on the other hand, wow, getting her to pick something under $10 was quite the event. I eventually started working the sales angle on various stuffed animals and I'm proud to say I closed the sale on a small Bush Baby toy.

One thing I noted at the zoo...the ubiquitous screaming child. Now, I'm not judging, I'm a parent and my kids have never been immune to this kind of spectacular behavioral performance. But compared to the Kansas City zoo, there was definitely an undercurrent of tragedy among the younger set. In the gift shop particularly, the sharp, tired barkings of parents were contrasted against more melting down than in a Zurich fondue restaurant. I guess the largeness of the zoo tends to have kids getting tired out by the end of the day.

Thence to Trader Joe's for some light snacks for dinner, and the hotel. We found out we happened to be right next door to a Cabela's, and I needed socks since we forgot to pack extra, so over to Cabela's, then!


You have to love taxidermy to appreciate this place.


So back to the hotel, where Debra and I enjoyed a bit of Spatlese and now are ushering the kids to bed. Tomorrow, we go to the Strategic Air Command Museum, and from there, to home.

Ahh, but after a night of the sort of sweet repose calculated to dissuade road-tripping parents from future more ambitious plans, we hoisted ourselves groggily up, and began the sport we like to call "keep the children reasonably quiet until it's time to check out". We drove southwest and after 20 minutes or so, arrived at our destination. It was quite cold out, and the family quickly left me in the dust, disappearing here behind the massive Titan, a staggering symbol of the Cold War.


The SR-71 is one of those essential photo ops.


The main hangar hasn't changed much. That's a U-2 overhead.


There was a partially exposed fuselage of a B-25 that we could walk along side, seeing the interior compartments. This was the cockpit.


The B-36 is just enormous, makes a B-52 seem kind of small and svelte in comparison. Six propeller engines, and four turbojets.


Unsurprisingly the kids like the more hands-on exhibits. We stayed over here rather a lot.


But eventually I coaxed them out to see some more aircraft. The B-17, long a favourite and my grandfather's habitat during the brief unpleasantness in Europe during his youth.


Camera got a bit foggy but the kids thought this little parasite fighter (XF-85 Goblin) was rather cute.


But simulators, and anything you can climb about on, were their favorites.


Ahh. Yes. You see that's what's called a reentry vehicle. Err...yes. Bit awkward, this. Well, you see, it was designed to reenter, well, over Russia. In rather not a very pleasant sort of time.


Well, while my daughter begins to ponder the deeper questions of the morality of war, my son seems cool with it and enjoys the interactive Minuteman silo diorama.


Ahh, the B-29. The plane that ended WWII in a particularly controversial way. Not this one, of course, but one of its ilk.


Soft light bathes the ordnance in the 29's forward bomb bay.


An unmanned Apollo vehicle sits in the...hey is that...LUCAS FOR THE LAST TIME GET YOUR CRAP OUT OF MY MUSEUM!!!


Some whirlybirds in the second hangar.


4000lb bomb, amusingly titled "Light" in an ironic twist, perhaps like calling the huge bouncer at the bar "Tiny".


I gotta say, it may not have made as much tactical or strategic sense as the various camoflauge finishes but the stainless or chrome look of this MiG-21 is fetching.


Kids, of course, enjoying the interactive stuff the most.


Then back into the vehicle after an episode of grave indecision in the gift shop, and we headed southeast towards KC, arriving mid afternoon. Now, to fix dinner and prepare for a week of work. Was a fun weekend, and the birthday girl had a good time. Sort of a road-trip sampler, all the fun, tiredness, bickering, and mess of a real road trip but fits in a weekend!