Post reply

SSISDB

  • March 6, 2015 at 1:26 am

    #398133

    During the installation of the SSISDB in SQL 2012 AG, the password used to encrypt the database was not preserved.

    Now when the server is failed over, the SSISDB on the new primary node is not usuable.

    How do I now get around this?

    Is it easier to start from scratch again. Document the password and decrypt after using the master key after failover.

  • March 6, 2015 at 2:08 am

    #1782101

    I believe that you have to remove the SSISDB and start from scratch as there is no way that I know of to recover the password after the setup has completed.

    I will add one caveat before you strip the SSIDB out and restart, and that Is I've only ever worked with SSISDB on a single instance, so I'm not familiar with setting up the SSISDB on a cluster.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • March 6, 2015 at 2:13 am

    #1782102

    Thanks Jason,

    I'm leaning towards doing that.

  • March 6, 2015 at 2:24 am

    #1782103

    By 'not preserved', do you mean you don't remember the password you used when creating the SSIS Catalogue (creates SSISDB)? Or you expected that the password will be synced as part of the replica database syncing from primary replica to secondary replica? The password is not synced as part of the AG.

    After failover, you will need to re-encrypt the master key in order to continue to run packages. Look at the following on BOL:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password';

    GO

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    GO

    'Password' is the password you used when creating the SSIS catalogue. For auto-failover you need to put in a check for which replica is primary and re-encrypt after each failover via a job.

    Cheers,

    Charles

  • March 6, 2015 at 2:59 am

    #1782113

    Thanks Charles,

    Yes the password was not kept. So I cannot use the SSISDB on the secondary node after failover.

  • March 6, 2015 at 6:54 am

    #1782158

    Consider the following; failover to the original primary replica and regenerate the master key with a new password (key). Use ssisdb. I said you should consider the option so you should review it and probably test it in your non-prod environment first. Schedule during maintenance window.

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'newstrongpassword'

    --

    Charles

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply