Re: Encryption in SQL Server 2005

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/2005/12/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/2005/10/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@xxxxxxxxx> wrote in message
news:1144963477.749189.188960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Security Permissions revert to default
    ... BLOG --> http://blogs.dirteam.com/blogs/jorge/default.aspx ... don't disable replication for too long! ... on most of my profiles the permissions ...
    (microsoft.public.windows.server.active_directory)
  • Re: Forcing replication after tombstone cleanup
    ... BLOG --> http://blogs.dirteam.com/blogs/jorge/default.aspx ... I have inherited a network where the replication was a real mess and several ... servers were not replicating properly. ... Also if I run repadmin /showreps I get: ...
    (microsoft.public.windows.server.active_directory)
  • Replication and permisions
    ... I have some troubles with replication and permissions. ... I have two SQL ... Servers on different phisical locations. ...
    (microsoft.public.sqlserver.replication)
  • RE: Global Katalog defekt
    ... also im Active Directory ist der Haken bei GC gesetzt. ... Identifying all servers. ... Active Directory LDAP Services Check ... The replication generated an error: ...
    (microsoft.public.de.german.win2000.active_directory)
  • Re: How to stop two servers in different sites trying to replicate with each other
    ... communicate directly with Site C and vice versa. ... ADSS the DC in Site B keeps setting up one of its replication partners to ... ISTG for intersites connections using BH) ... the ISTG won't use the BH servers between Site C and SiteB to ...
    (microsoft.public.win2000.active_directory)