SSIS 2012 - Catalog Encryption

  • 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

  • Didn't know about the answer, googled it 🙂

    M&M

  • 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/

  • 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

  • This was removed by the editor as SPAM

  • 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

  • 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!

  • 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/

  • 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]

  • Thanks for the SSIS question. More please! 🙂

  • 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.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

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

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

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

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

  • 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

  • nice question thank you

    Iulian

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

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