Export/Import SQL EM Registrations?

[hp...@nospam.nospam]

I have a development laptop I frequently need to reimage (ghost). I have about 25 SQL Servers registered in Enterprise Manager. Some use Windows Auth, some SQL Auth. Can I export the registration information so I don’t have to re-enter it every time I rebuild my laptop?

I have been able to export/import the server aliases using this key (this works fine): [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

I have also exported/imported: [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X]

But All I get are the groups, no servers.

TIA

[Narayana Vyas Kondreddi]

It is possible. You have to use DMO to save the registered servers to a
file, from the source machine. And then move this file to the target machine
and add the servers on the target machine by looping through the rows in the
file (again you will have to use DMO).

I hope this gives you a starting point. If not, post back and I'll try to
post some code.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

<hp...@nospam.nospam> wrote in message
news:2DFF37FF-E4D3-4FFC...@microsoft.com...

[Mingqing Cheng [MSFT]]

Hi TIA,

From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I understood
you? If there is anything I misunderstood, please feel free to let me know:)

Based on my scope, The method by exporting Register Files is not supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO

Here are the steps:

1. Create a vbs file and call it readreg.vbs which contains the following.
Please substitute [servername] with SQL Server name and [password] with the
SA password.

It also creates a table RegServers in pubs database so make sure that this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.

Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput

set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups

oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"

For Each oServerGroup in oApplication.ServerGroups

For Each oRegisteredServer in oServerGroup.RegisteredServers

MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"

oSQLServer.ExecuteImmediate MsgOutput
Next
Next

oSQLServer.Disconnect

msgbox "complete"

2. To run, open a command-prompt and run "cscript readreg.vbs".

3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:

use pubs
select * from RegServers

4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with the
sa
password.

Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput

set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups

oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")

For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)

If LoginOutput = "" Then
LoginOutput = " "
End if

Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer)
Next

Msgbox "complete"

5. To run, open a command-prompt and run "cscript createreg.vbs".

NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries in
the
following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X

Please test the method and I strongly recommand you doing this in a
development machine first:)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

[hp...@nospam.nospam]

Thanks for the scripts. I have changed them to JavaScript and am using XML to do the export/import. Is it possible to get the Server aliases using SQLDMO, or should I just read straight from the registry?

Thanks
-Kevin

[Mingqing Cheng [MSFT]]

Hi Kevin,

Does the script works fine with you? Based on my scope, I am afraid you
could not find Server Aliases by means of SQLDMO. I think reading directly
from Registry is fine.

BTW, It is highly appreciated if you could share us with your JavaScript
and XML files among Newsgroup Community. In this way, I believe, provided
multi ways for those who may encounter the same issue. Thanks in advance.

[hp...@nospam.nospam]

Thanks for your help! The scripts below are a hack, but they work fine for me.

These scripts can be used to dump/load SQL registration information so you don’t have to enter/re-enter it by hand (quite handy after you re-image your development workstation).

Dump_SQL_Reg.js
===============
Run from the command line to dump all SQL registration for the current machine and user to an XML file.
cscript /nologo Dump_SQL_Reg.js > servers.xml
NOTE: If there are any SQL servers using SQL auth, the passwords will be in clear text in the XML file.

Load_SQL_Reg.js
===============
Run from the command line to load SQL registrations from servers.xml (in same dir) for the currently logged in user.
cscript /nologo Load_SQL_Reg.js > load.log
Yes you can edit the servers.xml by hand and just use the loader script.

I whipped these up in a hurry, so I would not be surprised if there are some things it can’t deal with.
If you have any questions, let me know.

Sample of the XML file
======================

<?xml version='1.0' standalone='yes' ?>
<registrations>
<aliaslist>
<alias name='App1PRD_DB' data='DBMSSOCN,PRDDB01,1433'/>
<alias name='App2PRD_DB' data='DBMSSOCN, ,PRDDB01,1433'/>
<alias name='App1DEV_DB' data='DBMSSOCN,DEVDB01,1433'/>
<alias name='App2DEV_DB' data='DBMSSOCN, ,DEVDB01,1433'/>
</aliaslist>
<group name='App1'>
<reg server='App1_PRD_DB' login='' password='' trustedyn='1'/>
<reg server='App1_DEV_DB' login='' password='' trustedyn='1'/>
</group>
<group name='App2'>
<reg server=App2PRD_DB' login='XXXX' password='XXXX' trustedyn='0'/>
<reg server='App2DEV_DB' login='XXXX' password='XXXX' trustedyn='0'/>
</group>
</registrations>

