Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Encrypting password column in T-SQL?

    Hi Folks,

    I'm trying to create a table in my database to contain user login info. In MySQL, there were some functions you could use to encrypt the data that went into the password column. Is there similar functions in T-SQL? If so, what is the most convenient way to encrypt the passwords stored in the DB and decrypt them when requested from an outside programming procedure (such as in ASP.NET)? Any suggestions would be greatly appreciated.

    Regards,

    Paul
    Paul Palubinski

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL Server 2005 and SQL Server 2008 have a lot of encryption options. You can start here for SQL Server 2008. Most of the concepts are the same between the two, but SQL Server 2008 expanded on the actual functions a bit.

  3. #3
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Thanks for the response. I just took a quick look at that article, but it seems to be what I need.
    Paul Palubinski

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    By the by, you shouldn't need to decrypt your passwords... You take the entered value, encrypt it, and then compare this derivation to the stored encrypted password.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, georgev, georgev, georgev. Some day, you will learn that the word "should" bites you more often than anyone else.
    Code:
    if not exists (select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
      begin 
    	create master key encryption by password = 'Here is a database master key!'
      end
    
    create asymmetric key UserKey with algorithm = RSA_512
    
    select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
    select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
    select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
    select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
    Now, encode it 50 more times on the blackboard, young man.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @i int
        SET @i = 1
    
    WHILE @i <= 50
      BEGIN
        PRINT EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
    
        SET @i = @i + 1
      END
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2009
    Posts
    2

    The CHECKSUM function does it simply.

    the code:

    SELECT CHECKSUM('my string')

    returns a number which is a one-directional encrytion of the given string.



    Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
    http://www.nobhillsoft.com

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, not really.
    And Good Lord, if you are going to do that then at least use BinaryChecksum.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I never thought of using checksum. Probably because the results of the function are not unique. Although, this would give you the ability to better guess the password, since there are multiple variations that will generate the same checksum, and therefore grant you access. Better yet, if you have access to the checksum column, you can make an educated guess as to the password.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I believe Peso reverse engineered sql server checksum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    I ended up using ENCRYPTBYASYMKEY() and in terms of awesomeness, it was the opposite of Colin Farrell's performance in the movie, Dare Devil. Thanks for all the assistance folks.
    Paul Palubinski

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by ppalubinski
    I ended up using ENCRYPTBYASYMKEY() and in terms of awesomeness, it was the opposite of Colin Farrell's performance in the movie, Dare Devil. Thanks for all the assistance folks.
    Best practice is not to encrypt passwords at all. Passwords should be hashed (use the HashBytes function). In that respect George was correct: hash the password and compare hashes, don't compare passwords.

    The only reason to encrypt a password is if you require recovery of the original password. Password recovery is itself a weakness and ideally passwords should be unrecoverable. Note that password complexity rules are also important to make sure the password can't be recovered using a password dictionary attack.

  13. #13
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    To be honest, I probably don't need to apply a hash or password checker for this project. I would be deeply honored if someone decided it was worthwhile to try crack the system we're working on. And I'm sure the achievement would be more disappointing than a Colin Farrell movie.
    Paul Palubinski

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Here's the problem however. If you implement password authentication with only trivial security and from which passwords are recoverable then two things will happen. Firstly, you will deceive legitimate users into thinking their data is secure. Secondly, a determined attacker may be able to identify a user and recover the user's password. Chances are that user has used the same password or derivations of it elsewhere - possibly for more sensitive or important data than your system holds. Determined attackers know this and may target the weakest-link systems to get at those passwords. So as a consequence of your system's weaknesses you could make other data vulnerable too.

    In my opinion that is why every system should implement best practice password management and the user should never be swindled by a system that looks secure when it actually isn't.

  15. #15
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Fair enough, but we determine the passwords for our clients and the data is our own data that they are just allowed to view, but I'll definitely look into using a hash algorithm to get into a good habit.
    Paul Palubinski

Posting Permissions

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