Master key issue with SSISDB and AlwaysOn Availablity Group

  • Hi Security Guru,

    I am trying to put every thing together from this below blog post so that we can run SSIS packages when a failover occurs to 2nd Replica that automatically becomes our new primary replica. The encrypted password is stored in lookup table.

    http://blogs.msdn.com/b/mattm/archive/2012/09/19/ssis-with-alwayson.aspx

    Here is my script:

    -----------------------------------------------------------------------------------------------------

    USE TEST;

    DECLARE @last_role TINYINT;

    SET @last_role = (SELECT TOP 1 [replica_role] FROM [TEST].[dbo].[lookup_replica_role]);

    DECLARE @current_role TINYINT;

    SET @current_role = ( SELECT ROLE FROM sys.dm_hadr_availability_replica_states

    WHERE is_local = 1);

    IF (@last_role = 2 AND @current_role = 1) -- Last time it was secondary,

    -- currently it is primary: need re-encrypt

    -- the database master key

    BEGIN

    USE SSISDB;

    PRINT 'Opening the key'

    OPEN SYMMETRIC KEY ssisdb_key DECRYPTION BY CERTIFICATE ssisdb_cert;

    DECLARE @pwd NVARCHAR(MAX);

    SET @pwd = (SELECT TOP 1 CONVERT(NVARCHAR,DecryptByKey(ssisdb_pwd)) FROM TEST.dbo.lookup_replica_role);

    DECLARE @sqlString NVARCHAR(1024);SET @sqlString = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @pwd +'''';

    USE SSISDB;

    SELECT @sqlString

    EXECUTE sp_executesql @sqlString;

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    END

    USE TEST;

    UPDATE dbo.[lookup_replica_role] SET [replica_role] = @current_role;

    --------------------------------------------------------------------------------------------------------

    Here is the issue:

    Please create a master key in the database or open the master key in the session before performing this operation

    However, I don't have any issues opening master key by running only below script with same password to both nodes and run SSIS packages. I can failover to any node and run this below script and it works just fine.

    USE SSISDB       OPEN master Key decryption by password = 'xxxxxxxxxx'

    ALTER Master Key ADD encryption by Service Master Key

    What I am missing here?

    Thanks much,

    Attopeu

Viewing 0 posts

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