How to Move a Symantec Critical System Protection (SCSP) 5.2 Manager and MSDE Database to a Production Database and Server.

Products

• Critical System Protection

Issue/Introduction

What are the steps for moving an SCSP Manager and MSDE Database to a Production Database and Server?

Symptoms
The free MSDE 2000 database that installs with the demo version of SCSP has a 2gb limit and is meant for demonstration purposes, so it is preferable to move the scspdb database to a production server when the SCSP product has been purchased and settings need to be retained. While there are multiple ways of doing this, below is one way I have found to move both the MSDE database and the management server while retaining settings and agent connectivity when both the MSDE and the MSSQL database server are the same base version (i.e., MSSQL 2000, or MSSQL 20005).

1. Backup the MSDE database using OSQL:
Log into the MSDE instance: osql -S 127.0.0.1\SCSP -U sa -P [sa password]
Type at the following prompt 1> use master
1> go
1> backup database scspdb to disk = "c:\scspdb.dat"
1> go

2. Copy the following files to the new management server host:
c:\program files\symantec\critical system protection\server\agent-cert.ssl
c:\program files\symantec\critical system protection\server\server-cert.ssl
c:\program files\symantec\critical system protection\server\ui-cert.ssl
c:\program files\symantec\critical system protection\server\tomcat\conf\server.xml
c:\scspdb.dat


3. Run MSSQL script provided by Microsoft here to create stored procedure to transfer logins and copy the output to a .txt file:


USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
EXEC sp_help_revlogin
GO
----- End Script -----

4. Install the SCSP 5.2 manager to the new production server pointing to the new MSSQL "SCSP" database instance located on the production database server.

5. When install is finished, verify login is successful.

6. Type "net stop sismanager" at the "run" line.

7. Rename the following files on the new management server host to .old and replace them with the appropriate files from the old management server:
c:\program files\symantec\critical system protection\server\agent-cert.ssl
c:\program files\symantec\critical system protection\server\server-cert.ssl
c:\program files\symantec\critical system protection\server\ui-cert.ssl
c:\program files\symantec\critical system protection\server\tomcat\conf\server.xml

8. Type "net start sismanager" at the "run" line.

9. Open the console, login, and accept the certificate. This verifies that communication with the old database is successful. If login fails, verify the certs were copied over properly and that the ip address for the database server in the old server.xml file is correct. If moving the MSDE, the server.xml file will list the ip address of the database server as 127.0.0.1 (loopback). If this isn't changed to the actual ip address of the old server, then SCSP will be looking locally for the database and the connection details haven't been changed yet. This will cause a connection failure. Here are the two entries that must be changed. They follow the entries [name="Database=Console"] and [name="Database-Agent"].

10. Type "net stop sismanager" at the "run" line.

11. Open the MSSQL 2000 Enterprise Manager on the new server and perform a restore from the file scspdb.dat that was copied from the old SCSP manager.
a. Right-click SCSPDB and choose "All Tasks -> Restore Database..."
b. Restore as database "SCSPDB"
c. Restore: "From Device"
d. Click the "Select Devices" button, make sure "Disk" is selected and click "Add"
e. Check "File name" and click the "..." button for browse to select the file "scspdb.dat"
f. Click "Ok", then "Ok"
g. Make sure "Databse - complete" is checked, then click "Ok". This will begin the recovery proper.
h. After the recovery is complete, restart the scspdb database.

12. Expand the SCSP database instance in Enterprise Manager, then expand "Security". Click on "Logins" and delete "scsp_ops" and "scsp_plugin".

13. Open Query Analyzer and paste the output of sp_help_revlogin, then run it to recover logins

14. Restart database server, then open enterprise manager and expand "Security" and "Logins", right-click on "scspdba", select "properties". Click on the "Database Access" tab and verify that "SCSPDB" is checked. If it's not, check it and click "OK"

15. Restart the SCSP database instance.

16. Shut down the old SCSP management server host. Then, on the new management server, right-click windows "Start" and go to "Explore". Scroll down and right-click "My Network Places" and click "Properties". Right-click "Local Area Connection" and select "Properties". Scroll down and select "Internet Protocol (TCP/IP)" and click the "Properties" button. Now change the IP address to the same as the old manager. If using Active Directory, make the appropriate changes there and remove the old manager entry.

17. Type "net start sismanager" at the "run" line.

18. Verify that the console login is successful.

Cause

n/a

Resolution