Encrypt and Decrypt password in SQL

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  October 31, 2018 at 12:19 pm

  #380630

  In a winform vb.net apps, I used a function below to Encrypt password and save into USERINFO table.
  Is it possible to create a function in SQL to decrypt password or have to decrypt password through apps?

  Private Function Encrypt(clearText As String) As String
     Dim EncryptionKey As String = "MATY2SPBNI99878"
     Dim clearBytes As Byte() = Encoding.Unicode.GetBytes(clearText)
     Using encryptor As Aes = Aes.Create()
      Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D,
       &H65, &H64, &H76, &H65, &H64, &H65,
       &H76})
      encryptor.Key = pdb.GetBytes(32)
      encryptor.IV = pdb.GetBytes(16)
      Using ms As New MemoryStream()
        Using cs As New CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write)
         cs.Write(clearBytes, 0, clearBytes.Length)
         cs.Close()
        End Using
        clearText = Convert.ToBase64String(ms.ToArray())
      End Using
     End Using
     Return clearText
    End Function

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 31, 2018 at 12:34 pm

  #2011933

  Why do you want to decrypt them? Normally password should be stored in a hashed and salted method, so they can't be decrypted.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  Follow

  October 31, 2018 at 2:56 pm

  #2011963

  It's not a good idea to pass or return unencrypted passwords from a database function. Someone might intercept the call and read the password. You should only pass, store and retrieve encrypted passwords to a database.
  If the password is only needed for user verification, so needs only checked that it is the same one as saved, then the password should be a salted hash so impossible to decrypt.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 31, 2018 at 3:49 pm

  #2011969

  Never encrypt passwords. Salted hash, then the app hashes (with the same salt) and you compare the hashes.

  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