Obfuscating/masking data at column level

  • Hi everyone!
    My question is about security.
    In a DB (SQL Server 2012), I found an ACL table in which, unfortunately, user passwords appear in plain text (not crypted).

    I'll implement password encryption for sure, but at the time it would be great to have a quick and dirty solution.
    I'd like to obfuscate/mask the content of the password column without altering its content.
    That is, I'd like to make sure that
    - A select from SSMS shows masked passwords
    - All "external" queries (coming from the applications that consume the DB) keep on retrieving the content in clear, so that any existing data consumer does not see any difference

    Basically, I'd like to operate at server side avoiding, at least for this step, to modify the applications.

    I know SQL Server 2016 has dynamic data masking, which would solve my problem, but my actual DB is a 2012 one.

    Thanks in advance to anyone who wants to give me some tip. I even thought of injecting an assembly (.NET) into SQL Server...

  • To the best of my knowledge, there is no way to do this in 2012 and certainly no way to do any kind of obfuscation that someone with the right privs couldn't easily reverse.  One way hashes with a sash of salt or two are the best way to go for passwords.  I'd add some extreme urgency to this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Jeff.
    I'm planning to avoid the quick and dirty step and go with a PBKDF based solution.

    Max

  • How about something like below?  Since it involves renaming the table, naturally you may need to adjust the implementation details to avoid/lessen glitches in your specific environment:

    1) Create a new physical table with the identical structure of the existing table;
    2) Rename the existing physical table;
    3) Create a view with the original physical table name, but that returns blanks/masked data back for the password value;
    4) Change your internal code that deals with the password to refer to the new physical name.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you SSCarpal Tunnel!

    Max

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

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