Transfer SophosDB to remote server

If you add SophosManagement to the local administrators group on SQLSERVER, does the command work and the management service start?  It shouldn't need all those rights but it would at least get you running for the short term while we work out what's wrong.

Otherwise, the contents of C:\SQLLogins.txt after running

sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SQLSERVER\Sophos DB Admins'" > C:\SQLLogins.txt

would be interesting to see.

Regards,

Jak

this is what i got from the cmd command:

C:\>sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SOPHOSSEC\S
ophos DB Admins'"
LoginName SID
                  DefDBName DefLangName AUser ARemote
--------- ----------------------------------------------------------------------
----------------- --------- ----------- ----- -------

(0 rows affected)
LoginName DBName UserName UserOrAlias
--------- ------ -------- -----------

(0 rows affected)

Hi Jak,

just a few questions:

1. what SophosManagement account should i use when i modify the existing management server account to access the database in the SQL server? is it the SophosManagement account on the Management server or the SQL server which is member of Sophos DB Admins?

2. is there a possibility that there still a local SQL instance left in the management server that might cause the management server not to start? i dont know if i am making sense.

Thanks for the help.

HI,

Edit, I just re-read your post to see the command again:

sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SOPHOSSEC\Sophos DB Admins'"

Doesn't look right, what is SOPHOSEC, this should be being run on the SQLServer, so the command should be:

sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SQLSERVER\Sophos DB Admins'"

As Sophos DB Admins is a local group on SQLSERVER.

I think we need to confirm the environment as this is getting confusing.

• Your domain is called DELL (seems an odd name for a domain, sounds more like a computer name)
• SQL server is called SQLSERVER
• Both the management server and SQLServer are in the same DELL domain
• SophosManagement is a domain account that can therefore be referenced DELL\SophosManagement
• Based on the previous command you ran Is the SEC server called SOPHOSSEC then? 

There are potentially a number of combinations regarding the account depending on the environment.

• If the 2 computers are in a domain then typically you would use a domain account.
• If the 2 computers are in a workgroup then you would have to create SophosManagement on both computers with the same password.  When you ran the installer on the SQL server you would choose the local account and likewise when you instal the management component on the management server you would use the local account.
• If the SEC server is in a domain and the SQL server is in a workgroup and the SEC server is not a DC, you could create a local SophosManagement account on both with the same password.  You would choose the local accounts when you ran both installers.
• If the SEC server is a DC and the SQL server is a member server you would just create one domain "SophosManagement" account and enter the same account when running both installers.
• If the SEC server is a DC and the SQL server is in a workgroup, you would create a domain account on the DC called SophosManageemnt and a local account on the SQL Server called "SophosManagement".

Regards,

Jak

Hi Jak,

i am sorry for the confusion. i did configure everything from sratch doing it in a pilot network before implementing it this week making sure im doing the right thing. i appreciate your help.

• Domain: RSA.com
• SQL Server: SQLSERV
• Both the management server and SQLServer are in the same RSA domain
• SophosManagement is a domain account that can therefore be referenced RSA\SophosManagement
• ran sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SQLSERV\Sophos DB Admins'"

C:\>sqlcmd -E -S .\sophos -Q "EXEC sp_helplogins @LoginNamePattern ='SQLSERV\Sop
hos DB Admins'"
LoginName                                        SID
                                                         DefDBName    DefLangNam
e          AUser ARemote
------------------------------------------------ -------------------------------
-------------------------------------------------------- ------------ ----------
---------- ----- -------
SQLSERV\Sophos DB Admins                         0x01050000000000051500000041DE8
9D282EEB6FB08C41F9AF4030000                              master       us_english
           yes   no

(1 rows affected)
LoginName                                        DBName           UserName
                                   UserOrAlias
------------------------------------------------ ---------------- --------------
---------------------------------- -----------
SQLSERV\Sophos DB Admins                         SOPHOS47         Sophos DB Admi
ns Role                            MemberOf
SQLSERV\Sophos DB Admins                         SOPHOS47         SQLSERV\Sophos
 DB Admins                         User

(2 rows affected)

Sophos DB Admins is a local group on SQLSERV

• 2 computers are in a domain then typically you would used a domain account.
• SEC server and the SQL server is a member server in RSA domain and created "SophosManagement" account and entered the same account when running both installers."Sophos Management" account is member of "Sophos DB Admins" local group in the SQLSERV (SQL SERVER).
  

heres the result from the cmd command i run from the SQLSERV

C:\WINDOWS\system32>SQLCMD -E -S .\sophos -d SOPHOS47 -Q "exec dbo.SDDMBootstrap
DataGet"


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------
<bootstrapData xmlns="http://www.sophos.com/xml/msys/bootstrapdata.xsd"><bootstr
apCommand publisher="e6561447-b027-4bb9-a021-ba177a5c6287" subscriptionGroupId="
9859529D-C32C-456B-9791-AE5F9AFB8C1B" siteId="DEFAULT" address="\\SECCONSOLE\Sop
hosUpdate" addre

currently the management service wont start. :(

tried to do a UDL test from SQL SERVER to Management Server and its success.

Thanks

Hi,

That all looks good on the SQL side, if the "RSA\SophosManagment" account can call that Stored Procedure without error

So the error at the SEC server in the Windows application event log when you try and start the Sophos management service is still:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

More than likely you get this if the management server just can't see the SQL instance. because either:

• It can't resolve the address in the connection string, in this case SQLSERVER
• A firewall is on the SQL Server blocking access to the SQL port
• SQL Server is not confgiured to listen on TCP/IP.

Your connection string that the management service is using is in (HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseConnectionMS)

and should be:

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

for this setup and is what you reported.  So that seems ok.

If you create a text file on the management server called: "TestSophos.UDL" with the contents:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=SOPHOS47;Data Source=Sqlserver\SOPHOS;

Run it, then click "Test connection" it succeeds, so this tests the connection string is ok, meaning the address is resolvable from the management service and that the SOPHOS47 database can be accessed.

Also just check that the keys under:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseUser\

reflect the correct user:

DatabaseUserDomain  = RSA

DatabaseUserName     = SophosManagment

As you have SQLCMD.exe on the management server (from the local SQL instance), you should also be able to run:

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

either as rsa\SophosManement or just as your account, both test the SQL instance is accesible from the management server but running the command in a command prompt running rsa\SophosManagement is a closer simulation to what the management service is doing.

If that succeeds I'm a bit flummoxed and befuddled

Regards,

Jak

Hi Jak,

the for the advice. the management service is okay now. i took your advice on revieweing the SQL instance connection if its accepting connection. though the tcp/ip is enable already in the SQL server instance, i configured addition settings for isolutions which it helped us solved the problem.

•  enabled SQLServer brower(by default off)
• restart the SQR Server service
• Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express R2
• under properties selected Security on the left window. Then, select SQL Server and Windows Authentication mode.. (by default Windoes Authentication mode)
• Restart the service.

Management service is okay now. Many thanks for your support and patience. i appreciate it much. God bless

Hi Jak,

may ask a question about Patch Assessment for endpoint, since i just upgraded from 4.7 to 5.1 sec console. how do i deploy patch assessment push install without reinstall the AV agent? just install patch assessment to endpoint.

thanks

HI,

Glad it's all ok and you're up to SEC 5.1.

SEC only uses Windows auth not SQL auth.  I suspect that starting the SQL browser service did the trick.  I guess just the name of the server in the connection string wasn't enough to connect to the right port in this instance (excuse the pun :) ).

More info here:

http://msdn.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

To just "add" the patch client to an install see:

http://www.sophos.com/en-us/support/knowledgebase/113976.aspx

Regards,

Jak