Hiding "password" data in database?

_{Thursday, January 13, 2011 8:47 PM}

Hi,

As an application developer I have a question about security, namely the password column (field) in the database.

As usual, "top management" has a higher level of privilege as compared to "application developers". And, this brings a security issue like:

Some piece of data may be very private to the "top management", and may not be open to "application developers".

On windows login forms, we use any kind of password field to show up with invisible characters, like "*".

However, as the application saves the data (the password data) in the "Users" table, and if some one (eg. an "application developer") queries the database, the password field shows up with visible characters again -- and reveals the passwords of "top management" -- making the whole unsecure.

Some can come up with a solution like: "Why not to do some number cruching and encrypt the password and saving to the database after?"

But then, as an application developer, I would be the one to encrypt the password. So, i can retrieve the password (de-crypt) by doing some back-engineering.

Unsecure again!...

Is there a data type like "password field" in SQL Server, so it would not show the characters in that column?

Any ideas?

Thanks in advance.

_{Thursday, January 13, 2011 9:12 PM ✅Answered | 1 vote}

There are no datatypes like 'password' in SQL server ,the option that exists in SQL server to solve your requirement is to use ENCRYPTION for a specific column (password column in your case) in table.

http://www.mssqltips.com/tip.asp?tip=928
http://msdn.microsoft.com/en-us/library/ms345262.aspx

Thanks, Leks

_{Friday, January 14, 2011 11:49 PM ✅Answered | 1 vote}

Some can come up with a solution like: "Why not to do some number cruching and encrypt the password and saving to the database after?"

But then, as an application developer, I would be the one to encrypt the password. So, i can retrieve the password (de-crypt) by doing some back-engineering.

A best practice for passwords is to store a hash of the password value (perhaps salted).  A hash is one-way and cannot be converted back to the original value.  To verify passwords, compare the hash of the user-supplied value to the hashed password value in the database. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

_{Thursday, January 13, 2011 11:35 PM | 1 vote}

Our intended solution for high security needs is to use SQL Server 2008 Enterprise Edition with SQL Server Audit. Then the sysadmin could view the password value (which might sometime be necessary), but such access would be audited. And if the audits are ported off box, and only available to some responsible 3rd party (an access auditor), any inappropriate admin access would be discovered and the sysadmin could be disiplined for violating policy. This solution preserves emergency access (e.g. somebody gets hit by a bus) but leaves a forensic audit trail of the access. That might be excessive for your situation.Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

_{Friday, January 14, 2011 1:06 PM}

Is it feasible for your application to use Windows authentication?

You could create two windows groups; let's call them MyDomain\Chiefs and MyDomain\Indians. You then add your users' windows accounts to these groups as appropriate. You can create logins for these windows groups.

You then create database roles db_Chiefs and db_Indians in your database, and assign the logins associated to the windows groups to these roles.

Finally, you grant appropriate permissions to each role on the objects in your database.

_{Friday, January 14, 2011 11:24 PM}

HowIs it feasible for your application to use Windows authentication?

You could create two windows groups; let's call them MyDomain\Chiefs and MyDomain\Indians. You then add your users' windows accounts to these groups as appropriate. You can create logins for these windows groups.

You then create database roles db_Chiefs and db_Indians in your database, and assign the logins associated to the windows groups to these roles.

Finally, you grant appropriate permissions to each role on the objects in your database.

How about sysadmin then ? He will have access to all data in your SQL instance thats why we have recommended him to use encryption for specific columns
Thanks, Leks