How to Move the Symantec Critical System Protection (SCSP) 5,x MSDE Database to an MSSQL 2005 Database Server

Article:TECH116276  |  Created: 2009-01-29  |  Updated: 2012-05-10  |  Article URL http://www.symantec.com/docs/TECH116276
Article Type
Technical Solution

Product(s)

Languages

Problem



The SCSP MSDE version that comes with the product is 2000 SP4. How do you move the SCSP MSDE database to an MSSQL 2005 database server after testing?

Symptoms
n/a


 


Cause



n/a


Solution



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 the MSDE and the MSSQL database server are NOT the same base version (i.e., MSDE==2000sp4 and MSSQL production DB == MSSQL 2005).


1. Type "net stop sismanager" at the "run" line. Run the MSSQL script provided by Microsoft here to create a stored procedure to transfer logins. Copy the output to c:\output.txt (Method 2):


 

                    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_2000_to_2005') IS NOT NULL
                    DROP PROCEDURE sp_help_revlogin_2000_to_2005
                    GO
                    CREATE PROCEDURE sp_help_revlogin_2000_to_2005

                    @login_name sysname = NULL,
                    @include_db bit = 0,
                    @include_role bit = 0

                    AS
                    DECLARE @name sysname
                    DECLARE @xstatus int
                    DECLARE @binpwd varbinary (256)
                    DECLARE @dfltdb varchar (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 STATIC FOR
                    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
                    FROM master.dbo.sysxlogins
                    WHERE srvid IS NULL AND
                    [name] <> 'sa'
                    ELSE
                    DECLARE login_curs CURSOR FOR
                    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
                    FROM master.dbo.sysxlogins
                    WHERE srvid IS NULL AND
                    [name] = @login_name

                    OPEN login_curs

                    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

                    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 ''
                    PRINT ''
                    PRINT '/***** CREATE LOGINS *****/'

                    WHILE @@fetch_status = 0
                    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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
                    PRINT @tmpstr
                    SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
                    PRINT @tmpstr
                    END
                    END
                    ELSE
                    BEGIN -- SQL Server authentication
                    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

                    IF (@binpwd IS NOT NULL)
                    BEGIN -- Non-null password
                    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
                    SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
                    END
                    ELSE
                    BEGIN -- Null password
                    SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
                    END

                    SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
                    PRINT @tmpstr
                    END

                    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
                    END

                    IF @include_db = 1
                    BEGIN
                    PRINT ''
                    PRINT ''
                    PRINT ''
                    PRINT '/***** SET DEFAULT DATABASES *****/'

                    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

                    WHILE @@fetch_status = 0
                    BEGIN
                    PRINT ''
                    SET @tmpstr = '-- Login: ' + @name
                    PRINT @tmpstr

                    SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
                    PRINT @tmpstr

                    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
                    END
                    END

                    IF @include_role = 1
                    BEGIN
                    PRINT ''
                    PRINT ''
                    PRINT ''
                    PRINT '/***** SET SERVER ROLES *****/'

                    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

                    WHILE @@fetch_status = 0
                    BEGIN
                    PRINT ''
                    SET @tmpstr = '-- Login: ' + @name
                    PRINT @tmpstr

                    IF @xstatus &16 = 16 -- sysadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &32 = 32 -- securityadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &64 = 64 -- serveradmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &128 = 128 -- setupadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &256 = 256 --processadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &512 = 512 -- diskadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &1024 = 1024 -- dbcreator
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
                    PRINT @tmpstr
                    END

                    IF @xstatus &4096 = 4096 -- bulkadmin
                    BEGIN
                    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
                    PRINT @tmpstr
                    END

                    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
                    END
                    END

                    CLOSE login_curs
                    DEALLOCATE login_curs
                    RETURN 0
                    GO

                    exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1



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:\program files\symantec\critical system protection\server\MSSQL$SCSP\Data\SCSPDBprimary.mdf
c:\program files\symantec\critical system protection\server\MSSQL$SCSP\Data\SCSPDBlog.ldf
c:\output.txt

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


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 "" and "".

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

11. Open SQL Server Management Studio and connect to the SCSP database instance. Expand "Databases" and right-click SCSPDB. Click on "Tasks", then "Detach".

12. Rename the following file on the new management server host to .old and replace it with the appropriate file from the old management server, then revisit step 11, but instead of selecting "Detach", now select "Attach" so that the old database can be attached:
[MSSQL data root]\SCSPDBprimary.mdf

13. In the SQL SErver Management Studio expand "Security", then "Logins" and delete "scsp_ops", scsp_plugin" and "scspdba".

14. In the SQL Server Management Studio expand "System Databases", then click on "master". Now, in the toolbar, click on the "Database Engine Query" icon next to "New Query". Paste in the text from output.txt copied from the old manager. Please note that during the copy process a carriage return was added in the middle of the password hash for each of the logins. When you copy the text into the query you must remove this carriage return to make each hash a single string again or the query will fail.

15. After the query has run successfully, 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 successful login to the SCSP console.

Legacy ID



2009092905472953


Article URL http://www.symantec.com/docs/TECH116276


Terms of use for this information are found in Legal Notices

Please Sign In

Login using SymAccount.

Knowledge Base Search

Knowledge Base Search

Rate this Article

Help us improve your support experience.

MySymantec

MySymantec

Contacting Support

Contacting Support