//*************************************************//
// Dump_SQL_Reg.js
var oApplication = new ActiveXObject ("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups
var reReplaceXMLChars = new RegExp ("<|>|&|'","g");

var serverGroupEnum = new Enumerator(oServerGroups);
var xml = "<?xml version='1.0' standalone='yes' ?>\n<registrations>\n";

// Grab the alias list (if any)
var aliaslist = getSQLAliasXML();
xml += aliaslist;

for (;!serverGroupEnum.atEnd();serverGroupEnum.moveNext())
{
oServerGroup = serverGroupEnum.item();
xml += " <group name='" + oServerGroup.Name.replace(reReplaceXMLChars, XMLEncode) +"'>\n";

registeredServerEnum = new Enumerator(oServerGroup.RegisteredServers);

for (;!registeredServerEnum.atEnd();registeredServerEnum.moveNext())
{
oRegisteredServer = registeredServerEnum.item();
xml += " <reg server='" + oRegisteredServer.Name.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "login='" + oRegisteredServer.Login.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "password='" + oRegisteredServer.Password.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "trustedyn='" + oRegisteredServer.UseTrustedConnection + "'/>\n";
}
xml +=" </group>\n";
}
xml += "</registrations>";

WScript.Echo (xml);

/*
|| returns the alias on the current machine in an XML fragment
|| <aliaslist>
|| <alias name="my server alias" data="DBMSSOCN,xxxxxx,1433"/>
|| </aliaslist>
*/

function getSQLAliasXML()
{
var XMLFrag = ""
var hive = "HKLM";
var key = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\ConnectTo";
aAliasValues = HKLM_EnumValue(key);
if (aAliasValues != null)
{
var WshShell = WScript.CreateObject ("WScript.Shell");
var buffer = "";
XMLFrag += " <aliaslist>\n";
for (var x=0; x < aAliasValues.length; x++)
{
buffer = WshShell.RegRead (hive + "\\" + key + "\\" + aAliasValues[x]);
XMLFrag += " <alias name='" + aAliasValues[x] + "' data='" + buffer + "'/>\n";
}
XMLFrag += " </aliaslist>\n";
}

return (XMLFrag);
}

/*
|| Pass path to registry Key in HKLM
|| Returns an array with the values in the key
|| null if key is not found
||
*/

function HKLM_EnumValue (regPath)
{
HKLM = 0x80000002;
sRegPath = regPath;

try
{
oLoc = new ActiveXObject("WbemScripting.SWbemLocator");
oSvc = oLoc.ConnectServer(null, "root\\default");
oReg = oSvc.Get("StdRegProv");
oMethod = oReg.Methods_.Item("EnumValues");
oInParam = oMethod.InParameters.SpawnInstance_();
oInParam.hDefKey = HKLM;
oInParam.sSubKeyName = sRegPath;
oOutParam = oReg.ExecMethod_(oMethod.Name, oInParam);
if (oOutParam.sNames != null)
return (oOutParam.sNames.toArray());
else
return (null);
}
catch(err)
{
WScript.Echo("Error occurred\nCode: " + hex(err.number) +"; Description: " + err.description);
}
}

//User-defined function to format error codes.
//VBScript has a Hex() function but JScript does not.
function hex(nmb)
{
if (nmb > 0)
return nmb.toString(16);
else
return (nmb + 0x100000000).toString(16);
}

// Strip out special chars
function XMLEncode(str)
{
return (
(str == "<") ? "<" :
(str == ">") ? ">" :
(str == "&") ? "&amp;" :
(str == "'") ? "&apos;" :
(str == "\"") ? """ : str
);
}
//*************************************************//

//*************************************************//
// Load_SQL_Reg.js
// open the xml file

var fname = "servers.xml";

var xmlDoc = new ActiveXObject("Msxml2.DOMDocument");
xmlDoc.async = false;
xmlDoc.resolveExternals = false;
xmlDoc.load(fname);

if (xmlDoc.parseError.errorCode != 0)
{
var myErr = xmlDoc.parseError;
WScript.Echo("You have error " + myErr.reason);
}
else
{
// process each alias
var objNodeList = xmlDoc.getElementsByTagName("alias");
for (var i=0; i < objNodeList.length; i++)
{
var alias = objNodeList.item(i).getAttribute("name");
var data = objNodeList.item(i).getAttribute("data");
addAlias (alias, data);
}

var oApplication = new ActiveXObject("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups;
var oRegisteredServers = oApplication.RegisteredServers;

var objNodeList = xmlDoc.getElementsByTagName("reg");

for (var i=0; i < objNodeList.length; i++)
{
// Test the group
var group = objNodeList.item(i).parentNode.getAttribute("name");
try
{
// does the group already exist?
var oServerGroup = oServerGroups(group);
}
catch (e)
{
// add it
WScript.Echo("Adding group: " + group);
var oGroup = new ActiveXObject("SQLDMO.ServerGroup");
oGroup.Name = group;
oServerGroups.Add(oGroup);
}
// Test the server
server = objNodeList.item(i).getAttribute("server");
try
{ // Does the server already exist in this group?
var oRegisteredServer = oServerGroups(group).RegisteredServers(server);

// If so delete it!
WScript.Echo ("Removing Server: " + server);
oServerGroups(group).RegisteredServers.Remove(server);
}
catch (e)
{
// does not exist, do nothing
}
try
{
// NOTE: this will fail if server is already registered in a different group
WScript.Echo ("Adding Server: " + server);
var oNewServer = new ActiveXObject("SQLDMO.RegisteredServer");
oNewServer.Name = server;
oNewServer.Login = objNodeList.item(i).getAttribute("login").replace(/^$/, " ");
oNewServer.Password = objNodeList.item(i).getAttribute("password");
oNewServer.UseTrustedConnection = parseInt (objNodeList.item(i).getAttribute("trustedyn"));
oServerGroups(group).RegisteredServers.Add(oNewServer);
}
catch (e)
{
// print error and continue
WScript.Echo ("***Error**: " + e.description);
}
}
}

function addAlias(alias, data)
{
var regKey = "HKLM\\SOFTWARE\\Microsoft\\MSSQLServer\\Client\\ConnectTo\\" + alias ;
// see if the key exists
var WshShell = WScript.CreateObject ("WScript.Shell");
try
{
var found = WshShell.RegRead (regKey);
}
catch (e)
{
//do nothing
}
if (found != null)
{
// delete the key before re-add
WshShell.RegDelete (regKey);
}
// add the key
WScript.Echo ("Adding alias for : " + regKey);
WshShell.RegWrite (regKey, data, "REG_SZ");
}

//*************************************************//