Friday, May 11, 2012

Change SSIS 2012 Catalog DB encryption algorithm

Hi,

By default, when you install the SSIS catalog, the encryption algorithm is  AES_256..
You can read about the various configurable options here.. http://msdn.microsoft.com/en-us/library/ff878147.aspx

To change it, you'll need to get the database in a single user mode, then use the  "catalog.configure_catalog" SP to change it. Yes you'd use T-SQL to control that now...

you may read more about it here http://msdn.microsoft.com/en-us/library/ff878042%28v=sql.110%29.aspx

so the whole script would be..

select * from catalog.catalog_properties

ALTER DATABASE SSISDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

catalog.configure_catalog 'ENCRYPTION_ALGORITHM','AES_192'

select * from catalog.catalog_properties

ALTER DATABASE SSISDB
SET MULTI_USER;
GO

Please note that you need to be a SSIS_Admin or SysAdmin to be able to do that...

Hope that helps someone...