Wednesday, January 23, 2013

Resolving "Key not valid for use in specified state" Error When Importing Registered Servers In SSMS

I love SQL Server Management Studio (SSMS). Is it the best tool out there? Debatable. But when you add SSMS Tools Pack and SQL Sentry Plan Explorer it's pretty hard to beat IMO. It's also usually pretty stable and reliable for me and since I spend most of my day with it open I can't complain. ...but then I re-imaged my work box.
It was time to upgrade to the latest OS and Visual Studio editions at work so why not start over clean, right? Right.
After backing up everything I needed I was off and running and re-loading all my beloved tools when it came time to import my old registered servers list. I thought I was clever; I backed it up "with login and password info" to make life easier. All was well until I imported the file and got the dreaded error message: "Key not valid for use in specified state"
Oh. No.

Of course, the servers then showed up but on closing and re-opening SSMS (2008 R2) my registered server list was blank. CRAP!

Luckily I knew where this info was stored and I navigated to %appdata%\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml
Upon opening the file in Notepad++ I jumped found the servers that had been setup with SQL Logins and started modifying...

  1. I removed the encrypted password and uid=xxx bits from the "ConnectionStringWithEncryptedPassword" element.
  2. I added "trusted_connection=true;" to the connection string modified in step 1
  3. I changed "PersistLoginNameAndPassword" to "PersistLoginName" in the "CredentialPersistenceType" element.
  4. I saved the file and crossed my fingers...
Success! I opened SSMS and had no error. My registered server list was intact and all was right with the world. Of course, the login info for a few servers will need to be corrected, but that's much less work than re-registering a pile of servers.

I hope this info will help others in the same boat :)
-A


Friday, December 2, 2011

SQL Permissions - The Case of the "Token-based server access violation"

I managed to create and then fix my own PEBKAC issue today and I thought I'd share the resolution in case it helps someone else out there Google-ing/Bing-ing for the solution.

The problem is failed logins and in your SQL error log you'll see entries like this:
Login failed for user 'DOMAIN\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 11.

You might see these sorts of errors when you've got domain problems or kerberos issues, but that's not the case here. You can eliminate those issues if others can log in or if the broken user/users work when you grant them sysadmin server role membership.
Since granting anyone sysadmin (even on a dev box) just isn't acceptable in my world I had to dig deeper. It turns out that the real problem was that at some point I had denied CONNECT SQL to the DOMAIN\Domain Users group. DOH!
Probably not a common thing to do but this problem would manifest itself if you had done the same thing on other groups. Maybe you have a group of accountants you don't want poking around in the db, or a group of nosy developers and you revoke CONNECT to their AD group. Then the exception arises and someone needs access but they just can't connect despite you granting them permission. Look for that deny setting and you'll be all set.

Cheers!

-A

Sunday, November 20, 2011

A little C++ Never Hurt Anybody

As a good friend of mine would agree to, C and I had have a rocky past. I'm still not sure I like C/C++ and all it's nasty pointers and buffers, but I absolutely do respect it. I just find it convoluted and annoying to read personally and love how easy C# makes things. I'll take C# over C++ any day of the week and twice on Sundays... :p

Anyway, there comes a time in every tech geeks life when they go to use a FOSS product and realize it doesn't do quite what they need. This recently happened to me with a very cool media centre project - OpenELEC.
It's a really, really quick booting stripped down Linux based XBMC setup that's optimized for different platforms (like x64 Atom CPU and Nvidia ION graphics). The whole install is ~90MB and it'll run of a MicroSD card, USB, stick, etc. I'm very impressed with it and recommend anyone who likes XBMC check it out. I digress.

So OpenELEC is great and all, but I'm still a huge fan of using an Xbox 360 wireless remote to control things and this is where it fell down. The Linux xpad kernel driver isn't the best, and the SDL joystick code in the XBMC version that ships with OpenELEC is straight up broken when it comes to the 360 wireless controller.
This is where my old friend C and I met again. In digging through event captures, debug logs, and source code I was able to find the code that was causing my problem. Then I got rough. I cut out blocks of code that weren't called, I stubbed out procedures that weren't needed, and I hack-hack-hacked my through some changes to produce a file that not only compiled, it worked!

All in all it was a great learning experience both for working with Linux systems as well as C++ and I can honestly say I enjoyed it. And because I fixed this myself the victory is that much sweeter.
If anyone is interested I shared the changes here on the OpenELEC forums. Now excuse me, I've got some stunning HD content to watch on my now fully functional Asus 1012p EeeBox media centre. ;)

-A

Sunday, October 16, 2011

Thoughts On SQL PASS 2011

This year I attended SQL PASS in Seattle, Washington for the first time. The experience exceeded my expectations, and my expectations were high. If you know me at all you know that I'm typically guilty of holding exceedingly high expectations from anything training related so saying that the conference exceeded my expectations really is something.

The quality of the presentations given were all very high and the knowledge demonstrated by the speakers was beyond anything I've seen before. Without a doubt SQL PASS is where you go to meet the experts. I learned so much in-depth knowledge about advanced internals like workspace memory, IO, and residuals & predicates that the time and money it took to attend will be made back in the first week that I'm back at work. I thank everyone who gave their time and talent to SQL PASS as there's no doubt it takes a huge amount of effort to pull off something that great.

Now, I'm not saying everything was perfect. The big brother/sister program was a failure from my perspective. I never met either my big brother or anyone in my "first timer" group. I was where I was supposed to be, but so were about a thousand other people and there were rooms big enough to hold maybe half of that in a usable manner. I have no idea if my big brother even showed up - according to the group leaders I found he wasn't there. I can't say I was upset about it, but if I've got any feedback to offer for areas of improvement I think that would be it - fix that whole program or just kill it off. The first timer networking session with Don Gabor was well run though, and certainly valuable.

Overall it was a fantastic week in Seattle. I met some new people, reconnected with some others, learned a lot, and for the first time ever; felt a sense of community. I think it's the sense of community which has really left me feeling motivated. I want that feeling to continue and thus I've committed to posting up a technical series!
I've decided that not a lot of people know about the magic that is MSX - TSX SQL Agent setups and thus I'll be blogging a lot about that in the near future. It's my way of saying thanks and giving back to the SQL Server community that has given so much to me.