SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSMS 2008 Export/Import of Aliases and Registered Servers can fail


SSMS 2008 Export/Import of Aliases and Registered Servers can fail

Author
Message
tpalmer
tpalmer
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 243
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
Laura Grob
Laura Grob
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 Visits: 479
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.
tpalmer
tpalmer
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 243
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.
Jeff Taylor-144547
Jeff Taylor-144547
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 220
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.
Jeff Taylor-144547
Jeff Taylor-144547
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 220
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.
FairFunk
FairFunk
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1650
Cheers tpalmer. Anoter SSMS oddity where technically SSMS is correct but still not working as expected.
Toni-256719
Toni-256719
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3828 Visits: 999
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?
Gary Noter
Gary Noter
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 75
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.
Toni-256719
Toni-256719
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3828 Visits: 999
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search