Transfer SophosDB to remote server

Hi,

The fact that you could see the SOPHOS47 database when you created a UDL test file (I assume this UDL test file looks similar to the connection string registry key the management service uses) to test connectivity to the SQL database from the management server the error would suggest permissions problems of the user SophosManagement.

That UDL test was run as you, where as the management service is trying to connect as the SophosManagement account.

A good test now would be... on the SQL server create a command prompt running as SophosManagement user.
E.g in a command prompt type:
runas /user:[domain]\SophosManagement cmd
In this new CMD window running as SophosManagement , then execute a SQL command against the SQL server.

SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrapDataGet"

This should return some XML rather than an error. If it errors, what is shown?


Note: Ideally you would run this SQLCMD test on the management server to connect to the remote SQL instance but as the management server probably doesn't have sqlcmd, testing permssions on the SQL server is ok.


Otherwise:

Can you:
1. Check that "SophosManagement" user can log onto the SQL server (The above test does this)

2. DROP the Sophos DB Admins Login. To do so, on the SQL Server run:
sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"

Where:
SERVERNAME should be replaced with the NetBIOS form of the computer name of the SQL server.
I assume the SQL instance is called SOPHOS.

3. Re-create the Login. On the SQL Server run:
sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"

Where:
SERVERNAME should be replaced with the NetBIOS form of the computer name of the SQL server.
I assume the SQL instance is called SOPHOS.

4. On the SQL server run:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql

Where:
I assume the SQL instance is called SOPHOS.
the SQL file can be obtained from here: http://sophserv.sophos.com/repo_kb/111898/file/ResetUserMappings.sql.txt

5. Ensure that "SophosManagement " is a member of the "Sophos DB Admins" group on the SQL Server.

6. Start the management service.

Regards,
Jak


Note:
As for the errors with running SQLcmd, sqlcmd -E -S .\sophos -Q "SOPHOS47" is wrong it should be:
sqlcmd -E -S .\sophos -Q "select name from sysdatabases"
But as the UDL test from the management server to the SQL server listed the database it's a bit mute.

HI Jak,

I appreciate your help a lot. i apologize for my lack of skillset on this.

SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrapDataGet" - below is the result of the xml.

<bootstrapData xmlns="http://www.sophos.com/xml/msys/bootstrapdata.xsd"><bootstr
apCommand publisher="22d805f0-8824-4f06-8466-7630e4cd62dc" subscriptionGroupId="
F6F67B32-370C-405A-8396-7EDED89EA47F" siteId="DEFAULT" address="\\SOPHOSSEC\Soph
osUpdate" addres

tried as well running this command to test if i can login to SQL server:

- got result below:

runas /user:[domain]\SophosManagement cmd 

C:\>runas /user:[DELL\SophosManagement] cmd
Enter the password for [DELL\SophosManagement]:
RUNAS ERROR: Unable to acquire user password

May i ask why im getting this error though i provided correct password? cant still start management service. :(

thanks

HI,

The command would be:

runas /user:DELL\SophosManagement cmd

Hopefully the sqlcmd command running under that user context (in the launched command prompt window) will give us a clue.

Would you also provide the connection string from this registry  location on the management server:

HKEY_LOCAL_MACHINE\SOFTWARE\[Wow6432Node]\Sophos\EE\Management Tools\DatabaseConnectionMS

What does that value have in it?

Regards,

Jak

Hi Jak,

Heres what i got from unas /user:DELL\SophosManagement cmd

C:\>runas /user:dell\SophosManagement cmd
Enter the password for dell\SophosManagement:
Attempting to start cmd as user "dell\SophosManagement" ...
RUNAS ERROR: Unable to run - cmd
1385: Logon failure: the user has not been granted the requested logon type at t
his computer.

heres the connection string from the management server:

Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=SOPHOS47;Data Source=Sqlserver\SOPHOS;

HI,

From that (if that was run on the SQL machine) I understand that the account SophosManagement must be a domain account,....because if the connection string is correct the name of the SQL server is SQLSERVER?  IS this true?

Then based on the command: runas /user:dell\SophosManagement cmd

If you're running that on SQLSERVER, DELL can't be a computer name and must be the domain name?

Is that all correct?

As a quick test, if you make the SophosManagement account an admin, does the same command then work?

Also with this being set, does the management service start?

Regards,

Jak

Hi Jak,

i apologize for the confusion.the error result of the cmd is the result of the command initiated from Management server.

tested to execute cmd command in the SQL server and successfuly able to run cmd as SophosManagement account.

btw, i have a question, what SophosManagement account should i use on the reinstalling the management server on access the SQL database on the the SQL server? should i use the SophosManagement account on the management server or the Sophosmanagement account on the SQL Server?

both Management Server and SQL server are in the same domain.

Thanks

Ahh,

"1385: Logon failure: the user has not been granted the requested logon type at this computer."

makes more sense now, I assume that SEC is on a DC, as SophosManagement is just a domain user it doesn't have rights by default to logon to a DC as you have to be an admin.

On the SQL server (SQLSERVER), run:

runas /user:dell\SophosManagement cmd

In the command prompt that is launched as dell\sophosmanagement run:

SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrapDataGet"

If the dell\sophosmanagement account has the correct rights to the SOPHOS47 database you should get:

"<bootstrapData xmlns="http://www.sophos.com/xml/msys/bootstrapdata.xsd"><bootstr......"

If it fails, what is the error?

Regards,

Jak

Hi,

heres what i got from SQL server cmd command: it fails

C:\>SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrapDataGet"
Msg 4060, Level 11, State 1, Server SQLSERVER\SOPHOS, Line 1
Cannot open database "SOPHOS47" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server SQLSERVER\SOPHOS, Line 1
Login failed for user 'DELL\SophosManagement'.

thanks for the help

Aha, that's the problem, well it's a problem, I can't say it's the only one at this point.

Can you check that

1. On the computer: "SQLSERVER", there is a Windows group called "Sophos DB Admins"?
2. The user "DELL\SophosManagement" is a member of the group.

If the group didn't exist, you can create it and add 'DELL\SophosManagement" to it.

Then on "SQLSERVER" run the 3 commands::

sqlcmd -E -S .\SOPHOS -Q "DROP LOGIN [SQLSERVER\Sophos DB Admins]"

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

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

Note: For the last command, the SQL file can be obtained from here: http://sophserv.sophos.com/repo_kb/111898/file/ResetUserMappings.sql.txt

If you download that and rename the file to ResetUserMappings.sql and copy it into C:\ the first command will run.

Once this is done, can you attempt, again on "SQLSERVER":

runas /user:dell\SophosManagement cmd

In the command prompt that is launched as dell\sophosmanagement run:

SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrapDataGet"

Does that now work?

Regards,

Jak

Hi,

already double check SophosManagement account. member with Sophos DB Admins.

run again the command but still failed. cant understand why Login failed for SophosManagement account. :(

Thanks,