Script level upgrade for database ‘master’ failed

The following scenario Occurred recently:

• A Customer identifies that SQL Services are down and cannot even restart them manually & makes the emergency call to us
• Upon initial verification the error from Application log in event viewer looked like:

Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 15151, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

• I believed in the error message and looked for a master database backup to repair or rebuild it. For some reason, the system databases didn’t have a backup schedule.
• The next option would be bypassing the script level upgrade mode and start the SQL Server so that I can have a look at the SQL Error log. To accomplish this, I Added Trace flag T902 to SQL Server startup parameters & started the SQL Services successfully. You can find more about Trace flag T902 here
• After starting the SQL Services, I verified the SQL Error log and identified the root cause of the issue:

Starting execution of ISServer_upgrade.SQL

Taking SSISDB to single user mode

Setting database option SINGLE_USER to ON for database 'SSISDB'.

AppDomain 2 (SSISDB.dbo[ddl].1) unloaded.

AppDomain 3 (SSISDB.dbo[ddl].2) unloaded.

Grant permission to ModuleSigner

Cannot find the login '##MS_SSISServerCleanupJobLogin##'<c/> because it does not exist or you do not have permission.

Error: 15151<c/> Severity: 16<c/> State: 1.

Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151<c/> state 1<c/> severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database<c/> it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors<c/> take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error: 912<c/> Severity: 21<c/> State: 2.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup<c/> repair it<c/> or rebuild it. For more information about how to rebuild the master database<c/> see SQL Server Books Online.

Error: 3417<c/> Severity: 21<c/> State: 3.

SQL Server shutdown has been initiated

• The shutdown of SQL Services was triggered because the SSIS upgrade script couldn’t find the login ##MS_SSISServerCleanupJobLogin## to complete the upgrade.
• Then, I brought the SSISDB to multi_user mode (It was in single user mode after starting the SQL Services) & created the missing login ##MS_SSISServerCleanupJobLogin##  with some random password. After creating the login, I mapped the SSISDB user ##MS_SSISServerCleanupJobUser## to the newly created login. You can find the login creation & user mapping script below:

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'95DJQEXZVEVUusHeh', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

• Now, I removed the trace flag T902 from the SQL Server startup parameters & restarted the SQL Services successfully. The SSIS script upgrade worked this time & there were no issues.
• You can encounter these kinds of issues during SP/CU upgrades and/or improper restoration of SSISDB from a lower version to a higher version of SQL Server.