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
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 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
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 531
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
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 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-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 1668
Cheers tpalmer. Anoter SSMS oddity where technically SSMS is correct but still not working as expected.
Toni-256719
Toni-256719
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4163 Visits: 1052
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 Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4163 Visits: 1052
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