SQL Server Upgrade Nightmare: Error 15151 Strikes During Database Updates!

Table of Contents

Encountering errors during SQL Server upgrades can be a significant roadblock for database administrators. One particularly disruptive issue is Error 15151, which often surfaces when applying cumulative updates (CUs) or service packs (SPs). This error signals a problem during the database upgrade script execution phase of the update process. This article is designed to guide you through understanding and resolving Error 15151, ensuring a smoother SQL Server update experience.

Symptoms of Error 15151 During SQL Server Upgrade¶

When a CU or SP installation process encounters Error 15151, the setup program will typically display a generic error message indicating a failure in the database engine recovery. This message often lacks specific details about the root cause, requiring further investigation. The primary error message you might encounter in the setup program is:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

To pinpoint the exact reason behind this failure, it’s crucial to examine the SQL Server error log. Within the error log, you’ll likely find an entry similar to the following, which clearly identifies Error 15151:

Error: 15151, Severity: 16, State: 1.
Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

This specific error message definitively points to the inability of SQL Server to locate the login ##MS_SSISServerCleanupJobLogin##. This login is a critical component for the proper functioning of SQL Server Integration Services (SSIS) catalog cleanup jobs. The error message indicates two potential scenarios: either the login is genuinely missing, or the account attempting the upgrade lacks the necessary permissions to access or verify its existence.

Root Cause of Error 15151¶

The underlying cause of Error 15151 during SQL Server upgrades is typically related to the ##MS_SSISServerCleanupJobLogin## login. This login, and its associated user ##MS_SSISServerCleanupJobUser##, are integral to the SSIS catalog database (SSISDB). The error arises when one of the following scenarios occurs:

• Accidental or Intentional Deletion: The most common cause is the unintentional or deliberate deletion of the ##MS_SSISServerCleanupJobLogin## login from the SQL Server instance. Database administrators might inadvertently remove this login while cleaning up unused accounts, unaware of its system-critical role.

• Incorrect Backup and Restore Procedures: If the SQL Server instance or the SSISDB database was restored from a backup, and the proper procedures were not followed, the login might not have been correctly restored. Specifically, if system databases like master and msdb were not restored in sync with user databases, inconsistencies in security principals can occur.

• Permissions Issues (Less Likely): While the error message mentions permission issues, it’s less likely to be the primary cause in standard scenarios. The account performing the SQL Server upgrade typically has elevated privileges. However, in highly locked-down environments or after specific security hardening measures, permission problems could theoretically contribute to the issue. It’s more probable that the login is simply missing.

Essentially, Error 15151 during SQL Server upgrades is a manifestation of a missing or inaccessible ##MS_SSISServerCleanupJobLogin##, which disrupts the database upgrade scripts that rely on this login’s existence.

Step-by-Step Resolution for Error 15151¶

Resolving Error 15151 involves re-creating the missing ##MS_SSISServerCleanupJobLogin## and ensuring the associated user ##MS_SSISServerCleanupJobUser## in the SSISDB database is correctly linked to this login. Follow these detailed steps to rectify the issue and proceed with your SQL Server upgrade:

Step 1: Start SQL Server with Trace Flag 902

To bypass the upgrade scripts that are failing due to the missing login, you need to start SQL Server in minimal configuration mode. This is achieved by using trace flag 902. This allows you to connect to SQL Server and make the necessary corrections without the upgrade process immediately re-triggering the error.

You can add trace flag 902 as a startup parameter to the SQL Server service. Here’s how:

1. Open SQL Server Configuration Manager.
2. Locate SQL Server Services.
3. Right-click on your SQL Server instance service and select Properties.
4. Go to the Startup Parameters tab.
5. In the “Specify startup parameters” box, add -T902 at the end of any existing parameters, separated by a semicolon if needed. For example, if there are other parameters, it might look like -d"SQLDataDir";-e"SQLErrorLogDir";-T902.
6. Click Apply and then OK.
7. Restart the SQL Server service.

Starting SQL Server with trace flag 902 prevents the automatic execution of upgrade scripts, giving you a window to fix the login issue.

Step 2: Re-create the ##MS_SSISServerCleanupJobLogin## Login

Once SQL Server is running with trace flag 902, connect to it using SQL Server Management Studio (SSMS) or another SQL client as a highly privileged user (e.g., sysadmin role). Execute the following Transact-SQL (T-SQL) script to re-create the ##MS_SSISServerCleanupJobLogin## login in the master database:

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##]
WITH PASSWORD = N'<YourStrongPassword>', -- Replace <YourStrongPassword> with a strong password
DEFAULT_DATABASE = [master],
DEFAULT_LANGUAGE = [us_english],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

Important Considerations:

• Password: Replace <YourStrongPassword> with a robust password. While the password is automatically managed by SQL Server in normal circumstances, setting one during manual creation is necessary. Choose a strong, complex password for security best practices.
• Default Settings: The script sets default database to master, default language to us_english, and disables password expiration and policy checks. These settings are consistent with how system logins are typically configured.

After executing this script, the ##MS_SSISServerCleanupJobLogin## login will be re-created at the server level.

Step 3: Map the User in SSISDB to the Newly Created Login

Next, you need to ensure that the user ##MS_SSISServerCleanupJobUser## in the SSISDB database is correctly associated with the newly created login. Execute the following T-SQL script in the context of the SSISDB database:

USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] WITH LOGIN =[##MS_SSISServerCleanupJobLogin##]
GO

This script uses the ALTER USER statement to re-map the existing user ##MS_SSISServerCleanupJobUser## to the ##MS_SSISServerCleanupJobLogin## login you just created. This step is crucial for restoring the connection between the database user and the server-level login.

Step 4: (Optional) Re-create the User if Missing

In some rare scenarios, not only the login but also the user ##MS_SSISServerCleanupJobUser## within the SSISDB database might be missing. If the previous step fails or you suspect the user is also absent, execute the following comprehensive script in the SSISDB database context:

USE [SSISDB]
GO
DROP USER [##MS_SSISServerCleanupJobUser##]
GO

CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##]
GO

ALTER USER [##MS_SSISServerCleanupJobUser##] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO [##MS_SSISServerCleanupJobUser##]
GO
GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO [##MS_SSISServerCleanupJobUser##]
GO

Explanation of this script:

• DROP USER [##MS_SSISServerCleanupJobUser##]: This line attempts to drop the user if it exists. If the user doesn’t exist, this command will fail, but the script will continue. Dropping and re-creating ensures a clean state.
• CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##]: This recreates the user ##MS_SSISServerCleanupJobUser## and explicitly links it to the ##MS_SSISServerCleanupJobLogin## login.
• ALTER USER [##MS_SSISServerCleanupJobUser##] WITH DEFAULT_SCHEMA=[dbo]: Sets the default schema for this user to dbo (database owner). This is a standard setting for database users.
• GRANT EXECUTE ...: These GRANT statements provide the necessary execute permissions to the user on two internal stored procedures within the SSISDB database: [internal].[cleanup_server_project_version] and [internal].[cleanup_server_retention_window]. These stored procedures are used by the SSIS catalog cleanup jobs.

This comprehensive script ensures that both the login and the user are correctly configured with the necessary permissions for the SSIS catalog cleanup functionality.

Step 5: Remove Trace Flag 902 and Restart SQL Server

After successfully executing the scripts and resolving the login and user issues, you need to remove the trace flag 902 and restart SQL Server normally to allow the upgrade process to complete.

1. Go back to SQL Server Configuration Manager.
2. Navigate to the Startup Parameters tab for your SQL Server instance service.
3. Remove -T902 from the startup parameters.
4. Click Apply and then OK.
5. Restart the SQL Server service.

Upon restarting without trace flag 902, SQL Server will resume the database upgrade process. With the ##MS_SSISServerCleanupJobLogin## and ##MS_SSISServerCleanupJobUser## correctly configured, Error 15151 should be resolved, and the upgrade should proceed without further issues.

Step 6: Verify Upgrade Completion (Post-Restart)

After the restart, it’s crucial to verify that the SQL Server upgrade has completed successfully.

• Check SQL Server Error Log: Examine the SQL Server error log for any further errors or warnings during startup and upgrade. Look for messages indicating successful database upgrades.
• Verify SQL Server Version: Connect to SQL Server and execute SELECT @@VERSION; to confirm that the SQL Server version has been updated to the target CU or SP level.
• Test SSIS Functionality (If Applicable): If you use SQL Server Integration Services, test basic SSIS catalog operations to ensure that the cleanup jobs and other SSIS functionalities are working as expected.

By completing these verification steps, you can confidently confirm that Error 15151 has been resolved and your SQL Server upgrade is successful.

Best Practices to Prevent Error 15151¶

While the resolution steps outlined above address Error 15151 effectively, preventing this error in the first place is always preferable. Here are some best practices to minimize the risk of encountering this issue during SQL Server upgrades:

• Avoid Manual Deletion of System Logins/Users: Exercise extreme caution when managing SQL Server logins and users. Never manually delete system-created logins or users like ##MS_SSISServerCleanupJobLogin## or ##MS_SSISServerCleanupJobUser## unless explicitly instructed by Microsoft documentation and fully understanding the implications. These logins often have critical internal roles.

• Follow Proper Backup and Restore Procedures: When performing backup and restore operations, especially involving system databases (master, msdb) and user databases like SSISDB, adhere to Microsoft’s recommended best practices. Ensure that system databases and relevant user databases are restored consistently to maintain security principal mappings.

• Regularly Review Security Principals (Auditing): Implement a process for periodically reviewing and auditing SQL Server logins and users, particularly in environments with multiple administrators. This helps identify and rectify any accidental or unauthorized changes to system accounts.

• Test Upgrades in Non-Production Environments: Before applying any CU or SP to production SQL Server instances, thoroughly test the upgrade process in a representative non-production environment. This allows you to identify and resolve potential issues like Error 15151 without impacting live operations.

• Consult Upgrade Documentation: Always carefully review the release notes and upgrade documentation for the specific CU or SP you are applying. Microsoft documentation often provides important pre-upgrade steps, known issues, and mitigation strategies.

By implementing these proactive measures, you can significantly reduce the likelihood of encountering Error 15151 and ensure smoother, more reliable SQL Server upgrade processes.

---

We hope this guide has been helpful in resolving Error 15151 during your SQL Server upgrade. If you have encountered this error or have additional insights or troubleshooting tips, please feel free to share your experiences in the comments below. Your contributions can help other database administrators facing similar challenges.