Transparent Data Encryption -SQL Server

Overview of steps –

 

1.Create Master Key on Master Database

2.Create Certificate protected by Master Key on Master Database

3.Create Database Encryption Key using Certificate on User Database that must be encrypted

4.Change the Database Option setting to Encryption

5.Backup Certificate and Private Key at safe place

 

Note -

1.By default certificate will expire in one year if we did not mention any date during certificate creation

2.In case of certificate expiry sql server will not stop or wait to renewal of certificate it will encrypt even certificate is expired 


1.Create Master Key on Master Database


Use Master

Go

Create Master Key

Encryption By Password ='Strong@password'

Go



2.Create Certificate protected by Master Key on Master Database


use master

Go

create Certificate TDE_CERT

WITH SUBJECT ='TDE Certificate'

Go


Select * from sys.certificates (check if certificate is created)



3.Create Database Encryption Key using Certificate on User Database that must be encrypted


Use TDEDB

Go

Create Database Encryption Key

with algorithm = AES_256

Encryption By Server Certificate TDE_CERT;

GO


4.Change the Database Option setting to Encryption


use master

Go

Alter database TDEDB

Set encryption on

Go


5.Backup Certificate and Private Key at safe place


Use Master

Go

backup certificate TDE_CERT

To FIle='C:\Encryption\TDE_CERT.cer'

with private key (file='C:\Encryption\pkey.key' ,encryption by password ='Passwo@rd')

Go



Comments

Post a Comment

Popular posts from this blog

How to read MSDTC Trace logs

I have recently come across situation where i need to read the logs of MSDTC  and it is not straight forward approach after researching little bit i am able to figure out the method ,Please find detail information on how to read MSDTC logs and let me know if you have questions in comments Steps to read MSDTC logs tracefmt.exe  will play important role reading MSDTC logs 1.Please check if you have tracefmt.exe in  C:\WINDOWS\system32\MsDtc location and  copy to C:\WINDOWS\system32\MsDtc\trace ,  if you don’t have tracefmt.exe in the location please download from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11800 and need to install complete package from below link (I have attached tacefmt.exe in zip folder) 2.Once you install go to C:\WinDDK\7600.16385.1\tools\tracing\amd64 and you will find tracefmt.exe  and copy tracefmt.exe   to C:\WINDOWS\system32\MsDtc\Trace where you have trace files 3.If you al...
Read more

CDC Monitoring

Change data capture(CDC) is feature where we can track the changes on table if we enable CDC on that table and CDC should be enabled from DB level first and we can enable it for tables  As i see so many articles about CDC in the internet i don't  want to go through CDC this topic will discuss only about system tables in CDC and how to do basic monitoring , tracking the errors for CDC enabled tables  To Monitor the empty scans we can use this select statement and empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1 whenever log scan result is empty and it will increment when ever it got empty result from scan SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0 And for latency between actuall table and CDC we can use below select SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 We can store data from the views     sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_er...
Read more

Error - Exclusive access could not be obtained because the database is in use. Msg 3101, Level 16, State 1, Line 3 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

We need to alter database and change to single user mode before restoring the DB and to avoid connection issues if application still connecting to DB we can include in restore command ,So it will automatically make DB to single user mode before restoring and it will change to multi user mode after restoring automatically Use master Go Alter database set single_user with rollback immediate Go Restore database command Go Alter database set multi_user with rollback immediate Go
Read more