locked
Master key issue with SSISDB and AlwaysOn Availablity Group RRS feed

  • Question

  • 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 KongDBA
    Thursday, March 28, 2013 9:07 PM

All replies

  • Hi KongDBA,

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.


    Maggie Luo
    TechNet Community Support

    Tuesday, April 2, 2013 8:54 AM
  • Hi,

    What if you run the query "OPEN MASTER KEY...." directly without using sp_executesql?


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, April 3, 2013 9:52 AM