| Author |
Topic |
|
mpooley
Starting Member
13 Posts |
 Posted - 2012-02-04 : 08:53:29
|
| Simple encryption problem re-passwordI have been reading a simple tutorial online about encryption of a column in a table.I followed all the instructions on a test project and everything worked ok.My problem is that the example code to decrypt the column did not contain any password'So even though a password was created E.G.USE EncryptTestGOCREATE MASTER KEY ENCRYPTION BYPASSWORD = 'SQLAuthority'GOUSE EncryptTestGOCREATE CERTIFICATE EncryptTestCertWITH SUBJECT = 'SQLAuthority'GOlater on it is decrpyted without a password usingUSE EncryptTestGOOPEN SYMMETRIC KEY TestTableKeyDECRYPTION BY CERTIFICATE EncryptTestCertSELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol) ) AS DecryptSecondColFROM TestTableGOI am obviously missing something really basic here.I can see it is using the Certificate to decrypt but surely if the certificate is in the DB then so could someone else without knowing the password.I have tried contacting the guy who wrote the tutorial but with no luck.can anyone here help me please?thanksMikeMichael D Pooley |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-04 : 09:38:31
|
| Well the first problem is that the tutorial's teaching you the wrong approach. Passwords should not be encrypted as there is no need to ever decrypt them. They should be stored hashed (salted hash) and checked by comparing the hashed valued.And yes, you're right. Anyone who has permission to open the key can decrypt anything that was encrypted with that key. A sysadmin has all permissions in the DB, hence can decrypt any encrypted data (except EncryptByPassPhrase when done properly)--Gail ShawSQL Server MVP |
 |
|
|
mpooley
Starting Member
13 Posts |
Posted - 2012-02-04 : 11:24:11
|
| Ah! So I shouldn't use this approach at all then?i don't suppose you could point me to a decent tutorial then ?thanksMikeMichael D Pooley |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-04 : 11:37:30
|
| Depends what you're looking for. Are you looking specifically for recommended practices for password storage? Are you looking for a primer on encryption in general?The sample you posted is the way you encrypt a column in a database, it's just a poor approach for passwords specifically. Sensitive data that does need to be retrieved in its unencrypted form, yes (maybe, depends who you are protecting against)--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-04 : 13:31:08
|
Quality advice, as always, Gail  |
|
|
|
mpooley
Starting Member
13 Posts |
Posted - 2012-02-04 : 18:58:45
|
| to be honest i don't know enough to know what i need to know IYSWIMI do want to encrypt just 1 column at the moment but I also want to learn the basics of encryption at least.thanksMikeMichael D Pooley |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-05 : 03:09:27
|
| If the column you want to "encrypt" is a password then you don't actually want encryption, you want SALT + HASH (a form of encryption which cannot be decrypted). You SALT + HASH the password when the user registers, and store that in the database, and when they login you SALT + HASH the password they typed and compare that against what you have stored in the database. There is a good Wikipedia article on SALT & HASHIf you want to store a credit card number, or a national security number, say, then you need Encryption - because you will need to decrypt the data at some later point. I think your best option is to encrypt that data in the application, because it will then travel securely from APP to Database, but if that's not practical SQL can do the job at the Database end. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 06:35:34
|
| What roughly does that column store?--Gail ShawSQL Server MVP |
|
|
|
mpooley
Starting Member
13 Posts |
Posted - 2012-02-05 : 06:47:51
|
quote:
Originally posted by Kristen
If the column you want to "encrypt" is a password then you don't actually want encryption, you want SALT + HASH (a form of encryption which cannot be decrypted). You SALT + HASH the password when the user registers, and store that in the database, and when they login you SALT + HASH the password they typed and compare that against what you have stored in the database. There is a good Wikipedia article on SALT & HASHIf you want to store a credit card number, or a national security number, say, then you need Encryption - because you will need to decrypt the data at some later point. I think your best option is to encrypt that data in the application, because it will then travel securely from APP to Database, but if that's not practical SQL can do the job at the Database end.
Michael D PooleyThanks That info about the password is very useful. I will definitely need a password and that is something i didn't know.The info to be stored is Bank account stuff - login details etcThat includes Passwords though!thanks |
|
|
|
mpooley
Starting Member
13 Posts |
Posted - 2012-02-05 : 06:54:17
|
quote:
Originally posted by GilaMonster
What roughly does that column store?--Gail ShawSQL Server MVP
Bank account numbersweb log in numbers and passwords etcthanksMikeMichael D Pooley |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 08:11:43
|
| Login names don't usually get encrypted, because you have to filter for them (and filtering on encrypted values is hard). Passwords, do some reading on hashes (hash_bytes in SQL) and salting of hashesAccount numbers sounds like it'll be reversible encryption, so probably the way the tutorial showed, unless you need to protect them against the DBA as well, in which case you'' have to do the encrypting outside of SQL.Unfortunately there's no such thing as 'basics' when it comes to encryption. It's a complex field, requires lots of thought and design and risk analysis.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-05 : 09:01:53
|
| For passwords:Register: provide UserID and new password, use SALT and HASH and store the passwordLogin: Provide UserID and password. SALT and HASH the password provided and compare that against the stored value.As explained above However ... you probably also need to do Lost Password. You obviously can't send the original password, as that has been one-way-encrypted. You can provide a temporary password instead (and store that in the database, presumably one-way-encrypted too), but if they ask twice and you generate two, different, one-time passwords the user may be confused and keep trying the wrong one.You may also want to have a time limit on how long the one-time password is valid for (also stored in the registration record)and the user may remember their original password, so you may want to still allow that, as well as the temporary password.You may want a limit on the number of failed attempts that the user can make (and then how do you unlock the account?). You might send the user an email "You account has been locked until XXX because of multiple failed password attempts; this is for your security to stop dictionary-attacks etc etc etc" because if you do not do this the user will wonder why their real password (which they have now remembered, or found the piece of paper it was written on ...) won't work either.Multiple-attempt locks need to apply to the Forgotten Password route too (otherwise I will be able to hack in with dictionary attack by repeatedly asking for Lost Password)You don't have to implement all this, of course!, I only mention it so you have some ideas.Next time you register on a website, whose data is of moderate importance to you, do a "lost-password" and see what happens. If they tell you your original password then they are storing it in plain text (or decryptable form, which is not much better), and thus your account is not highly secure. If this matters to you go somewhere else instead! |
|
|
|
mpooley
Starting Member
13 Posts |
Posted - 2012-02-05 : 18:06:54
|
| Thanks to you bothI will have to take a bit more time to study this. looks like it's a lot more involved than I thought.thanks for your adviceMikeMichael D Pooley |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 20:19:38
|
| If you're willing to spend some money, there are two books that I can recommend that include some stuff on encryption.Securing SQL Server by Denny CherryExpert SQL Server 2005 development by Adam Machanic (Do not get the 2008 version)Both have a chapter on encryption.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 20:25:34
|
quote:
Originally posted by Kristen
You may want a limit on the number of failed attempts that the user can make (and then how do you unlock the account?).
The other option is to add a small delay after a certain number of failed logins, and increase the delay as the number of consecutive failed logins increases. Dictionary attacks work by literally throwing the dictionary at the problem and trying different words with a specific account until one works (btw, in an average system probably 40% of accounts will fall to a dictionary attack if one is used)If the attacker has 10 000 words to try (a small dictionary) and the delay in returning success failure increases by 1 second after every 5 failed logins, then after 250 words he's waiting almost a minute for the login to fail per word. After 2000 words, 6 and a half minutes. Attackers aren't going to sit through that.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-06 : 03:10:12
|
| That's a good idea, that feedback is instant to a real use who has just been klutzing with attempting to remember their password.We've been working on the basis of not disclosing to the user what the nature of the problem is (so we specifically do NOT say "That EMail address does not exist" or "That password is wrong", or "You have had too many tries and your account is locked" ... because that gives some information to the hacker, we just say "Incorrect login details"But maybe saying "Too many consecutive attempts, your account is locked please retry in 1 minute" ... "... 2 minutes" ... solves the problem of the user wondering why a real password is not working. |
|
|
|
|
Simple encryption problem re-password