Encryption in SQL Server 2005

I have three questions that I have not been able to find answers for.
Maybe this is because they are very obvious to everyone but me. Any
help would be greatly appreicated.
1. Are keys and certificates restricted (or can be restricted) at the
user/schema level? I know that tables can be restricted but if I have
a table with two encrypted columns (one by key A and one by Key B) and
group 1 and group 2 both have access to the table, I want goup 1 to
read the data encrypted by key A and not key B and vise versa for group
2.
2. I have a database with encrypted data that is replicated to a
reporting server and DR server. How does that work with the keys? Can
they be exported and imported on the servers in question and be OK?
Same question for restores.
3. And now the really dumb one. If I have a table with column B
encrypted, can I do a "Select DecryptByKey(ColumnB) From TableA" as
long as I have opened my key correctly? When I try this I either get
junk, NULL, or the first letter of the cleartext and that is it.
Links to articles or blogs would be greatly appreicated!!!!
Jim YoumansI found the answer to #3. Thanks!
Jim|||1. Key usage can be restricted via permissions. Look in Books Online at the
Permissions sections. I also have demos on my blog that illustrate this, for
example: http://blogs.msdn.com/lcris/archive...16/504692.aspx.
2. Replication does not replicate keys. So you will need to share the keys
on the servers, which means you need to create keys on both servers.
Certificates can be backed up and keys can be created with special
parameters (KEY_SURCE, IDENTITY_VALUE) that allows recreating the same key
if the same parameters are used. See
http://blogs.msdn.com/lcris/archive...14/481434.aspx.
3. You need to cast after decryption to the original data type.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1144963477.749189.188960@.i39g2000cwa.googlegroups.com...
>I have three questions that I have not been able to find answers for.
> Maybe this is because they are very obvious to everyone but me. Any
> help would be greatly appreicated.
> 1. Are keys and certificates restricted (or can be restricted) at the
> user/schema level? I know that tables can be restricted but if I have
> a table with two encrypted columns (one by key A and one by Key B) and
> group 1 and group 2 both have access to the table, I want goup 1 to
> read the data encrypted by key A and not key B and vise versa for group
> 2.
> 2. I have a database with encrypted data that is replicated to a
> reporting server and DR server. How does that work with the keys? Can
> they be exported and imported on the servers in question and be OK?
> Same question for restores.
> 3. And now the really dumb one. If I have a table with column B
> encrypted, can I do a "Select DecryptByKey(ColumnB) From TableA" as
> long as I have opened my key correctly? When I try this I either get
> junk, NULL, or the first letter of the cleartext and that is it.
> Links to articles or blogs would be greatly appreicated!!!!
> Jim Youmans
>|||It's really a good idea to go through Laurentiu's blog if
you are venturing into encryption. I think most questions
I've seen posted on encryption are answered in the blog.
It's already been posted but the site is:
http://blogs.msdn.com/lcris/
Check the demos - they are very well done and give you a
good "hands on" understanding of encryption in 2005.
-Sue
On 13 Apr 2006 14:43:47 -0700, "Jim Youmans"
<jdyoumans@.gmail.com> wrote:

>I found the answer to #3. Thanks!
>Jim|||I have been reading Laurentiu's blog for the last two days. I have not
gotten all the way through it, but I will. Thank you so much and you
have a vantasic blog Laurentiu.
Jim

Encryption Best-Practices

Please forgive me if I have overlooked a thread that answers this question, but I assure you that I have looked.

I would really appreciate a guide of sorts that would tell me the correct steps to take to properly secure a column in my database. I don't need specifics on how to do each step, I either have those already or can find them myself. In fact, I have already successfully encrypted and decrypted some data. I just want to make sure that I create the right keys and certificates and that I follow best-practices as far as backups and stuff is concerned.

Thanks,

Todd Sparks

Here are some suggestions:

- if you will need to share the same encryption key across databases or servers, make sure that you create the key using the KEY_SOURCE and IDENTITY_VALUE parameters.

- if you protect a key using a password, pay special attention to how the password is manipulated by your application and make sure it doesn't leak out. Do not hardcode passwords in your application - have the user enter them instead.

- use strong encryption algorithms. We recommend AES if you only work with Windows 2003. If you need to decrypt on other OS's, then use TripleDES. Avoid RC4.

- open the encryption keys when you need to use them and close them after you are done using them.

Thanks
Laurentiu