SSIS 2012 - Catalog Encryption

Question

Post reply

SSIS 2012 - Catalog Encryption

Koen Verbeeck

SSC Guru

Points: 259115
More actions
April 26, 2012 at 11:48 pm

#392129

Comments posted to this topic are about the item SSIS 2012 - Catalog Encryption
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 27, 2012 at 2:08 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

M&M SSC-Insane Points: 21700 More actions · Answer 1

Didn't know about the answer, googled it 🙂

M&M

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Interesting question, thanks Koen!

I had it wrong because, like Mohammed, I had to google - and I found this reference that appears to contradict the reference Koen used:

In an Integration Services package, the following information is defined as sensitive:
(...)
* Any variable that is marked as sensitive. The marking of variables is controlled by Integration Services.
(...)
Also, for the protection levels that use a password, Integration Services uses the Triple DES cipher algorithm with a key length of 192 bits, available in the .NET Framework Class Library (FCL).

Can anyone who knows more about SSIS than I do (i.e.: something) comment on this apparent contradiction?

EDIT: Inserted quote in quote block for better readability

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Hugo Kornelis (4/27/2012)
Interesting question, thanks Koen!
I had it wrong because, like Mohammed, I had to google - and I found this reference that appears to contradict the reference Koen used:
In an Integration Services package, the following information is defined as sensitive:
(...)
* Any variable that is marked as sensitive. The marking of variables is controlled by Integration Services.
(...)
Also, for the protection levels that use a password, Integration Services uses the Triple DES cipher algorithm with a key length of 192 bits, available in the .NET Framework Class Library (FCL).
Can anyone who knows more about SSIS than I do (i.e.: something) comment on this apparent contradiction?

Never read that MSDN article, but I can see why it leads to confusion.

My question is about the encryption used inside the database engine (SSISDB) to encrypt parameter values. This is a new feature in SQL 2012 and somewhat equivalent to the protection level ServerStorage.

The protection levels however specify how sensitive data (so not only parameter values, but for example passwords in connection strings) are encrypted and this has nothing to do with the SSISDB (protection levels are more a feature of the legacy deployment model). Protection levels can be enforced on packages stored on the filesystem by Integration Services, so it is no way coupled to the encryption inside the SSISDB.

But I understand the confusion 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

Nice question Koen. Thanks!

And thanks for writing your own explanation for the answer! 😉

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Dana Medley SSCertifiable Points: 6764 More actions · Answer 5

Thomas Abraham (4/27/2012)
Nice question Koen. Thanks!
And thanks for writing your own explanation for the answer! 😉

+ 1 😀

Glad to see some more SSIS QotD's floating around there. Great job as always Koen.

Everything is awesome!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Great question!!! Glad to see some of the more obscure stuff from 2012. Makes the research more challenging since there is not much coverage of this stuff. Keep them coming Koen.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mtassin SSC-Insane Points: 23099 More actions · Answer 7

Thanks for the question, I learned something.... was confused because I hit the same google link that others above did and figured Triple DES was the trick.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Ken SSCoach Points: 17191 More actions · Answer 8

Thanks for the SSIS question. More please! 🙂

EL Jerry SSCertifiable Points: 7063 More actions · Answer 9

Good question, nice to see more 2012 QotDs here.

I also googled for possible answers and stumbled upon the following article:

http://msdn.microsoft.com/en-us/library/gg471508.aspx

It states the answer, but it made me think if it was only for "configuration values". Glad to find out it was right.

Thanks for your explanation, Koen.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 10

Thanks Koen. add me to the list of peeps enjoying the 2012 SSIS line of questions! cheers

Revenant SSC-Forever Points: 42467 More actions · Answer 11

It must have changed for 2012 - 2005 had Triple DES.

I did not check whether it is still true, and I got it wrong.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Stewart "Arturius" Campbell (4/27/2012)
This is a really good question, thanks Koen.
I'm happy to learn that ms have eventually upped the ssis security.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 13

Iulian -207023

SSCertifiable

Points: 7639

May 8, 2012 at 1:57 am

#1484354

nice question thank you

Iulian