Encryption of a particular Content DB in SharePoint 2013/2019

Question

Hi All,

Our client has requested to perform encryption on a particular Site Collection's Content DB. I have done research at my end and found that TDE is the most suitable approach to do so.

But I have some queries regarding applying TDE

1. Is Encryption can be applied to a particular content DB? If yes, then is there any affect on other DBs on the SQL server. Because I have read it that, tempdb for the SQL instance is also gets encrypted. This can affect other databases on the same instance.
2. Is there anything that we need to configure on SharePoint for enabling TDE or just we need to perform action on SQL server?
3. We have custom solution deployed on the site collection whose Content DB needs to be encrypted. So, will be there any affect to the custom solution after enabling the TDE on the Site collection's Content DB?
4. Is there any best approach other than TDE for this particular requirement?

Thanks,
Gaurav Singh

Answer 1

Hello @Gaurav Singh Gagwari ,

Transparent Data Encryption(TDE) needs not configure any features/settings on SharePoint. And TDE is designed to protect data by encrypting the physical files of the database.

Please the following steps to enable TDE:

1st: Create the DMK

• Symmetric key used to protect private keys and asymmetric keys
• Protected itself by Service Master Key(SMK), which is created by SQL Server setup
• Use syntax as follow: USE master;
  GO
  CREATE MASTER KEY ENCRYPTION
  BY PASSWORD='CrypticTDEpw4CompanyABC';
  GO

2st: Create the TDE cert

• Protected by the DMK
• Used tp protect the DB encryption key
• Use syntax as follow: USE master;
  GO
  CREATE CERTIFICATE CompanyABCtdeCert
  WITH SUBJECT='CompanyABC TDE Certificate';
  GO

3st: Backup the TDE cert

• Without a backup, data can be lost
• Backup creates two files, the Cert backup and the Private Key File
• Use following syntax: USE master;
  GO
  BACKUP CERTIFICATE CompanyABCtdeCert
  TO FILE = 'C:\temp\CompanyABCtdeCert.cer'
  WITH PRIVATE KEY (file='C:\temp\CompanyABCtdeCert.pvk',
  ENCRYPTION BY PASSWORD='CrypticTDEpw4CompanyABC!');
  GO

4st: Create the DEK

• DEK is used to encrypt specific database
• One created for each database
• Encryption method can be chosen for each DEK
• Use following syntax: USE [YourContentDB Name];
  //USE [SharePoint_AdminContent_f92a9af1-5581-4c6e-a0b0-f8dfb1bbe127];
  GO
  CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE CompanyABCtdeCert;
  GO

5st: Encrypt the DB

• Data encryption will begin after running command
• Size of DB will determine time it will take, can be lengthy and could cause user blocking
• Use following syntax: USE [YourContentDB Name];
  //USE [SharePoint_AdminContent_f92a9af1-5581-4c6e-a0b0-f8dfb1bbe127];
  GO
  ALTER DATABASE [YourContentDB Name]
  //ALTER DATABASE [SharePoint_AdminContent_f92a9af1-5581-4c6e-a0b0-f8dfb1bbe127]
  SET ENCRYPTION ON;
  GO

6st: Monitor Progress

• State is Returned
• State of 2 = Encryption Begun
• State of 3 = Encryption Complete
• Use following syntax: *USE [YourContentDB Name]
  //USE [SharePoint_AdminContent_f92a9af1-5581-4c6e-a0b0-f8dfb1bbe127];
  GO
  SELECT * FROM sys.dm_database_encryption_keys
  WHERE encryption_state =3;
  GO

Restoring Encrypted DB to another Server:

1st: Create new Master Key on Target Server(Does not need to match source master key)
2st: Backup Cert and Private Key from Source
3st: Restore Cert and Private Key onto Target(No need to export the DEK as it is part of the backup)

USE master;  
GO  
CREATE CERTIFICATE CompanyABCtdeCert  
FROM FILE = 'C:\temp\CompanyABCtdeCert.cer'  
WITH PRIVATE KEY(  
FILE ='C:\temp\CompanyABCtdeCert.pvk',  
DECRYPTION BY PASSWORD ='CrypticTDEpw4CompanyABC!');  
GO  

4st: Restore DB

Thanks,
Echo Du

============

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I'd recommend creating a second instance on the same server (call it contosoSQLserver/TDEinstance), and host those TDE encrypted databases there. Perhaps put the TDE data and logs on a separate volume as well.

1. dismount-spcontentdatabase "contoso_content" for all contosoSQLserver content dbs to be encrypted
2. move dbs to contosoSQLserver/TDEinstance and attach
3. Do the necessary regarding keys and etc listed above
4. Mount-SPContentDatabase "TDE_content" -DatabaseServer "contosoSQLserver/TDEinstance" -WebApplication https://contosoIntranet

May need to tweak access for the TDE instance for the some of the SP service accounts, probably the app pool accts, maybe spfarm. If you try to mount the spcontentdatabases and it doesn't work, likely perms issue on new instance. The TDE and non-TDE dbs should side by side in the same application without much strife.

Good luck!