Post reply

Encryption by Pass Phrase

  • November 7, 2017 at 7:16 am

    #341201

    Afternoon All,

    Just got a requirement dropped on me to encrypt some data that is older than N days. I wanted to use EncrypyByPassphrase for this, but I need to keep the data in the current column.

    Is it poor form to cast to a varchar the varbinary output of EncryptbyPassphrase and update the DB with that? Is it possible? Am I missing something?

    I presume data length might potentially be a problem?

    How would one encrypt in place normally?

    Cheers
    Alex

  • November 7, 2017 at 7:49 am

    #1966997

    It's very unlikely that you'll be able to retain the same value in the same column. For example, an 11 character string I just converted into an encrypted varbinary came out as 138 characters long. That's just for 11 characters, so if you have longer strings they'll likely be longer.

    varbinary and (n)varchars are very different data types. If you need to encrypt it for older records, I'd suggest using an different column, and making both columns nullable. then store the data in the relevant column.

    edit: I suppose you could convert it back after converting it to a varbinary, but you column will HAVE to be a nvarchar, it cannot be a varchar.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 7, 2017 at 10:53 am

    #1967027

    Cheers Thom.

    Fortunately our security man just picked holes in their proposed solution anyway, so its all going back to the drawing board for something less cobbled together.

    It's probably going to all end up in archive tables that have encrypted varbinary columns that get purged every 30 days or so. The details are sketchy but as we cannot modify the client application in anyway this is going to be the safest approach.

Viewing 3 posts - 1 through 2 (of 2 total)

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