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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy