Post reply

need to encrypt and store passwords and store in urser table

  • January 4, 2012 at 8:26 am

    #262354

    Does sql server 2005 offer some sort of prepackaged encrtpion? Or should i just make my own and store it as a function?

  • January 4, 2012 at 8:45 am

    #1428384

    yes, there's a suite of optiosn available starting in SQL 2005.

    this is one of my favorite references for getting started in 2005 encryption:

    http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

    the simplest to use is EncryptByPassPhrase:

    --http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    -- EncryptByPassPhrase(@password,@DataToEncrypt )

    select EncryptedData = EncryptByPassPhrase('PEPE', '123456789' )

    -- DecryptByPassPhrase(@password,@DataToDecrypt )

    declare @val varbinary(max)

    SET @val = 0x010000004A298120E629F7F4DC4AD09EB0E380C9E60040BDAE1DB6D8DA98C2A4D249EB71

    select convert(varchar(100),DecryptByPassPhrase('PEPE',@val))

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 4, 2012 at 9:42 am

    #1428450

    If you don't need to recover the password, just compare against it and reset if you don't remember it, then use a one way hash. There are the HASHBYTES function that can help here:

    http://msdn.microsoft.com/en-us/library/ms174415.aspx

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 4, 2012 at 9:48 am

    #1428463

    Don't encrypt passwords. There is absolutely no need to store a password encrypted. Hash (hashbytes) it instead (make sure you use a properly salted hash), then there's no chance that someone can somehow decrypt them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2012 at 9:52 am

    #1428471

    trust me i'm w/ you. Especially what this is for. Some nickle and dime content managment system being used by 10 people max. Not my call though. The person defining the specs is a front end guy and that's what he wants. couldnt talk him out of it. I suppose he just sees something fundementally wrong w/ storing passwords as is in the db w/o encrypting them.

  • January 4, 2012 at 9:56 am

    #1428480

    So store them hashed and tell him they're encrypted. It's not a big lie.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2012 at 10:02 am

    #1428488

    GilaMonster (1/4/2012)

    So store them hashed and tell him they're encrypted. It's not a big lie.

    And just include a password reset system. If he complains, bill him again to change the encryption, or offer to. He might just leave it.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 4, 2012 at 10:20 am

    #1428503

    i see. Thanks guys.

  • January 4, 2012 at 1:41 pm

    #1428641

    GilaMonster (1/4/2012)

    So store them hashed and tell him they're encrypted. It's not a big lie.

    LOL, that's a good idea.

  • January 4, 2012 at 2:16 pm

    #1428674

    To be honest i dont think he cares if it's hashed or encrypted. I think he just doesnt want it stored verbatum.

  • January 4, 2012 at 2:27 pm

    #1428690

    So how do i unhash it?

  • January 4, 2012 at 2:27 pm

    #1428691

    I'm fully in agreement it shouldn't be plain text, but encrypted is also not good for passwords (especially if using the database encryption which any db_owner or sysadmin can decrypt). One way salted hashes are the recommended way to store passwords.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2012 at 2:30 pm

    #1428695

    captcooldaddy (1/4/2012)

    So how do i unhash it?

    From my understanding of how that would work, you wouldn't. If you wanted to see if a typed password matched what was hashed you would hash the users input and then check to see if the hashes matched.

  • January 4, 2012 at 2:33 pm

    #1428697

    lol. Brilliant.

  • January 4, 2012 at 2:40 pm

    #1428700

    captcooldaddy (1/4/2012)

    So how do i unhash it?

    You don't that the whole point.

    The encryption algorithms are nondeterministic, you encrypt a value 3 different times and you get 3 different results. Hence, to compare a value with an encrypted value, the encrypted value has to be decrypted.

    The hash algorithms are deterministic. Hash a value 3 different times and you get the same value 3 times. Hence to compare a value to a hashed column, you can hash the input and compare the two. This is an advantage for passwords because it means that the plaintext password is neither stored, nor can it be retrieved, so there's no way that someone can extract the passwords.

    You do have to salt the hashes, otherwise you're vulnerable to the rainbow tables exploit.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

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