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