Cannot install database - Upgrade SEC 5.5.0 to 5.5.1

Hi Kader 

I am assuming that you have tried to upgrade the database from the GUI of the installer of SEC 5.5.1.

Please refer to this article which has a command-line method to upgrade the database for the respective version.

Please go through it and carefully follow the article.

Bonjour Jasmine,

I went thru the article : https://community.sophos.com/kb/en-us/116768#Domain%20member%20server%20or%20workgroup%202

Enterprise Console 5.5.1

• From 5.0/5.1.0/5.2.0/5.2.1/5.2.1 R2/5.2.2/5.3/5.3.1/5.4.0/5.4.1 and 5.5.0

The first command :

C:\sec_551\ServerInstaller\DB\Core\InstallDB.bat (local)\SOPHOS SCAD01MTL SOPHOS551 Sophos_InstallCore551DB.log

Give me error :

InstallDB (local)\SOPHOS SCAD01MTL SOPHOS551 Sophos_InstallCore551DB.log
Started
2020-02-19
12:11
==========
Msg 208, Level 16, State 1, Server SCAD01MTL\SOPHOS, Line 9
Invalid object name 'sysdatabases'.

Did I miss something ??

Thks for your advice and help 

Hi QC,

Result of the command you request :

Msg 208, Level 16, State 1, Server SCAD01MTL\SOPHOS, Line 1
Invalid object name 'sysdatabases'.

But If I run :

sqlcmd -E -S (local)\sophos -Q "SELECT * from sys.databases"

I have the result of my database :

master 1 NULL 0x01 2003-04-08 09:13:36.390 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0 0 0 0
tempdb 2 NULL 0x01 2020-02-19 11:04:07.383 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 25717107-CB4B-4B8F-A762-FCC437F1063C 1 0 NOTHING 0 0 0 0
model 3 NULL 0x01 2003-04-08 09:13:36.390 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0 0 0 0
msdb 4 NULL 0x01 2008-07-09 16:46:27.767 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 D92CDCC6-0209-4A91-BC1A-93790DDB158E 1 0 NOTHING 0 0 0 0
SafeGuard_FONDS_FQR_SCAD01MTL 5 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2014-06-26 09:20:56.043 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 1 FULL 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0406DC62-E708-4BD2-A7F0-263CBDEF1427 1 0 NOTHING 0 0 0 0
SOPHOS550 6 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2017-05-11 14:22:19.397 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 1 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 D60C3FFC-940D-43E7-B090-CC157B2B929C 1 0 NOTHING 0 0 0 0
SOPHOS52 7 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2013-02-12 18:39:35.923 100 NULL 0 MULTI_USER 0 0 0 0 ONLINE 0 1 0 0 OFF 1 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 A1FBDD1E-846E-4CE7-842C-B3A8987095EB 1 0 NOTHING 0 0 0 0
SophosSecurity 8 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2013-02-12 18:40:11.580 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 1 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 B35BDE7C-8FE6-4A0E-BE5A-7DABC3CDAE02 1 0 NOTHING 0 0 0 0
SOPHOSPATCH52 9 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2013-02-12 18:40:21.817 100 French_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 1 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 A1C69293-F247-4111-AE48-6D73BD015A3E 1 1 CHECKPOINT 0 0 0 0
SOPHOSENC52 10 NULL 0x0105000000000005150000000AF8BDF1424D58473F6D9FEE4C120000 2013-02-12 18:40:30.350 100 NULL 0 MULTI_USER 0 0 0 0 ONLINE 0 1 0 0 OFF 0 1 FULL 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 8D08EEC3-E979-4952-83B9-9AEEFEB69BF3 1 0 NOTHING 0 0 0 0

(10 rows affected)

Thks QC

Hello Kader,

quick reply, have to leave.

Ar least CreateDatabaseWithCollation.sql in the ...\DB\Core\ directory uses sysdatabases as opposed to sys.databases. Might suffice to amend it there.

Christian

Qc,

Sorry, not I'm not used with SQL command ;(

I have already sql file : CreateDataBaseWithCollation.sql in the..... ......\DB\Core\

Do i have substitute sysdatabases by sys.databases ??

--***********************************************************************************

--* *
--* Copyright 2004-2016 Sophos Limited. All rights reserved. *
--* *
--* If you choose to modify the scripts and/or commands provided in this file in *
--* any way or you choose to use them in any way other than as directed *
--* (“Modification”), you agree Sophos will not be liable to you for any loss or *
--* damage of any kind (except personal injury or death resulting from Sophos’s *
--* negligence) arising from any Modification, or from faults or defects in the *
--* installation whether caused by negligence or otherwise and Sophos shall not be *
--* liable for any indirect, special, incidental or consequential damages, whether *
--* in contract, tort or otherwise, arising out of any use of the scripts or any *
--* associated documentation. Sophos reserves the right not to provide support *
--* where any Modification has taken place. Sophos will use reasonable endeavours *
--* to provide first line support. Should issues arise that require second line *
--* support, or any other escalation process, Sophos cannot guarantee that such *
--* issues will be resolved. *
--* *
--* IN RELATION TO ANY MODIFICATION, TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE *
--* LAW, IN NO EVENT SHALL SOPHOS BE LIABLE TO YOU FOR OR TO THOSE CLAIMING THROUGH *
--* YOU FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, INCIDENTAL OR SPECIAL DAMAGE OR *
--* LOSS OF ANY KIND INCLUDING, BUT NOT LIMITED TO, LOSS OF PROFITS, LOSS OF *
--* CONTRACTS, BUSINESS INTERRUPTIONS, LOSS OF OR CORRUPTION OF DATA HOWEVER CAUSED *
--* AND WHETHER ARISING UNDER CONTRACT OR TORT, INCLUDING NEGLIGENCE, EVEN IF *
--* SOPHOS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. *
--* *
--***********************************************************************************

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

DECLARE @lastDBName NVARCHAR(100)
DECLARE @lastDBCollation NVARCHAR(100)
DECLARE @dropDB NVARCHAR(100)
DECLARE @createDB NVARCHAR(100)
SET @lastDBCollation = NULL

--get the latest SOPHOS DB
SELECT TOP 1 @lastDBName = name
FROM sysdatabases
WHERE name > 'SOPHOS1' and name<'SOPHOS9' and name <>'$(newDBName)'
ORDER BY name DESC

--get the database default collation from the latest DB (if we have)
IF @lastDBName IS NOT NULL
BEGIN
select @lastDBCollation = CAST(DATABASEPROPERTYEX(@lastDBName,'Collation') as nvarchar(100))
END
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(newDBName)' )
BEGIN
SET @dropDB = 'DROP DATABASE ' + '$(newDBNAme)'
EXEC (@dropDB)
END
IF @lastDBCollation IS NULL

--there is no previuos database
BEGIN

--create the new database with the server default collation
SET @createDB = 'CREATE DATABASE ' + '$(newDBNAme)'
END
ELSE

--there is at least one previous database
BEGIN

--create the new database with the collation given by the default collation of the latest (previuos) database
SET @createDB = 'CREATE DATABASE ' + '$(newDBNAme)' + ' COLLATE ' + @lastDBCollation
END
EXEC (@createDB)
GO

Thank you for you time.

Hi Kader 

I have reviewed the logs provided above and found the below error:

RunScript: Custom action data: C:\Program Files\Sophos\Enterprise Console\DB\Core\;InstallDBEx.bat;(local)\SOPHOS;SCAD01MTL;SOPHOS551;C:\ProgramData\Sophos\Management Installer\Sophos_InstallCoreDB.log.
RunScript: Command line = "C:\Program Files\Sophos\Enterprise Console\DB\Core\InstallDBEx.bat" (local)\SOPHOS SCAD01MTL SOPHOS551 "C:\ProgramData\Sophos\Management Installer\Sophos_InstallCoreDB.log"
RunScript:
RunScript: C:\Program Files\Sophos\Enterprise Console\DB\Core>sqlcmd -E -S (local)\SOPHOS -b -i CreateDatabaseWithCollation.sql -v newDBName = SOPHOS551 1>>"C:\ProgramData\Sophos\Management Installer\Sophos_InstallCoreDB.log"
RunScript: Failed. See "C:\ProgramData\Sophos\Management Installer\Sophos_InstallCoreDB.log" for details.
RunScript: Error 0x80004005: Batch file returned error code 1.
CustomAction RunDBScript returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)
MSI (s) (98:A4) [09:40:35:222]: User policy value 'DisableRollback' is 0
MSI (s) (98:A4) [09:40:35:222]: Machine policy value 'DisableRollback' is 0
Action ended 09:40:35: InstallFinalize. Return value 3.

QC has already suggested you the action to perform for this error but I'd still request you to go through this article once. 

Apart from that, QC can help you more on this as I am also not commanding on SQL part. Let's wait for his reply, else I'd suggest you go ahead and open a support case and PM me the support case number.

Hello Kadar,

as far as I can see this is the only occurrence of sysdatabases and thus simply substitute sysdatabases by sys.databases. Can't say why the compatibility view is not available with your SQL instance.

Anyway it looks like an oversight as in all other places the system catalog views (sys.xxxxxxx) are used. I expect Sophos will correct it with SEC 5.5.2 or 5.6 or whatever the next version will be.

Christian

Hi Christian,

Sorry for the delay, busy on another things !!! I will be back to you ASAP...Have a nice WE

Kader

Bonjour Christian,

Works like a charm [:)]

I substituted sysdatabases by sys.databases in the : CreateDatabaseWithCollation.sql file located C:\sec_551\ServerInstaller\DB\Core\

I followed the article proposed by Jasmine : https://community.sophos.com/kb/en-us/116768

InstallDB.bat

UpdatePatchDB.bat

and UpdateSophosSecurity.bat 

Without problem, then I ran the server installer to finish the upgrading.

Merci for your help and advices !!

Hi Kader 

Glad to hear that your issue has been resolved.

Hello all,

as we've heard 5.5.2 is available. Even though it no longer supports SQL Server 2008 (all flavours) and requires at least Native Client 11 and the Copyright in CreateDatabaseWithCollation.sql has been updated it still says
SELECT TOP 1 @lastDBName = name
FROM sysdatabases

Christian

Bonjour Christian,

Thanks for the info of SEC 5.5.2.

In a couple weeks I will build 2012 or 2019 Server to migrate SEC 5.5.1 by following this procedure :

https://community.sophos.com/kb/en-us/28276

I hope will have no problems.

Bonjour Christian,

Thanks for the info of SEC 5.5.2.

In a couple weeks I will build 2012 or 2019 Server to migrate SEC 5.5.1 by following this procedure :

https://community.sophos.com/kb/en-us/28276

I hope will have no problems.

Hi Kader 

The above article should work, feel free to reach out to us for any further concerns.