March 29, 2013 at 7:44 am
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