How do I encrypt a DB on SUBSCRIBER server to an encrypted DB on the PUB server?

  • Express12

    SSCoach

    Points: 18442

    I have a PUB on a SQL 2014 Ent server where the database is encrypted w/ TDE (Key algorithm=AES, Key Length=128)

    I have a SUB to that PUB residing on a SQL 2017 Ent server.

    When I check the PUB DB, it is encrypted. When I check the SUB DB is is NOT encrypted. I use this SQL to check:

    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

    WHERE is_encrypted > 0 ORDER BY name;

    GO

    Is there a setting I can use to get the SUBSCRIBING DB to be encrypted?

    BT
  • Sue_H

    SSC Guru

    Points: 90260

    You need to enable TDE on the subscriber if you want it encrypted. Enabling it on the publisher does not affect the subscriber.

    Sue

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715912

    These are two disparate databases, so as Sue mentioned, you need to enable TDE on the subscriber. Different cert and DEK used. These databases aren't linked and the data that goes to the distributor is not encrypted.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply