research Question

how to setup SQL serve 2016 TDE

Avatar of marrowyung
marrowyung asked on
Microsoft SQL ServerEncryptionSQL
3 Comments1 Solution21 ViewsLast Modified:

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



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

 

USE master 

GO

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

 


  1. 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' 

 

 


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

 

USE Northwind 

GO

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM AES_128 

ENCRYPTION BY SERVER CERTIFICATE NorthwindCert

GO

 

 


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

 

ALTER DATABASE Northwind 

SET ENCRYPTION ON 

 


  1.  
  2. 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. 

 


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

USE master 

GO

BACKUP CERTIFICATE NorthwindCert  

TO FILE = 'C:\NorthwindCert_File.cer' 

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

ENCRYPTION BY PASSWORD 'mY_P@$$w0rd' 

GO

 


  1.  

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

 


  1. 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 

GO

 

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

GO

 

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.

 


  1. 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'); 

GO 

 


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 .



 




ASKER CERTIFIED SOLUTION
marrowyungSenior Database Architecture (Data)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros