Msg 15559: Script Level Upgrade for master Failed

Msg 15559 stopped the SQL Server service from starting after an upgrade from SQL Server 2005 to SQL Server 2014. I helped a client with this one. The log showed two errors, 15559 and 912, and the second one is the one that keeps the instance down.

The Error

This is what the ERRORLOG showed:

Error: 15559, Severity: 16, State: 1.
Cannot drop certificate '##MS_AgentSigningCertificate##' because there is a user mapped to it.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent90_sysdbupg.sql' encountered error 15559, 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 the upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

What the Client Saw

My client finished the upgrade and then the SQL Server service would not start. As usual, I went to the ERRORLOG first. If you need help finding it, read where the ERRORLOG lives.

Why It Happens

The upgrade script tries to drop the ##MS_AgentSigningCertificate## certificate. SQL Server refuses while a user is still mapped to it, which is error 15559. The failed step then raises error 912, and the instance cannot start.

The same symptom can have other causes. I wrote about one with a folder permission problem and another where a login owns an availability group.

How to Check

To fix an upgrade failure, start SQL Server with trace flag 902. Then fix the error that made the script fail. Here is what I did for my client:

1. I added the trace flag -T902 to the startup parameters and started the instance.
2. I took a backup of all system databases.
3. I ran the three queries below.

The first query finds the principal ID of the certificate login:

SELECT principal_id
FROM sys.server_principals
WHERE name = '##MS_AgentSigningCertificate##'

It returned 284 as the principal ID. The second query looks for any permissions that this principal has granted to others:

SELECT *
FROM sys.server_permissions
WHERE grantor_principal_id = 284

It returned no rows. The third query finds the user mapped to the certificate:

USE msdb
GO
SELECT *
FROM sys.syslogins a
INNER JOIN sysusers b ON a.NAME = b.NAME
WHERE b.NAME = '##MS_AgentSigningCertificate##'

This query gave us the username.

The Fix

We dropped the mapped user. Then I removed the trace flag -T902 from the startup parameters.

Confirm It Worked

After that, we were able to bring up the SQL Server service successfully.

How to Prevent It

Take a backup of all system databases before any upgrade, as I did here before changing anything. Keep the ERRORLOG in reach, because the first error in it points to the cause.

Have you seen similar errors during an upgrade? How did you fix them? Tell me in the comments and share it with others.

An upgrade failure is not a broken server, it is one leftover object that the script cannot drop.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.