Skip to content

Database Encryption in SQL Server 2008

activate Transparent Data Encryption unter SQL Server 2008

we connect  the database “myselim”.First we need to create  Service Master Key (SMK). Therefor  we use command  ” USE master ” in master database  and then we give these statement :
1.) CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Passwort’

 

2.) we need to create certificate for Database master key 

CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘myselim DEK Certificate’

You have to backup these certificate immediately with private_key. because without private_key you can’t restore database on another server and your certificate is useless.

here is the command:
BACKUP CERTIFICATE MyServerCert TO FILE = ‘C:\selim\MyCertificateBackup.bck’
WITH PRIVATE KEY (
FILE = ‘c:\selim\MyPrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘Passwort’);

3.) now we create with this certificate Database encryption key.first we connect our database “myselim”.( use myselim)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert

4.) we have to activate now TDE on SQL 2008.
ALTER DATABASE myselim SET ENCRYPTION ON

You can control TDE 

Select db_name(database_id), encryption_state from sys.dm_database_encryption_keys