February 4, 2017 at 7:12 am
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...
February 4, 2017 at 4:22 pm
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
Change is inevitable... Change for the better is not.
February 6, 2017 at 6:12 am
Thank you Jeff.
I'm planning to avoid the quick and dirty step and go with a PBKDF based solution.
Max
February 6, 2017 at 12:06 pm
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".
February 6, 2017 at 1:51 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy