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 as 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\
\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.
Thomas Palmer
Also posted to: http://www.sqlservercentral.com/Forums/Topic872254-391-1.aspx
No comments:
Post a Comment