1

Is there a feature in SQL Server that makes the values we give for "passwords" encrypted/encoded? If not, what is the simplest way to encode my passwords and store them in a table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SriKrishnaChaitanya Vinnakota
  • 51
  • 2
  • What version of SQL you using? – Xedni Jul 13 '17 at 16:06
  • 4
    The most common practice is to salt/hash passwords in the application and store the hash in the database. Although this could be done in SQL Server too, that would require passing the clear password to SQL Server, which would be an issue if the communication channel isn't encrupted. – Dan Guzman Jul 13 '17 at 16:07
  • It is SqlServer 2014 – SriKrishnaChaitanya Vinnakota Jul 13 '17 at 16:08
  • To make my question even simpler, if I insert actual password in the insert query, is there a feature that encodes the password I entered and store in corresponding table? – SriKrishnaChaitanya Vinnakota Jul 13 '17 at 16:10
  • If you're worried about the password in transit between the data source and the database, you'd have to encrypt it at the source. However once it gets to the database, just hash the password using something like SHA2_256 or SHA2_512 along with an appropriately long salt. – Xedni Jul 13 '17 at 16:14
  • 2
    @SriKrishnaChaitanyaVinnakota not with a basic `INSERT` query, no. You can encrypt the data at rest (TDE), but anyone with access to the table will still be able to read the data. You need to be salting and hashing the password at the *source*, before the data gets anywhere near the database. Then insert that salted/hashed value into the database. TL;DR: passwords should be in cleartext for as brief a time as possible, and passed in the clear between as few "components" of your system as possible. – alroc Jul 13 '17 at 16:17
  • A good SO post on the subject: https://stackoverflow.com/questions/1219899/where-do-you-store-your-salt-strings – Xedni Jul 13 '17 at 16:19
  • @SriKrishnaChaitanyaVinnakota, in SQL Sever 2016, you can encrypt data both in motion and at rest using Always Encrypted. See https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine. It's still better to only store salted hashes for passwords, though. – Dan Guzman Jul 13 '17 at 20:47
  • **Do not encrypt passwords**, when the attacker gets the DB he will also get the encryption key. Just using a hash function is not sufficient and just adding a salt does little to improve the security. Iterate over an HMAC with a random salt for about a 100ms duration and save the salt with the hash. Use a function such as `ehash`, `PBKDF2`, `Bcrypt` or similar functions. The point is to make the attacker spend a lot of time finding passwords by brute force. – zaph Jul 13 '17 at 21:31
  • @DanGuzman and Xedni See above comment, hashing with a salt is not secure, that concept hasn't been secure for a couple of decades.. – zaph Jul 13 '17 at 21:33
  • @Xedni No, do not encrypt the password at the source, that is not secure and the result just becomes the password. In order to securely transmit the password use HTTPS, that encrypts everything except the host URL. – zaph Jul 13 '17 at 21:35
  • Hey @Zaph, I'm having a little trouble distinguishing between "simple salting and hashing" and the more elaborate method you described. At the end of the day, dont both create a ridiculously one-way garbled message which is next to impossible to rainbow attack? – Xedni Jul 14 '17 at 02:04
  • Yes both create a "one-way garbled message" but it is how fast that is the key. The issue is how much CPU time the function takes, it is the time factor that protects against brute force attacks. A password verifier should consume about 100ms of CPU time, just a hash with salt is more like 1us, a 100,000 improvement in attack resistance. This is achieved by requiring a substantial number of iteration. NIST (National Institute of Standards and Technology) currently recommends PBKDF2 for password verifiers. – zaph Jul 14 '17 at 02:34
  • Password verifier attacks typically use a list of frequently used passwords ordered by frequency and with up to 10 million entries and add to this fuzzing by common attack code. For more information see [Infosec](http://resources.infosecinstitute.com/10-popular-password-cracking-tools/) and [SecLists](https://github.com/danielmiessler/SecLists/tree/master/Passwords). This should convince that just hash protected passwords provided essentially no security. – zaph Jul 14 '17 at 02:40

1 Answers1

-1

There is a function called PWDENCRYPT that will help you do this

MBurnham
  • 381
  • 1
  • 9
  • You can't see it because of your rep but that was already an answer that was deleted. The issue (by Xendi): From MSDN: "PWDENCRYPT is an older function and might not be supported in a future release of SQL Server. Use HASHBYTES instead. HASHBYTES provides more hashing algorithms. ". And do not simply encrypt the password without salting it. TO not do so opens up the data to a myriad of attack vectors. – zaph Jul 13 '17 at 21:37