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

Reflecting Back On The Year

Santa-Yoda is pleased with these goals

This year has absolutely flown by! My wife and I welcomed our first child into the world and he's been an absolute joy to raise thus far. Working as a Sr. DB Dev has also been fun & rewarding, so I guess that means home and work life have both been pretty great this year.
As the year winds down I thought I'd make a quick post outlining some of the goals I'm setting for next year both personally and professionally as I start working on them now. Also, I'm hoping that by publically declaring these goals it'll help keep me honest ;)

Personal Goals

Fitness
Without a doubt I need to drop some weight! It's really amazing not surprising at all how keeping moderately active doing absolutely nothing and eating healthy  like a pig can pack on the pounds. I'd like to be down 50lbs by this time next year and I don't think that's at all unreasonable.

Learning
Learning some basic ASL (American Sign Language) with my son is something I've already started. He doesn't have any hearing problems, but it's something I've read about and I can see no downside to doing it. Ultimately I'd like to also learn Mandarin with him, though I think it's best to get him started with English first.

Finishing What I Start
I'm my own worst enemy when it comes to projects. I'm interested in a very wide array of things and because of that I take on an awful lot of things ranging from car repair, to carpentry, to microelectronics. For my own sanity (and to better manage my time) I've got to start serializing projects and forcing myself to not start anything new until the one before it is done.

Career Goals

I'm Certifiable!
Loved by some, hated by others I've decided to pursue Microsoft Certification again. I'll target MCSE: Data Platform first, and if that goes well I'll probably get the Windows 2012 admin cert as well. I would love to eventually become a Certified Master for SQL Server, but that's a pretty lofty goal... One step at a time.

Documentation
I think anyone in the tech industry would probably agree that the first thing to get dropped when people and teams are swamped is documentation. I know I'm guilty of it and I have resolved to tackle the problem head-on; admitting you have a problem is the first step, right? ;)
I think that spending more time documenting architecture plans, current findings, observed pain points, and fleshing out policies & procedures will pay off pretty quickly and I plan to make time for it every day.

Sharing Is Caring
It bothers me greatly that I still haven't posted anything about MSX & TSX servers after reading my post from April saying the same. At one time I thought that I would like to shoot for being a SQL Server MVP (some tweets from Paul Randal actually jived with what I ultimately decided about that as a goal), but there's really no way I could be one as I just don't share enough. I'm going to make it a point to share more information through this blog, MS forums, LinkedIn groups, and (first and foremost) with my co-workers. I think it would be awesome if in 2013 I could stand up in front of the TorPASS SQL Server user group and present, but it's not a goal I'm going to set at this point; I'm quite happy to attend and learn from those that do present at this point.

Ok, that's it for now... Time to hit the gym and read some books!
-A

Catching Up

Wow, it's been a while. I still haven't done a bloody thing regarding presenting info on MSX and TSX servers (my TechNet account expired) and I've basically stopped blogging.
I'd love to say that I'll start again soon but that would probably be a lie. My wife is due with our first child tomorrow and I don't expect I'll have much time for blog posts in the near future.

I do however have some posts I'd like to share though. I've got some SQL Server 2008 R2 IO performance metrics with consumer/prosumer SSD storage that I'd like to share, and I'm nearing completion on an application for database query stress testing that I'll put up on CodePlex. I'm learning about tasks and threading as I go so it's a slow process, but it's fun.

Another thing about not blogging for a while... I HAVE NO IDEA WHAT I WAS THINKING WHEN I SETTLED ON THIS COLOUR SCHEME! Good lord it's ugly! I think I'll change this now...

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