Scripting Enterprise Manager registration entries
-
Our company manages dozens of remote SQL Servers at client sites. Any time we bring up a new support workstation, or change a workstation name or domain, we have to key in the dozens of registration entries.
I have figured out that this data is stored in the registry at:
HKEY_CURRENT_USER/Software/Microsoft/Microsoft SQL Server/80/SQLEW/Registered Servers X
But Microsoft stores it as Binary data and it looks encrypted. Has anyone figured out how to script information into this?
Dave
-
I've been searching for a way to do that since the release of 7.0
I had given it up as a lost cause, so I am EXTREMELY interested in the responses you get.
-
Curious as well. If no one comes up with an answer, we'll regroup and try again!
Andy
-
Hey All,
I work for a large company also and this was the response we got from a Microsoft representative. Please try this out... the only thing is I have no way of including the capicom.dll file they refer to. If anyone needs that file, just respond back with your email address and I'll forward it on to you.
I am attaching sample vbscript files that will allow to copy out registrations from SQL Server Enterprise Manager, and re-register them on a new machine. I am including the usage instructions below:
step 1. search your machine for file capicom.dll (used for encrypt password). If no file is found, save the file at your sql server binn directory then go to DOS command prompt to register it by using the command: regsrv32 <folder name>/capicom.dll
step 2. sqlserver70.vbs is used to export the sql server registration information and sqlserver 2k.vbs is used to re-register the sql server back to enterprise manager based on the file generated by sqlserver70.vbs.
step 3. once sql server is registered, you should un-register the file capicom.dll (using command:: regsrv32 /u <folder name>/capicom.dll) and then delete the file capicom.dll.
NOTE: Both the .vbs scripts should work against both SQL Server 7.0 and SQL Server 2000. I strongly recommend that you test this on a test machine first. To get this script to meet your specific requirements, you will probably need to modify the script.
Edited by - rkozakow on 12/12/2002 6:25:07 PM
-
VBScript for SQL 2000:
Dim Application
Dim ServerGroupObject
Dim RegisteredServerObject
Dim txtfile, fil1 , ts
Dim ed
Set ed = CreateObject("CAPICOM.EncryptedData")
Set Application = CreateObject ("SQLDMO.Application")
set ServerGroups = Application.ServerGroups
Set ServerGroupObject = ServerGroups.Item("SQL Server Group")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fil1 = fso.GetFile("c:\testfile.txt")
Set ts = fil1.OpenAsTextStream(1)
Do While Not ts.AtEndOfStream
set RegisteredServerObject=CreateObject("SQLDMO.RegisteredServer")
LoginMode = ts.read(1)
if LoginMode =0 then
'MsgBox("SQL")
ts.read(1)
sname = ""
flag="start"
Do while (flag <> ",")
flag=ts.read(1)
if flag ="," then Exit Do
sname= sname & flag
loop
'msgbox sname
flag="start"
Do while (flag <> ",")
flag=ts.read(1)
if flag ="," then Exit Do
lname= lname & flag
loop
'msgbox lname
flag="start"
Do while (flag <> ",")
flag=ts.read(1)
if flag ="," then Exit Do
pname= pname & flag
loop
'msgbox pname
S = ts.ReadLine
if pname <>"" then
ed.SetSecret "secret"
ed.Decrypt (pname)
Text = ed.Content
else
Text = ""
end if
RegisteredServerObject.Name = sname
RegisteredServerObject.Login = lname
RegisteredServerObject.Password = Text
sname=""
pname=""
lname=""
end if
if LoginMode =1 then
'MsgBox("Trusted")
ts.read(1)
flag="start"
Do while (flag <> ",")
flag=ts.read(1)
if flag ="," then Exit Do
sname= sname & flag
loop
'msgbox sname
S = ts.ReadLine
RegisteredServerObject.Name = sname
RegisteredServerObject.UseTrustedConnection = 1
sname=""
end if
'Msgbox "Register SQL Server: " & RegisteredServerObject.Name
On Error Resume Next
ServerGroupObject.RegisteredServers.Add RegisteredServerObject
Loop
ts.Close
MsgBox("SQL Servers are registered!")
Edited by - rkozakow on 12/12/2002 6:25:22 PM
-
VBScript for SQL 7.0:
Dim Application
Dim ServerGroupObject
Dim RegisteredServerObject
Dim strTemp
Dim authentication
Dim servername
Dim login
Dim password
Dim fso, txtfile
Dim ed
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.createTextFile("c:\testfile.txt", True)
Set ed = CreateObject("CAPICOM.EncryptedData")
Set Application = CreateObject ("SQLDMO.Application")
set ServerGroups = Application.ServerGroups
Set ServerGroupObject = ServerGroups.Item("SQL Server Group")
For Each RegisteredServerObject In ServerGroupObject.RegisteredServers
strTemp = strTemp & "Server Name: " & RegisteredServerObject.Name & " "
strTemp = strTemp & "Server Password: " & RegisteredServerObject.Password & " "
strTemp = strTemp & "trusted connection: " & RegisteredServerObject.UseTrustedConnection & vbCrLf
servername = RegisteredServerObject.Name
authentication = RegisteredServerObject.UseTrustedConnection
login = RegisteredServerObject.login
password = RegisteredServerObject.password
if password <> "" then
ed.Content = password
ed.SetSecret "secret"
Text = ed.Encrypt
else Text = password
end if
txtfile.Writeline (authentication & "," & servername & "," _
& login & "," + Text) + ","
Next
txtfile.Close
MsgBox ("Registration information is scripted out!")
-
That is excellent.....I can't wait to test it out myself....
-
Agreed! This looks interesting.
Andy
-
Let me know if it works. I'm a chicken
Steve Jones
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply