Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Master key issue with SSISDB and AlwaysOn Availablity Group Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 8:38 AM
Points: 50, Visits: 971
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



Post #1436929
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse