February 24, 2010 at 1:32 pm
Every Microsoft DBA either has to build their support workstations from time-to-time, or perhaps help a new member of a team setup SSMS to support various production servers. In an enterprise environment, being able to migrate over Alias server and registered server lists can save a lot of time with setup. I am surprised at the number of entries on the web that can't understand the need to do this. We manage hundreds of SQL Server instances and alias servers as part of our enterprise management. During a rebuild, I found this issue and I hope to help others with this issue in the future, and save us all some time to do the important stuff.
All of this information was collected from various sources on the Internet, but I want to compile it in one place.
The alias servers can be imported to a new machine running SSRS2008 (2005) by exporting HKLM\SOFTWARE\MICROSOFT\MSSQLServer\Client\ConnectTo and importing it on the new machine running SSRS. As with everything from Microsoft, they do not recommend this, but it does work. Make sure you backup these entries prior to running them on the new machine in case you have to rollback.
The other issue appears straight forward, but exporting registered servers does have some issues.
A basic import of the registered servers (and groups) from workstation 1 was performed by opening SSMS and right clicking 'Local Server Groups' --> Tasks --> Export. During this selecting the 'Do not include user names and passwords in the export file' option is selected by default. This REGSERV file can be exported, moved to the second workstation, and Imported in the same way. If you decide to unselect the 'Do not include user names and passwords in the export file' option, you may have issues when importing these entries or when opening SSMS the next time with either of the following errors:
IMPORT ERROR:
The operation 'Import' failed. (Microsoft.SqlServer.management.RegisteredServers)
Key not valid for use in specified state. (System.Security)
The issue at hand is that data encrypted on one machine can't be decrypted on the other. The "Do not include user names or passwords" omits the encrypted data so the file can be opened on another machine. If you have a list of say 10 registered servers with 9 using Windows Authentication but one using SQL authentication, the import will bail on the entry with the SQL authentication and throw the above error. if you try to open the server it bailed on or if you close and reopen SSMS, the following error will occur. I also saw this is you click on the Registered Server view in some cases.
Microsoft .NET Framework
Unhandled exception has occurred in a component in your application. If you Continue, the application will ignore this error and attempt to continue.
Key not valid for use in specified state.
If you select the Continue button, you will not be able to open the registered Server view.
To fix this:
The Registered Server entries are stored in the regSrvr.xml file found in the Document and Settings\<username>\Application data\Microsoft\Microsoft SQL Server\100\Tools\Shell directory. Every time registered Servers view is opened, the RegSrvr.xml is checked. If it does not exist, the view will create a new regSrvr.xml, without any entries of course. You can fix this by closing SSMS, deleting (or renaming) the regSrvr.xml file, and reopening SSMS. A new regSrvr.xml will be created.
You can go back to the original server and perform the export ensuring the 'Do not include user names and passwords in the export file' option is selected and import it on the other workstation you wish to duplicate the registered Server list to in SSMS. Windows authenticated connections will have to be entered manually during your first connection to them.
I hope this helps save you some time setting up SSMS and possibly researching potential issues.
Thanks,
Tom Palmer
May 19, 2010 at 5:03 pm
I just want to be sure I am getting this correct, there is no way to export a local group with user names and passwords included (they are servers in a different domain with no trust so we have to use sql authentication on them) and import it on a different machine? We were trying to get all of our servers registered quickly by having the primary db create a local group for their particular servers and then let the group import it to speed up the process.
May 20, 2010 at 8:23 am
If there is, I am not aware of it nor could I find anything on it. We connect to over 75 production servers on various domains, but they all trust each other so Win Auth works fine for us. Ther are a couple in non-production environments that we use SQL logins. On these servers, we were forced to manually enter our login information at first connect.
August 13, 2010 at 10:49 am
I'm having the same issue, I exported from SQL 2005 and also tried from a side by side 2005/2008 import and each time I tried to import the registered servers into 2008 I got errors. I started importing each group until I got all in, then when I hit 'refresh' it gave me the 'key not valid for use in specified state' error and now I cannot see any registered servers.
I tried the deleting of the 'shell' folder under app data, but that didn't work either.
August 13, 2010 at 10:53 am
ok so I just figured out my issue...
I found 2 'Shell' folders...
\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools
\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell
I had to remove both before it reset itself.
September 18, 2012 at 4:21 am
Cheers tpalmer. Anoter SSMS oddity where technically SSMS is correct but still not working as expected.
January 27, 2014 at 6:39 am
Deleting the RegSrvr.xml file didn't solve my issue. I'm still not able to import the registered servers from one computer to another.
It has worked in the past but just not to this one laptop.
Each time I try importing, I get the error of:
Key not valid for use in specified state. (System.Security)
Does anyone have any other suggestions?
February 4, 2014 at 10:08 am
Toni-256719, here's a "crazy" "other suggestion":
Open up both SSMS's side-by-side and manually create the reg'd servers in 2008 (or 2012) that are in 2005.
That's what I had to do today.
February 4, 2014 at 10:28 am
Gary, that's exactly what I have been doing.
There are a lot of servers to do that with but it was my only option.
Thanks for the suggestion, though.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply