Transfer SophosDB to remote server

Hi,

Typically in a workgroup mode it would be setup with:

• a dedicated account, e.g. 'SophosManagement' on both computers, i.e. the management server and database computer.  This would have the same password.
• This 'SophosManagement' user account, would be a member of a local Windows group called "Sophos DB Admins" on the database computer.
• The SophosManagement account doesn't need to be an admin account as it obtains the rights to access the stored procedures in the database by being a member of the Sophos DB Admins group.

Also.. I would suggest, checking that the collation of the instance to ensure both instances are the same.  The collation might be something like: "SQL_Latin1_General_CP1_CI_AS".

The command to run on both computers to check the collation would be:

sqlcmd -E -S (local)\sophos -Q "select SERVERPROPERTY('Collation')"

These should be the same.  If not, you should remove and re-create the new instance, with the same collation as the old instance.

After restoring the database over  the top of a database (in the case below SOPHOS47), you should run:

sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"

sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"

sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql

Where, SERVERNAME is the name of your database computer as "Sophos DB ADmins" is a local group.

The above commands are to ensure that the "SOPHOS DB Admins" group is mapped correctly and therefore gives a non admin user on the computer access to the SQL objects required.  Of course, if you run the SophosMangement account as an admin, this problem is not seen as typically members of Administrators are SQL admins but it's not good practice.

Regards,

Jak

Hi,

Typically in a workgroup mode it would be setup with:

• a dedicated account, e.g. 'SophosManagement' on both computers, i.e. the management server and database computer.  This would have the same password.
• This 'SophosManagement' user account, would be a member of a local Windows group called "Sophos DB Admins" on the database computer.
• The SophosManagement account doesn't need to be an admin account as it obtains the rights to access the stored procedures in the database by being a member of the Sophos DB Admins group.

Also.. I would suggest, checking that the collation of the instance to ensure both instances are the same.  The collation might be something like: "SQL_Latin1_General_CP1_CI_AS".

The command to run on both computers to check the collation would be:

sqlcmd -E -S (local)\sophos -Q "select SERVERPROPERTY('Collation')"

These should be the same.  If not, you should remove and re-create the new instance, with the same collation as the old instance.

After restoring the database over  the top of a database (in the case below SOPHOS47), you should run:

sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"

sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"

sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql

Where, SERVERNAME is the name of your database computer as "Sophos DB ADmins" is a local group.

The above commands are to ensure that the "SOPHOS DB Admins" group is mapped correctly and therefore gives a non admin user on the computer access to the SQL objects required.  Of course, if you run the SophosMangement account as an admin, this problem is not seen as typically members of Administrators are SQL admins but it's not good practice.

Regards,

Jak