PASSWORD

Question

PASSWORD

sahoong

SSCrazy

Points: 2545
More actions
January 19, 2009 at 7:15 am

#217596

The Gurus,
Could you please, tell me how to hide the content of a password column in the database table? I want the content to show as xxxxxxxxxx instead of mypassword for example.
Many thanks in advance.
Regards,
Sahoong.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

You can look up Encryption on BOL. That would be the best. Or let your App handle the encryption and save the encrypted data in the password Field. And decrypt in the application layer.

-Roy

sahoong SSCrazy Points: 2545 More actions · Answer 2

sahoong

SSCrazy

Points: 2545

January 19, 2009 at 7:51 am

#929233

Thanks so much Roy. I'll do that.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

If you don't need to show the password ever, you can use a one way hash with pwdencrypt()

http://blogs.msdn.com/lcris/archive/2007/10/31/sql-server-undocumented-password-hashing-builtins-pwdcompare-and-pwdencrypt.aspx

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Aren't there about 10 million articles on how to break pwdencrypt() ?

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Roy Ernest (1/19/2009)
You can look up Encryption on BOL. That would be the best. Or let your App handle the encryption and save the encrypted data in the password Field. And decrypt in the application layer.

We do that for CC #s with the xp_crypt product.

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 6

If you use a decent one way encryption routine, you never need to decrypt the passwords. Just use the same encryption routine when the user enters their password, and if the encrypted result matches the value stored in the table, the user is authenticated. There's lots of good information on this on the net, and I don't think it's that hard to write a routine using a decent hashing algorithm to do this.

We get around the whole password issue in our apps by using the LAN ID to drive everything. This isn't really an option when you have external parites accessing an app, say on a website, but for internal apps it's definitely the way to go. Eliminates the need to have users change passwords periodically for the app, as they have to change them on the LAN. Much harder to spoof as well, assuming users keep their LAN ID passwords reasonably secure (most of our users are authenticated on the LAN with smart cards, so there's even less chance of spoofing).

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Ross McMicken (1/20/2009)
If you use a decent one way encryption routine, you never need to decrypt the passwords. Just use the same encryption routine when the user enters their password, and if the encrypted result matches the value stored in the table, the user is authenticated. There's lots of good information on this on the net, and I don't think it's that hard to write a routine using a decent hashing algorithm to do this.
We get around the whole password issue in our apps by using the LAN ID to drive everything. This isn't really an option when you have external parites accessing an app, say on a website, but for internal apps it's definitely the way to go. Eliminates the need to have users change passwords periodically for the app, as they have to change them on the LAN. Much harder to spoof as well, assuming users keep their LAN ID passwords reasonably secure (most of our users are authenticated on the LAN with smart cards, so there's even less chance of spoofing).

I beleive he is talking about Customer passwords. Not the User passwords.

-Roy