Recently I came across the following error when going into a Sophos Enterprise Console (5.2.1) after an upgrade from 5.2.0 which struck me as a little odd.
Management server connection failed
Could not connect to the Management Server.
This may be due to one of the following:
• Local network problems
• Management service has stopped on the server
• Your database service has stopped.
Either attempt to reconnect or close the application. Attempting to reconnect may take a few minutes.
Clicking ‘Reconnect’ fails.
In addition to the above message, when you attempt to start the ‘Sophos Management Service’ service from Windows services (Start | Run, then type: services.msc | Press return), the following error is displayed:
Could not start the Sophos Management Service on Local Computer.
Error 0x80004005: Unspecified error
There are various causes for this issue.
- The database does not exist.
- The database account is not a member of the Windows ‘Sophos DB Admins’ group.
- The database account does not have sufficient rights to access the database.
Providing that the correct database exists and the database account is a member of the Windows security group ‘Sophos DB Admins’, it is likely that the SID of this group in Windows is different from that stored in SQL. As a result, the database user does not have access to the database.
Below are the troubleshooting steps I performed.
- Check that the database exists in the SQL instance. See article 17323 for a list of expected databases for each version of Enterprise Console. If the database does not exist in the SQL instance you need to create it either by running the installer or running the scripts.
- Determine your database account. Ensure that this Windows account is a member of the Windows security group ‘Sophos DB Admins’.
Note: If the database component is installed on a domain controller this will be a domain local group, otherwise, it will be a local group.
If not, add the database user to the group and restart the “Sophos Management Service”.
Providing that the correct database exists and the database account is a member of the Windows security group ‘Sophos DB Admins’, it is likely that the SID of the group in Windows is different from that stored in SQL Server.
Note: You may also see a ‘Failure Audit’, Event ID 18456 from source MSSQL$SOPHOS in the application event log. This same message is also logged in the SQL Server ERRORLOG file.
Run the following commands in a command prompt on the database server from the Enterprise Console directory, e.g., program filessophosenterprise console (or program files (x86)… on a 64-bit computer)…
sqlcmd -E -S .SOPHOS -d SOPHOS521 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SOPHOSPATCH52 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SOPHOSENC52 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SophosSecurity -i ResetUserMappings.sql
If running this command returns the error:
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
…the problem is the mapping between the Windows group ‘Sophos DB Admins’ and the SQL Login. To fix this issue, run the following commands, substituting SERVERNAME for your domain name if ‘Sophos DB Admins’ is a domain group; otherwise enter the computer name where the ‘Sophos DB Admins’ group resides.
Note: The square brackets are required.
sqlcmd -E -S .sophos -Q “DROP LOGIN [SERVERNAMESophos DB Admins]”
sqlcmd -E -S .sophos -Q “CREATE LOGIN [SERVERNAMESophos DB Admins] FROM WINDOWS”
Once complete, re-run the previous commands, i.e.:
sqlcmd -E -S .SOPHOS -d SOPHOS521 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SOPHOSPATCH52 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SOPHOSENC52 -i ResetUserMappings.sql
sqlcmd -E -S .SOPHOS -d SophosSecurity -i ResetUserMappings.sql