Andrews Tech Notes (and other random things): Resolving "Key not valid for use in specified state" Error When Importing Registered Servers In SSMS

Wednesday, 23 January 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...

I removed the encrypted password and uid=xxx bits from the "ConnectionStringWithEncryptedPassword" element.
I added "trusted_connection=true;" to the connection string modified in step 1
I changed "PersistLoginNameAndPassword" to "PersistLoginName" in the "CredentialPersistenceType" element.
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

9 comments:

Oleksandr Dubelewskyj said...: Good article, it helped me much.
Small trick:
to replace all uid/password data , You can user regex replacement in Notepad++: pattern to find is:
uid=dba;password="[^"]+"; 10 July 2013 at 03:11
Anonymous said...: It works!
Perfect. Thanks a lot!!; 24 July 2013 at 04:56
Anonymous said...: Or just delete the file :); 20 May 2014 at 07:10
Unknown said...: I also had to rename (effectively remove) the following folders to stop the registered servers from coming back:

C:\Users\David\Documents\Visual Studio 2015\Settings\SQL Server Management Studio OLD\OLDNewSettings.vssettin

C:\Users\David\AppData\Roaming\Microsoft\Microsoft SQL Server\130\Tools\ShellOLD\OLDRegSrvr.xml

C:\Users\David\AppData\Local\Microsoft\SQL Server Management Studio\13.0 OLD

C:\Users\David\Documents\USB\Documents\Documents\SQL Server Management Studio\Settings\SQL Server Management Studio OLD; 27 August 2016 at 16:24
Unknown said...: setup SSMS 16.3 - uninstall
remove directories
reinstall SSMS 16.3
ignore initial vssettings message. It will disappear next SSMS launch; 27 August 2016 at 16:26
Anonymous said...: Thanks heaps for this!

Just a note that I replaced steps 1 & 2 with just removing the encrypted passwords. That way I only have to reenter the passwords, not the usernames too.; 3 January 2017 at 00:30
Alfonso said...: A lot of thanks!!!; 16 February 2018 at 06:26
Joe Rall said...: Thank you so much for this post. I had some older entries that when imported on a new device were causing an issue. Once I fired up that XML and deleted the invalid entries, my SSMS instance fires up with no errors. Thank you!; 15 March 2018 at 13:44
Unknown said...: Thank you. All I needed to do is step 1 and 2. I've been storing the passwords in the description field. Not great, but it allows me to copy and paste it back into the empty password field after the import; 24 April 2019 at 13:27