Obfuscating/masking data at column level

• ampliMax

  Old Hand

  Points: 362

  More actions

  February 4, 2017 at 7:12 am

  #401547

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

• Jeff Moden

  SSC Guru

  Points: 1004595

  More actions

  February 4, 2017 at 4:22 pm

  #1926741

  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)
  

• ampliMax

  Old Hand

  Points: 362

  More actions

  February 6, 2017 at 6:12 am

  #1926837

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

  Max

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  February 6, 2017 at 12:06 pm

  #1926937

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

• ampliMax

  Old Hand

  Points: 362

  More actions

  February 6, 2017 at 1:51 pm

  #1926965

  Thank you SSCarpal Tunnel!

  Max