locked
Issue recreating SSIS Catalog RRS feed

  • Question

  • I am working on a SQL Server 2012 (SP 4) instance.

    Background:

    About 5 years ago someone restored the SSISDB to the server.  Nothing has happened in the SSIS catalog since it was restored, no alterations to SSIS projects, no event messages.  Today we tried to deploy a project to this server and we get this message:  "Please create a master key in the database or open the master key in the session before performing this operation"  Error 15581.  I get the same error trying to validate package, download project.  Seems pretty obvious to me that whoever restored this database did not follow protocol to apply the password after restoring.

    My problem:

    I backed up the database and deleted the SSIS catalog.  Then I try to recreate it.  Every time I try, I get this exception:  Cannot drop master key because certificate 'MS_Cert_Exec_2' is encrypted by it.  Changed database context to 'SSISDB'.

    I can't find MS_Cert_Exec_2 anywhere on the server.  If I could, I'm not sure how I would delete it.  I'm not sure if it is a good idea to delete this certificate; I don't know what it is doing.

    This is a test server, but I still don't want to break it.  Any help anyone can provide would be appreciated.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, July 22, 2020 9:58 PM

Answers

All replies

  • Hi Russ Loski,

    "Please create a master key in the database or open the master key in the session before performing this operation"  Error 15581.

    Please refer to How To Fix SSIS Deployment Error “Please Create A Master Key” .

    Cannot drop master key because certificate 'MS_Cert_Exec_2' is encrypted by it.

    Please refer to Encrypting with SQL in test database, can't drop Master Key because it encrypted a 'test' certificate.

    May I know if you back up the master key for the SSISDB database ?

    Please refer to Backup, Restore, and Move the SSIS Catalog .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Russ Loski Thursday, July 23, 2020 4:42 PM
    Thursday, July 23, 2020 1:50 AM
  • May I know if you back up the master key for the SSISDB database ?

    The person who restored this database is no longer working there, so I am not sure of the source of the database.

    I will check the resource that mentioned the test certificate.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, July 23, 2020 11:23 AM
  • It would be simpler to delete the SSISDB, create a new SSISDB and redeploy your projects.

    • Marked as answer by Russ Loski Thursday, July 23, 2020 4:42 PM
    Thursday, July 23, 2020 12:05 PM
  • It would be simpler to delete the SSISDB, create a new SSISDB and redeploy your projects.

    That's what I thought.  But doing that gave me the "Cannot drop master key" error.

    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, July 23, 2020 12:10 PM
  • We found a 2012 Server with SSISDB catalog.  I followed the steps in 

    https://techcommunity.microsoft.com/t5/sql-server-integration-services/ssis-catalog-backup-and-restore/ba-p/388058 which are similar to the steps in Backup, Restore and Move the SSIS Catalog.

    I ran a trace while trying to create the SSIS Catalog and found that the error occurred when trying to drop Master Key in the SSISDB.  What the resource I had did was to regenerate the Master Key with a new password, using the FORCE keyword.

    I think that the FORCE keyword in the RESTORE MASTER KEY statement is what got me passed the Credential issue.

    Thanks for your help.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, July 23, 2020 4:48 PM