none
Upgrade from Standard to Enterprise ##MS_AgentSigningCertificate## Error

    Question

  • Hi everybody!

     

    We upgraded from SQL Server 2005 x64 Standard to Enterprise using the SKUUPGRADE=1 switch. The installation went through so far but failed at one point with the following error:

     

    (We are upgrading a german installation so I'll have to translate the error messages as best as I can)

     

    C:\Programme\Microsoft SQL Server\MSSQL.4\MSSQL\Upgrade\sqlagent90_msdb_upgrade.sql

     

    "SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]The certificate cannot be dropped because one or more entities are either signed or encrypted using it.. To continue, correct the problem, and then run SQL Server Setup again."

     

    The log file contains these interesting messages:

     

    [Microsoft][SQL Native Client][SQL Server]A certificate with the name '##MS_AgentSigningCertificate##' already exists or it has already been added to this database.

     

    [Microsoft][SQL Native Client][SQL Server]Cannot create ##MS_AgentSigningCertificate## in msdb. INSTMSDB.SQL terminating.

     

    A look in the Object-Explorer shows me that only the master and msdb databases have the ##MS_AgentSigningCertificate## certificate. The master database also has a user named ##MS_AgentSigningCertificate##.

     

    Now that the installation in not fully finished and in this state I'm not able to apply SP2 and the latest cumulative update package I'd really like to get rid of this problem and successfully finish the installation.

     

    Any help will be greatly appreciated!

     

    The SQL Server is running on a fully patched Windows Server 2003 R2 Standard x64.

     

    Thanks
    VZ

    Wednesday, September 3, 2008 1:22 PM

Answers

  • The Microsoft Support really helped us out here. We had to run the script below to fix the problem.

     

    Code Snippet

    use msdb

    BEGIN TRANSACTION

    declare @sp sysname

    declare @exec_str nvarchar(1024)

    declare ms_crs_sps cursor global for select object_name(crypts.major_id) from sys.crypt_properties crypts, sys.certificates certs where crypts.thumbprint = certs.thumbprint and crypts.class = 1 and certs.name = '##MS_AgentSigningCertificate##'

    open ms_crs_sps

    fetch next from ms_crs_sps into @sp

    while @@fetch_status = 0

    begin

    if exists(select * from sys.objects where name = @sp) begin print 'Dropping signature from: ' + @sp set @exec_str = N'drop signature from ' + quotename(@sp) + N' by certificate [##MS_AgentSigningCertificate##]'

    Execute(@exec_str)

    if (@@error <> 0)

    begin

    declare @err_str nvarchar(1024)

    set @err_str = 'Cannot drop signature from ' + quotename(@sp) + '. Terminating.'

    close ms_crs_sps

    deallocate ms_crs_sps

    ROLLBACK TRANSACTION

    RAISERROR(@err_str, 20, 127) WITH LOG

    return

    end

    end

    fetch next from ms_crs_sps into @sp

    end

    close ms_crs_sps

    deallocate ms_crs_sps

    COMMIT TRANSACTION

    go

     

     

    Monday, September 15, 2008 9:08 AM

All replies

  • One additional question:

     

    Can anyone tell we what happens when I'll try to manually delete the certificate from the msdb database and both the certificate and the user from the master database?

    Thursday, September 4, 2008 9:17 AM
  •  

    I have the same problem, have yo been able to figure out which certificate to delete?
    Monday, September 8, 2008 5:03 PM
  • We're working on a solution with the Microsoft Support. I'll keep you updated when we got through this.

     

    Tuesday, September 9, 2008 7:13 AM
  • The Microsoft Support really helped us out here. We had to run the script below to fix the problem.

     

    Code Snippet

    use msdb

    BEGIN TRANSACTION

    declare @sp sysname

    declare @exec_str nvarchar(1024)

    declare ms_crs_sps cursor global for select object_name(crypts.major_id) from sys.crypt_properties crypts, sys.certificates certs where crypts.thumbprint = certs.thumbprint and crypts.class = 1 and certs.name = '##MS_AgentSigningCertificate##'

    open ms_crs_sps

    fetch next from ms_crs_sps into @sp

    while @@fetch_status = 0

    begin

    if exists(select * from sys.objects where name = @sp) begin print 'Dropping signature from: ' + @sp set @exec_str = N'drop signature from ' + quotename(@sp) + N' by certificate [##MS_AgentSigningCertificate##]'

    Execute(@exec_str)

    if (@@error <> 0)

    begin

    declare @err_str nvarchar(1024)

    set @err_str = 'Cannot drop signature from ' + quotename(@sp) + '. Terminating.'

    close ms_crs_sps

    deallocate ms_crs_sps

    ROLLBACK TRANSACTION

    RAISERROR(@err_str, 20, 127) WITH LOG

    return

    end

    end

    fetch next from ms_crs_sps into @sp

    end

    close ms_crs_sps

    deallocate ms_crs_sps

    COMMIT TRANSACTION

    go

     

     

    Monday, September 15, 2008 9:08 AM