encrypt password in sql server database

  • hi

    I practically hit the wall, I am new to sql and I want to know how can i store a password in the database by encrypting it.

    I want to use insert into a "table_name", then retrieve the password by using select form "table_name"

    New kid on the block

  • Sounds like two way column encryption

    This should help

    http://www.databasejournal.com/features/mssql/article.php/3922881/Column-Level-Encryption-in-SQL-Server.htm

  • Generally when encrypting a password you will use an algorythmn (spelling...) like SHA1

    The point of this is it is a one way encryption. When you come to validate it, you take the entered password and encrypt it using the same key and if the hash result matches then the right password is entered. It is theoretically possible for two passwords to give the same key the likelyhood is in the 1 in billions range.

    Why do you need to be able to unencrypt the encoded data?

  • And some examples for you:

    --Two way example

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

    -- EncryptByPassPhrase(@password,@DataToEncrypt )

    select EncryptedData = EncryptByPassPhrase('My$ecret|<ey', '123456789' )

    -- DecryptByPassPhrase(@password,@DataToDecrypt )

    declare @val varbinary(max)

    SET @val = 0x0100000084F9C2DF2BC4518DFDA73E9C054E709F4039E7B831FF695F12621BEC05509E34

    select convert(varchar(100),DecryptByPassPhrase('My$ecret|<ey',@val))

    --one way example: compare the hash:

    declare @val varbinary(max)

    SELECT @val = HashBytes('SHA1','MySecret Phrase')

    SELECT

    CASE

    WHEN @val = HashBytes('SHA1','MyOther Phrase')

    THEN 'True'

    ELSE 'FALSE'

    END

    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!

  • thank a lot

    New kid on the block

  • aaron.reese (9/13/2012)


    ...

    Why do you need to be able to unencrypt the encoded data?

    Interesting point - if you can't decrypt it, is there any argument for keeping it?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • If you can't unencrypt it, is there any point in keeping it

    Yes, an example is in my original post; where you convert another value and compare the results. Most website user logons (SSC for example) don't store your password. If you forget it they will set a temporary one, but they can't tell you yours.

    There are cases where you need to encrypt data and decrypt it again (e.g. stored credit card numbers) but it is a PITA because you have to decrypt it every time you want to display it in a form, on a report etc. But it does mean that you can't just select field from table if you get hacked. The only way to read the value is with the encryption secret key.

  • aaron.reese (9/13/2012)


    If you can't unencrypt it, is there any point in keeping it

    Yes, an example is in my original post; where you convert another value and compare the results. Most website user logons (SSC for example) don't store your password. If you forget it they will set a temporary one, but they can't tell you yours.

    There are cases where you need to encrypt data and decrypt it again (e.g. stored credit card numbers) but it is a PITA because you have to decrypt it every time you want to display it in a form, on a report etc. But it does mean that you can't just select field from table if you get hacked. The only way to read the value is with the encryption secret key.

    Just to add to Aaron's response. Make sure that if you use encryption you salt your "secret key". Not salting your encryption is dangerous, that is what allowed the recent crack at LinkedIn.

    Encrypt your passwords using a one-way hash with salt. Then when attempting to login, you calculate the same one-way hash and compare that to your stored value. If they match, you are good. If not, the password was incorrect. Actually website like SSC do store your password. In fact, any website that authenticates you stores your password. At SSC and most other sites the value is encrypted before it is stored. This is why it can't be recovered, it is a one-way hash. It is not stored in plain text.

    DO NOT STORE CREDIT CARD NUMBERS unless you are very very very certain that you know what you are doing. Storing credit card numbers is NOT something to take lightly. You are subject to massive repayments if the data you are storing is hacked. You must be PCI compliant and have evaluations once a year that your process is secure.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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