Decrypting from replicated table

I have a replicated table with an encrypted SSN column. In the source
database, we have created the master key, certificate, and symmetric key. I
s
there a way for me to decrypt the SSN from the replicated table, or do I hav
e
to perform a distributed query to the source database in order to get the
decrypted SSN?You can do a distributed query or you can setup the same master key,
certificate, and symmetric key on the subscriber.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>I have a replicated table with an encrypted SSN column. In the source
> database, we have created the master key, certificate, and symmetric key.
> Is
> there a way for me to decrypt the SSN from the replicated table, or do I
> have
> to perform a distributed query to the source database in order to get the
> decrypted SSN?|||How can I create the same keys in a different database?
"Michael Hotek" wrote:

> You can do a distributed query or you can setup the same master key,
> certificate, and symmetric key on the subscriber.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>
>|||Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
KEY. For additional information, see
http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last section
of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
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.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...[vbcol=seagreen]
> How can I create the same keys in a different database?
> "Michael Hotek" wrote:
>|||Thank you for the clarification. However, please correct my understanding
here:
1. I will need to drop my existing, original symmetric key and recreate
using the KEY_SOURCE and IDENTITY_VALUE params.
2. I will then need to "re-encrypt" my data using that new symmetric key
3. In the database, with the replicated table, I will need to create a new
symmetric key using the same params from the original key.
Please clarify any misunderstanding I have about the process. Thanks.
"Laurentiu Cristofor [MSFT]" wrote:

> Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
> KEY. For additional information, see
> http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last sectio
n
> of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
> --
> 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
.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...
>
>|||Don't drop the original symmetric key before re-encrypting, you will need it
to decrypt the existing data. Otherwise, this will be indeed the process:
create new key using the params, decrypt with old key and reencrypt with the
new one, then in the replicated database recreate the key from the same
params.
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.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:A68460ED-9912-40AC-9788-9A1C5E2FD15E@.microsoft.com...[vbcol=seagreen]
> Thank you for the clarification. However, please correct my understanding
> here:
> 1. I will need to drop my existing, original symmetric key and recreate
> using the KEY_SOURCE and IDENTITY_VALUE params.
> 2. I will then need to "re-encrypt" my data using that new symmetric key
> 3. In the database, with the replicated table, I will need to create a
> new
> symmetric key using the same params from the original key.
> Please clarify any misunderstanding I have about the process. Thanks.
> "Laurentiu Cristofor [MSFT]" wrote:
>