Post reply

EncryptbyPassPhrase

  • February 24, 2016 at 10:21 pm

    #325062

    Comments posted to this topic are about the item EncryptbyPassPhrase

  • February 24, 2016 at 11:04 pm

    This was removed by the editor as SPAM

  • February 25, 2016 at 6:35 am

    #1861029

    Interesting. At least they picked well. Thanks, Steve.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • February 25, 2016 at 9:24 am

    #1861133

    Thanks for the question. I didn't know that.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • February 25, 2016 at 10:48 am

    #1861171

    Lol, I just used this the other day to encrypt SSNs, other wise I would have had no idea. I had to come up with a way to store the SSNs securely, since I'm using them to reconcile a couple of different data sets from multiple systems. Good thing I did too, cause my boss sent one of the ERP admins around to make sure that I didn't have any floating around the DW :Whistling:

    One thing though, I currently am executing the ENCRYPTBYPASSPHRASE() function in an ETL via an Execute SQL task, which leaves the pass phrase in plain text. Does anyone know of a better way to do this? Passing the passphrase into the function as a variable is the first thing that comes to mind, but that still doesn't solve the problem of securing the passphrase.

    Any help would be appreciated.

    Thanks

  • February 25, 2016 at 10:58 am

    #1861178

    You have to secure this somehow. There isn't a good way to do this, other than injection from the package or as a parameter. In either case, you then need to treat that location as a security hole.

    I like a parameter of sorts, so operations can limit access to jobs, and then control the password themselves (and take liability).

  • February 25, 2016 at 11:15 am

    #1861186

    Steve Jones - SSC Editor (2/25/2016)

    You have to secure this somehow. There isn't a good way to do this, other than injection from the package or as a parameter. In either case, you then need to treat that location as a security hole.

    I like a parameter of sorts, so operations can limit access to jobs, and then control the password themselves (and take liability).

    I was thinking that password protecting the entire .dtsx package might also be a solution? I'm using Integration Services Catalog on SQL Server 2014. It should just change the protection level to server when I deploy it. Then only myself and people who have access to the SSIS catalog would be able to access the package, which is only myself and the sysadmin. Throw the package password in KeePass and then forget that I ever authored the thing, deny all knowledge of the events that transpired and refer any further inquiries to the online documentation which is ample.

    What say you O' Grand Wisdom Of the Hawaiian Shirt? Viable or did I swallow too much sea water during this morning's surf?

  • February 25, 2016 at 11:20 am

    #1861187

    That's fine. Package protection is good. The important thing is that someone knows where the password is and how it's controlled. I might even think about having a non-business person watch the password process and understand where it is. I used to give the admin passwords (sealed) to the CFO, just in case.

  • February 25, 2016 at 11:28 am

    #1861193

    My two bosses and the sysadmin have the KeePass master password. I'm pretty sure that each of them wrote it on a sticky, wrote "KEEPASS PASSWORD - IMPORTANT" over it, underlined that twice, and then pasted it directly to the bottom their monitors as soon as I was done verbally giving it to them over the phone. Maybe not the sysadmin.

    Alright, local package protection it is. Thanks as always for all your help Steve.

  • February 25, 2016 at 11:39 am

    #1861198

    Good luck, and you are welcome. If you'd like to write up how you to this one thing, protecting this data, we'd love an article.

    Steve

  • February 27, 2016 at 9:17 am

    #1861633

    Why would you be hiding data from the DBA's??Shouldn't be the other way around??

  • February 27, 2016 at 4:21 pm

    #1861656

    eje37575 (2/27/2016)

    Why would you be hiding data from the DBA's??Shouldn't be the other way around??

    There is certainly data you wouldn't want a sysadmin to see, nor should they necessarily see. A valid concern. DBAs can be responsible for the system, but not be allowed to see the data.

  • February 27, 2016 at 6:03 pm

    #1861664

    I guess it depends on the organization. I'm more of an sql developer/dba and have full access to production data. Our .NET developers do not have access production data they only have free reign when it comes to dev and qa.

  • February 28, 2016 at 3:51 am

    #1861679

    As I didn`t use that function before, so I googled it; found some confusing web links made me think it is useing DES algorithn as well.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • March 9, 2016 at 4:57 pm

    #1864156

    Thanks for the question.

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

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