Posted: 29 Mar 2013 12:44 AM PDT

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.aspxHere 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 @sqlStringEXECUTE sp_executesql @sqlString; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYEND 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 operationHowever, 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 KeyWhat I am missing here?Thanks much,Attopeu

Complex data to join rows from the same table

Posted: 28 Mar 2013 09:08 PM PDT

Hi AllI had a huge situations and at the moment only revolve by VB and XML, but is not a fast and easy way to force my users to do.Real world I receive data from several and diverse equipment and each of one insert on central table the following information.[b]Date/Time Insert Tag Device Value [/b]------------------- ---------- ----------- ----------03-28-2013 09:05 1305 125 1251,6903-28-2013 09:05 1305 126 1261,6903-28-2013 09:05 1305 127 1271,6903-28-2013 09:05 1305 128 1281,6903-28-2013 09:05 1305 129 1291,6903-28-2013 09:05 1305 130 1301,6903-28-2013 09:15 1305 125 1252,6903-28-2013 09:15 1305 126 1262,6903-28-2013 09:15 1305 127 1272,6903-28-2013 09:15 1305 128 1282,6903-28-2013 09:15 1305 129 1292,6903-28-2013 09:15 1305 130 1302,69I try several Joing {Inner / All / Out} but not with no sucess., using also a temporary table , but with VB I need a single output from data above decribe like this:[b]Data/Time D125 D126 D127 D128 D129 D130[/b]------------------ ---------- ---------- ---------- ---------- ---------- ------------03-28-2013 09:00 1251,69 1261,69 1271,69 1281,69 1291,69 1301,6903-28-2013 09:15 1252,69 1262,69 1272,69 1282,69 1292,69 1302,69I now is not so easy ask, but I thought I can bring this query directly from SQL Server.Any one can give some ideia to do this.Thanks Paulo Afonso:cool:

Question about new behavior of database selection combobox in SSMS 2012

Posted: 10 Sep 2012 05:08 AM PDT

Hey all,I have some questions about the new behavior of the database selection combobox in SQL 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL 2008 R2, clicking in the text of the database selected (as opposed to the dropdown arrow) selects the text in the combobox rather than activating the dropdown function.I find this sort of a nuisance, since the new intellisense isn't active in the combobox. I don't actually want to type out the name of the database since most of the databases have the same prefix in their name; that would be a lot of unnecessary typing.As such, I would prefer if clicking the text of the combobox would activate the dropdown rather than have it be available for text input. Does anyone know how I can do this?Also, if I use the Control + U keyboard shortcut to put the focus on the combobox, is there any key I can press to activate the dropdown so I can navgiate the available databases that way? I know pressing up and down will switch from one to another, but is there anyway to actually bring up the list?Can anyone recommend any options to alter the behavior of the combo box to make it more useful? I was considering using SSMSBoost; does anyone have any experience with it?I would appreciate any advice anyone can provide. Thanks.

Sql samples, sql tips

Friday, March 29, 2013

[SQL 2012] Master key issue with SSISDB and AlwaysOn Availablity Group

[SQL 2012] Master key issue with SSISDB and AlwaysOn Availablity Group

No comments:

Post a Comment

Search This Blog