Mike,

Thanks for your Wonderful article on SQL Server Encryption.

Please give me your inputs for the following scenario.

We have log shipping set up between server A and Server B. Server B is in read only mode.

There is no other encryption in any other databases on Server B.

I have performed the following steps on the primary server:

1) Created database master key with encryption using password.

2) Created a certificate

3) Created a symmetric key and encrypted it using certificate created in step 2( above)

4) Opened and Used the symmetric key to encrypt sensitive data in log shipping primary server.

After performing the above steps, I am able to decrypt the data in Primary Server (A) and receiving NULLs for the encrypted columns in the secondary Server(B).

Is there any solution available for the above problem.

I tried backing up the service master key from primary and restored it on secondary server. This works fine and able to decrypt the values on secondary server.

Will this affect anything else like linked servers or credentials on the secondary server?

Thanks,

Prem