Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: how to encrypt a single field like a password field

    without writing code in my application? Does SQL Server have stored procedure to do it?

    Any help is appreciated.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL Server 2005 has support for encryption, but you have to manage keys, and write your own stored procedures that use the encrypt and decrypt functions. SQL 2000 does not have native support for encryption, I believe.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Attached is function that is suitable for encrypting passwords.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Dec 2005
    Posts
    28

    Thanks a lot blindman.

    Quote Originally Posted by blindman
    Attached is function that is suitable for encrypting passwords.
    I have downloaded the code and will take a look at it.

  6. #6
    Join Date
    May 2006
    Posts
    5

    Use PWDENCRYPT

    Theres an inbuilt function in SQL2000:

    column type must be:
    Code:
    Declare PWCol varbinary(256)
    to insert/update use:

    ...
    Code:
    CONVERT(varbinary(256), PWDENCRYPT('THEPASSWORD'))
    and to compare a password...

    ...
    Code:
    where PWDCOMPARE('thepassword', PWCol) = 1
    Cheers,
    Phil
    ---
    Always remember that you're unique, just like everyone else.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    a quick google search on this undocumented function gives you results on how to hack it. PUBLIC KEY ENCRYPTION is the safest bet. It's been a while since I have done this (4 years?) but I used the RSA cypher.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.
    Not true. The encryption algorithm I gave is a "one-way" algorithm. It cannot be unencrypted, and thus is only suitable in limited situations such as password encryption. It is relatively easy to make secure one-way encryption schemes.
    The challenge is to make a secure "two-way" encryption algorithm. SQL Server's built-in encryption is "two-way" but is not secure and was hacked years ago, and the decryption method is readily available on the web.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Duplicate post.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2006
    Posts
    5

    Unhappy Paranoid

    a quick google search on this undocumented function gives you results on how to hack it.
    Blimey you boys do love to p!$$ on someone’s fire.

    It's only hackable if your front end code is crap and you don't parse throu before SQL.
    If you're stupid enough to leave your SQL server open to access then the fact you can hack a password in a table is pretty irrelevant when you can get control of the whole box.

    Right, I'm off to sulk in the corner.

    ...

    To err is human, to forgive is not our Policy.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whoa, Mr. Sensitive! You're gonna need thicker skin than that!

    And no sulking, either. If you think we're full-o-crap, then just say so (but without throwing all tact to the wind...).

    P!$$!ng on someone's fire: allowed.
    Sulking in the corner: frowned upon.
    P!$$!ing in the corner: well, when ya gotta go....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2006
    Posts
    5
    Well at least I now know why my sulking corner is starting to smell so bad.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We generally do our sulking and grousing in the Yak Corral. You can join us there:
    http://www.dbforums.com/showthread.p...89246&page=289
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Wy not to use the in-built function encrypt() ?
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is an undocumented function which may not be supported, or may use a different algorithm in future releases.

    The algorithm has changed through releases in the past, rendering whole databases inaccessible for applications that relied upon it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •