asked on

how to setup SQL serve 2016 TDE

hi,

I am now trying to setup TDE for our customer using SQL serve 2016 and from my understand TDE only protect SQL server DB restore to other SQL server, any thing else TDE can protect?

ONLY available for SQL server 2016 enterprise? how about standard edition?

here:

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15

seems saying only enterprise support it ?

currently this is my TDE setup procedure, is it still valid for SQL server 2016

setting up encryption on the server. To do that, we create the database master key on the master database

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE NorthwindCert

WITH SUBJECT = 'My DEK Certificate for Northwind database'

Set the encryption for the Northwind database by creating a database encryption key and password using the certificate we just created.

USE Northwind

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE NorthwindCert

After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE Northwind

SET ENCRYPTION ON

After that the backup of user database will be encrypted, can't be read and can't restore to other server.

when restore:

In order to restore the encrypted database backup on another SQL Server instance we need to first export the certificate we created on the instance on which the encrypted database backup was created.

To export the certificate to a file, I'll connect on my default instance and run this query.

USE master

BACKUP CERTIFICATE NorthwindCert

TO FILE = 'C:\NorthwindCert_File.cer'

WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk' ,

ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' )

we'll need to copy the certificate and the private key file to the other SQL Server instance.

On the other SQL server box which we will restore the encrypted DB to:

import the certificate by first creating a master key.

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd'

The password provided here is different from the one you used in the default instance as you are creating a new master key for this instance.

After a master key has been created, you can create a certificate by importing the certificate we created earlier.

CREATE CERTIFICATE NorthwindCert

FROM FILE = 'C:\NorthwindCert_File.cer'

WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk',

DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');

Notice that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which we used to export the certificate to a file. This is because we will use the same certificate on this new instance to access the encrypted database.

however, I am watching this:

https://www.youtube.com/watch?v=hxdqg7SZmR4&ab_channel=RedDogConsultingLLC

seems much complicate than what I have , please comment .

marrowyung

ASKER

I read this page and there are concerns:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2016

Replication doesn't automatically replicate data from a TDE-enabled database in an encrypted form. Separately enable TDE if you want to protect distribution and subscriber databases.
Snapshot replication can store data in unencrypted intermediate files like BCP files. The initial data distribution for transactional and merge replication can too. During such replication, you can enable encryption to protect the communication channel.

what is separately enable TDE if you want to protect distribution and subscriber databases means?

and the database configured for TDE can't use replication ? transactional , merge or snapshot replication will fail ?

"During such replication, you can enable encryption to protect the communication channel."

so for any DB involved in SQL replication, we can only make sure network has encryption between source and target SQL server in replication relationship ?

any other limitation on setting up TDE for a SQL server DB ?

marrowyung

ASKER

administrator, please put me to more zone.

ASKER CERTIFIED SOLUTION

marrowyung

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial