TDE With Log shipping

• Ext12

  Old Hand

  Points: 329

  More actions

  February 9, 2021 at 6:56 pm

  #3843034

  Our environment is SQL Server 2019. I'm new to TDE and am having to learn it because of a vendor request, but I am having trouble finding a similar environment to ours as I Google.

  We have a vendor's healthcare application my company uses and the DB supporting the app is in a cloud environment that I do not have access to. For me to gain access to the DB for analytics purposes, we implemented log shipping. This allows me to have a copy of the DB locally and it works great.

  Within this application, we have a different vendor's data and they want us to implement TDE on the replicated DB sitting locally on our server.

  As the local DB is in read only/stand by, is it even possible or recommended to make the target DB encrypted knowing the source DB is not encrypted? I can't even add someone to the DB to give them access in SSMS because it is "read only / standby" so I didn't think I could implement TDE either.

  It seems to me the proper solution is to encrypt the source DB in the cloud which would then be encrypted as the target DB through log shipping.

  Anyone have experience with something similar that could explain if it is possible to encrypt only the target while it is in "read only/standby?"

  Thanks...

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  February 10, 2021 at 5:24 pm

  #3843452

  You can't encrypt a read-only database. You can store a read-only database's files in an encrypted file system, though.

  You have a few options:

  • Use SQL Server TDE to encrypt the source read/write database, and copy the TDE key to the log-shipping target server.

    • If using a SQL Server Certificate as the TDE key, then use BACKUP CERTIFICATE[...] / CREATE CERTIFICATE FROM FILE [...] to copy the Cert to the target server, and everything will just work.
    • If using EKM as the TDE key store, then both the source and the target need access to the chosen EKM (such as a cloud KMS), or you must export the Asymmetric Key from the source system's EKM host and import that to an EKM host for the target system

  --- OR ---

  • Use an encrypted file system provider (Windows EFS, Vormetric, etc.) that will encrypt the folders holding the database files and its backup files

  Whatever way you go, your foremost task is backing up and protecting the encryption key. Lose that, and you lose everything.

   

  -Eddie

  Eddie Wuerch
  MCM: SQL

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736418

  More actions

  February 11, 2021 at 5:44 pm

  #3843904

  +1 to Eddie's note.