SQL Server Services stopped after applying SQL Server Cumulative/Security update

 SQL Server Services stopped after applying SQL Server Cumulative/Security update due to following error:

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

Note: Above error is one of the example.  Analyst may receive similar error for other missing security objects.

 

Whenever we have such upgrade script (SQL Server Cumulative\Security updates) failure issue and SQL Server instance is not getting started,

To find the root cause of the issue, go to the SQL Server installation Error location

Example: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\

If you found below error

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

We need to use trace flag 902 to start SQL which would bypass script upgrade mode. This would allow us the find the cause and fix it. So, here are the steps to fix missing user 'ModuleSigner' and login '##MS_SSISServerCleanupJobLogin##'

SQL instance needs to be rebooted and after that SQL Server Service does not start. 

As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.

Step 1: NET START MSSQLSERVER /T902

For named instance, we need to use below (replace instance name based on your environment)

NET START MSSQL$INSTANCENAME /T902

Step 2: As soon as SQL Server was started, you will able to connect because the upgrade installation didn’t run. Here is the T-SQL script which creates missing logins '##MS_SSISServerCleanupJobLogin##' and users 'ModuleSigner'.

USE [SSISDB]

CREATE USER [ModuleSigner] FOR CERTIFICATE [MS_SQLISSigningCertificate]

GO

USE [master]  
GO  
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD='Pa$$w0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  
GO  
GRANT VIEW SERVER STATE TO ##MS_SSISServerCleanupJobLogin##
GO
USE [SSISDB]  
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo]
GO

Step 3: After creating the login\user, stopped SQL Service using SQL Server Configuration Manager. We can also do it via command prompt using below command. Below is for the default instance.

NET STOP MSSQLSERVER

If you are dealing with named instance, then below is the command ((replace InstanceName based on your environment)

NET START MSSQL$INSTANCENAME

Step 4: Then start SQL normally (without trace flag) using SQL Server Configuration Manager.

NET START MSSQLSERVER

Step 5: Verify the SQL instance and install the latest Cumulative\Security update for the instance