January 1, 2015 at 3:52 pm
I am new to setting up transactional replication and need assistance in the initialization process when encryption is present on the Published database. How do I properly initialize my Subscriber database and apply the encryption key before pulling any transactions from the Distributor. The Initialize Subscriptions page when setting up a New Subscription references "A subscription database needs to be initialized with a snapshot of the publication data and schema unless it has already been specially prepared for the subscription". This reference is probably related to my issue, but I haven't been able to pin down the sequence of steps to "specially prepare" my Subscriber database. Help please.
Jacki
January 2, 2015 at 8:40 am
Encryption as in column level encryption (symmetric/asymmetric keys) or TDE?
January 2, 2015 at 8:59 am
I'm not very familiar with encryption. The vendor has given me a script that I need to run after restoring the database for the subscription. They call it reactivating the master key.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
I'm thinking this is TDE.
January 2, 2015 at 9:07 am
Could be either one. You need a master key for TDE or column level stuff.
Do you have a DMK? Run this:
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO
Any values in encryption_state?
January 2, 2015 at 9:31 am
encryption state comes back NULL on all databases. Does this mean it would be at the column level? If so, what does that mean for my replication setup concern?
Jacki
January 2, 2015 at 9:39 am
If they're all NULL, then it should be column level encryption. If you had a 1, 2, 3 for the database, those would imply TDE.
In terms of encryption, what you need to do is make sure that the keys are transferred over. I don't believe these can be replicated, and as this paper shows, you need to recreate the keys on the other side (subscriber).
January 2, 2015 at 9:46 am
Does this mean that running the script to re-activate the master key becomes necessary when the data is read for the first time, and is not necessary to be in place before replication begins? Which would mean that I can allow the initialization to happen when setting up the subscription and apply the master key script before the data is read for the first time.
Jacki
January 2, 2015 at 9:54 am
The master keys are unique to each database. You don't have to have the same ones in the publisher and subscriber.
You do need to have a master key in a database before you can create symmetric/asymmetric keys. Create one in the subscriber (use a script, make sure you save the pwd somewhere secure). Then you can re-create your sym/asym keys in the subscriber. There will need to be code (procs/batches) that manage opening the keys for decrypting data.
January 2, 2015 at 10:02 am
Thank you for all your assistance. It appears that our vendor may not have given me all that was necessary for replicating their encrypted data. They only instructed me to run the OPEN MASTER KEY... and ALTER MASTER KEY... statements.
I will use your link and the information you shared to set up the proper encryption on our subscriber.
Thankyou again for your prompt attention,
Jacki
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply