August 24, 2010 at 6:48 am
How Do I Encrypt Datbase Column?
Can any body explain it in details, I am really new to this encyption concept
August 24, 2010 at 8:42 am
Here is a blog posting on encryption from SS2005 which still applies and is (in my opinion) a great walk-through on certificate based encryption (passphrase encryption is pretty straight-forward and good examples exist in BOL for that).
http://blogs.msdn.com/b/lcris/archive/2005/12/16/504692.aspx
August 25, 2010 at 1:31 pm
Of course this is only one way of encrypting data. It really depends on what you are trying to encrypt and why. For example, this method of encryption is not what you want for storing credit card numbers.
If you are wanting to encrypt the data to prevent other users from viewing the contents in plain text (or prevent dev or dba type people with database access) then this is probably the right path to follow.
If you are talking about storing login information like a users password you probably don't want to use this at all. Login information should use a one way hash function methodology rather than two way. You can read up on SHA and other hash function encryption methods as a starter.
Encryption, as it should be, is pretty confusing and as with anything related to sql server the answer is, "it depends". 😀
_______________________________________________________________
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/
August 25, 2010 at 3:28 pm
It depends, the old standby..
Depending on what you need to encrypt and the audience you need to keep it from defines a great deal about HOW and WHERE you do the encryption. Here are some examples.
1. Credit Card information. The PCI guidelines generally specify that the key to the encryption can't be stored in the same place as the encrypted data. This is to protect against a single server breach giving all the information away. This would be a 2-way encryption as well.
2. Payroll information. You probably don't want this available to anybody but specified people in HR. Like CC information you might have the key on a server that isn't the database server. This would be a 2-way encryption as well.
3. Passwords for login verification. If you roll-you-own authentication/authorization scheme, your passwords should not be able to be decrypted, period, you can use a hash function like sha-1 (or MANY others) to hash the value and store it. Then when you verify the login you hash the passed value the same way and compare hashes. If they match, they are the same password. Keep in mind some hash methods CAN have collisions where two or more values can generate the same hash, however, in practice, this is not likely to have much effect.
4. Passwords for external connections. If you need to store passwords that are passed to other systems for authentication then you need a two-way method, like AES-256. You would likely store the keys locally and severely limit access to them.
I'm sure there are many more but this is just what came to mind.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply