After upgrading the Sophos Enterprise Console to version 5.x the 'Sophos Management Service' service is unable to start. The following error is found in the Microsoft SQL Server 'ERRORLOG' file:
Error: 18456, Severity: 14, State: 38.
Login failed for user '[DOMAIN]\[DatabaseUserAccount]'. Reason: Failed to open the explicitly specified database. [CLIENT: <named pipe>]
First seen in
Enterprise Console 5.0.0
It is likely that there is a mismatch in the Security ID (SID) for the 'Sophos DB Admins' group between 'Windows Active Directory' or 'Local Groups and Users' and the Microsoft SQL Server Sophos DB Admins login. To confirm that this is the case perform the following steps:
- Check the 'SQL ERROR' log for the presence of the above error. This log can normally be found in:
'C:\Program Files\Microsoft SQL Server\MSSQL10.SOPHOS\Logs\'
- Confirm a SID mismatch between the SQL and Windows groups by running the following command (Change DOMAIN to match the location of the Sophos DB Admins group):
sqlcmd -E -S .\SOPHOS -Q "ALTER USER [Sophos DB Admins] WITH LOGIN = [DOMAIN\Sophos DB Admins]"
This command should return the following error if there is an SID mismatch:
Msg 15098, Level16, State 1,
Server SERVER\Sophos, Line 1
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
What To Do
- Remove the login from the instance using the following command (as before, edit DOMAIN to be the domain name or server name depending on the location of the 'Sophos DB Admins' group.):
sqlcmd -E -S .\SOPHOS -Q "IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DOMAIN\Sophos DB Admins') DROP LOGIN [DOMAIN\Sophos DB Admins]"
- Re-add the login using the following command:
sqlcmd -E -S .\SOPHOS -Q "CREATE LOGIN [DOMAIN\Sophos DB Admins] FROM WINDOWS"
- To remap it to the login:
- If you are running Enterprise Console 5.0 or less (5.1.0 and later has the file as part of the installation file set), download the SQL script ResetUserMappings.zip and extract it to 'C:\Program Files\Sophos\Enterprise Console\' (Use 'C:\Program Files (x86)\Sophos\Enterprise Console' for x64 installations)
- Run the following commands.
Note: The below example is for Sophos Enterprise Console 5.0 installations. For other versions of Enterprise Console, see article 17323 for the correct database name for each version.
sqlcmd -E -S .\SOPHOS -d SOPHOS50 -i "C:\Program Files\Sophos\Enterprise Console\ResetUserMappings.sql"
sqlcmd -E -S .\SOPHOS -d SOPHOSPATCH -i "C:\Program Files\Sophos\Enterprise Console\ResetUserMappings.sql"
services.msc and start the .Sophos Management Service'.
services.msc and restart the 'Sophos Management Host Service'.
- Open the Enterprise Console and confirm that access to the Patch and Web event viewers open without errors.
In the event that the above has not resolved your issue, or you have issues accessing the Patch or Web event viewers, please contact support for further assistance.