SQL 2005, replicating Encrypted Data?

SQL 2005, replicating Encrypted Data?

• Mr Guy

  Let me know if this doesn't answer any of your questions:

  Elements of Replication Security

  
  

• SW Skeen

  Correct. I am storing encrypting data on the publisher and I want the data to be stored encrypted on the subscriber as well. I also need to be able to decrypt it both on the publisher and subscribers.

  Thanks,

  Aaron

  
  

• Jonathon H.

  Aaron,

  To clarify, you want to encrypt all the user data you intend to replicate, not replication agent password, correct The notes you includes from books online only applies to encrypt replication agent password.

  Thanks

  Wanwen

  
  
  

• AlexanderG

  By doing a full backup and restoring at the subscriber before enabling replication, the data encryption and decryption worked perfectly.

  Thanks,

  --AML--

  
  

• theflu

  Unfortunately, encryption keys won't be automatically replicated to subscriber. You would have to manually ensure that the same set of encryption keys is available on subscriber as they are on publisher.

  For your example, you could create your SYMMETRIC KEY using the same KEY_SOURCE, IDENTITY_VALUE and ALGORITHM at subscriber. This will ensure the same symmetric key hence you could decrypt the data at subscriber as well. As for database master key, ASYMMETRIC KEY, they don't need to be the same.

  If you use publisher database backup and restore it to the subscriber to setup replication, assuming all the needed keys are already created in the database backup, then you don't need to do the manual workaround since all the needed keys are already there. For security reason, we do recommend you regenerate your database master key at subscriber.

  Please let me know if you have further questions

  Thanks

  Wanwen

  
  
  

• Ebaoll

  Yeah, I've seen that, the only excerpt that seems to address what I need is:

• If a Master Key is present in a database involved in replication (a publication database, subscription database, or distribution database), replication encrypts and decrypts agent passwords in that database using a SQL Server 2005 database symmetric key. If Master Keys are used, a Master Key should be created in each database involved in replication. If a Master Key is not present in a database involved in replication, replication encrypts and decrypts agent passwords in that database using the algorithm available in SQL Server 2000. For more information about creating Master Keys, see CREATE MASTER KEY (Transact-SQL).
  
• Replication does not replicate Master Keys. If you require the Master Key at the Subscriber, you must export it from the publication database using BACKUP MASTER KEY and then import it into the subscription database using RESTORE MASTER KEY. For more information, see BACKUP MASTER KEY (Transact-SQL) and RESTORE MASTER KEY (Transact-SQL).
  
• If a Master Key is defined for an attachable subscription database, specify the Master Key password using the @db_master_key_password parameter of sp_attachsubscription (Transact-SQL). This allows the database to be attached at the Subscriber.

So when I run this in the publisher:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[Strong password here]'
GO

CREATE ASYMMETRIC KEY [Key_Name_1] '(since now encryption by password clause present, it will be encrypted with the master key)
WITH ALGORITHM = RSA_2048;
GO

CREATE SYMMETRIC KEY [Key_Name_2] '(Symmetric keys must be encrypted by using at least one of these four; certificate, password, symmetric key, or asymmetric key)
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY [Key_Name_1]
GO

I'll need to also create a master key on all the subscribers and the distributor. But does the symmetric key then get replicated or do I need to do a backup/restore on the subscriber to be able to decrypt the data However if I do a backup/restore, since it's based upon the master key, do I need to backup/restore all three

In other words, I don't think that really answers my questions... :)

Thanks,

Aaron