CODEWITHSUNDEEP
ssmsssms-17
kneidels
kneidels

Reputation: 924

'Key not valid for use in specified state. (System.Security)' on registered server import (SSMS)

My computer recently crashed and I am getting things re-installed. I would like to import the list of registered servers I had on my previous PC. I have both the RegSrvr.xml file from G:\Users\<user>\AppData\Roaming\Microsoft\Microsoft SQL Server\140\Tools\Shell as well as backups (with username/passwords) of *.regsrvr files which I can move from my laptop.

I see that moving over the RegSrvr.xml file to the same location in the new PC does nothing at all.

And when I try to import the *.regsrvr files - I get an error 'Key not valid for use in specified state. (System.Security)' and only about half the list actually import.

Is there a way around this?

Upvotes: 16

Views: 18019

Answers (5)

A. Murray
A. Murray

Reputation: 2801

I eventually managed to get to the bottom of this very frustrating problem. The annoying thing is, I had 3 registered servers that were causing this error and the error doesn't tell you which ones are causing it. What's more, one of the servers causing the issue wasn't even one I could see listed in my registered servers!

To resolve... I initially worked through the list one by one and removed any that didn't connect (I could replace them again if I wanted them back).

Then I tried to export the registered server list I had, to see if I could just delete them all and then reimport without passwords, and that's when it told me exactly which one was causing the issue.

Name of registered server causing the issue

Unfortunately, it wasn't in the list. So I tried to work out where the registered server files were kept on disk, and found this SO post. I searched through the file for the problem server but didn't find it. I eventually did find it inside C:\Users\andrew.murray\AppData\Roaming\Microsoft\SQL Server Management Studio\RegSrvr16.xml - I removed any mention of the DB from there by deleting the containing XML elements within that file (one was a sfc:Reference element and the other a document element)

Now it finally starts up without me hitting OK on that message 3 times in a row!

Upvotes: 1

bobt
bobt

Reputation: 605

I like Granger's answer because it's cumbersome for me to re-export from the other computer.

But instead of swapping password like Granger did above, I just deleted them (and you can re-input password from SSMS if needed). To remove password easily, use Notepad++ and search for password=[^;]*;

Use Regular Expression search.

Replace with blank.

Upvotes: 8

James Burnett
James Burnett

Reputation: 171

I had a hard time migrating between two computers & was getting this error. Even after re-importing and deleting the xml file, I still got it. I ended up running the Import and Export Settings wizard & selecting "Reset all settings" which did work.

Upvotes: 0

Granger
Granger

Reputation: 4409

The issue is that the passwords are stored in an encrypted string in the file, and the encryption key used is specific to that machine where SSMS is installed. So when you export them then import on a new machine, you get that error because it can't decrypt/use the passwords.

Most people just re-export from the old machine without passwords, then after importing into the new SSMS on the new machine, they manually go fix all the passwords.

What I wanted to add was that since it's "just a text file", you can save a LOT of work by manually editing C:\Users\{user}\AppData\Roaming\Microsoft\SQL Server Management Studio\RegSrvr.xml.

What I did was import the file and ignore all of those errors

===================================

Key not valid for use in specified state. (System.Security)

------------------------------ Program Location:

at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope)
at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.ProtectData(String input, Boolean encrypt) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_SecureConnectionString() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ConnectionString() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ServerName() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.AddRegisteredServerNode(RegisteredServer regSrv, TreeNodeCollection nodes)

Then I went through this process:

  1. In SSMS, fix a "server".
  2. Over in the file, copy/paste that "good" encrypted password string over to all of the other "servers" that had a connection using the same password.
  3. Restart SSMS.
  4. Repeat as needed.

Upvotes: 8

James Evans
James Evans

Reputation: 121

I had this issue after importing my SSMS Server registration file WITH PASSWORDS from a different computer. To fix this, I re-exported the Server registration "without passwords" from my other computer and re-imported that file without passwords. This worked however, you can edit the registrations as you go.

Upvotes: 12

Related Questions