Transparent Data Encryption (often abbreviated to TDE) is a
technology employed by both Microsoft and Oracle to encrypt database content.
TDE offers encryption at a column, table, and tablespace level. TDE solves the
problem of protecting data at rest, encrypting databases both on the hard drive
and consequently on backup media. Enterprises typically employ TDE to solve
compliance issues such as
PCI DSS.
WHAT IS Payment
Card Industry Data Security Standard (PCI DSS) ?
Defined by the Payment Card Industry Security Standards Council, the standard was created to increase controls around cardholder data to reduce credit card fraud via its exposure. Validation of compliance is done annually — by an external Qualified Security Assessor (QSA) that creates a Report on Compliance (ROC) for organizations handling large volumes of transactions, or by Self-Assessment Questionnaire (SAQ) for companies handling smaller volumes.
Microsoft SQL
Server 2008 provides real time encryption of data and log files.
Data is encrypted before it is written to disk; data is decrypted when it is
read from disk.
The "transparent" aspect of TDE is that
the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is
absolutely no code that needs to be written to perform the encryption and
decryption.
There are a couple of steps to be performed to
prepare the database for TDE, and then the encryption is turned on at the
database level via an ALTER DATBASE
command.
To avoid incidents
where backup tapes containing sensitive information have been lost or stolen
and sensitive information goes into wrong hand, Using TDE feature the backup files can be also encrypted. We just need to turn on encryption for database
no more additional efforts are needed to
generated encrypted backup, the regular BACKUP command itself generate
encrypted backup.
The data in the encrypted backup files is completely useless
without having access to the key that was used to encrypt the data.
What is mean
by term encryption?
It is the process of transforming information using an
algorithm to make it unreadable to anyone except those possessing special
knowledge, usually referred to as a key
The security provided by encryption is based on the strength
of the algorithm and protection of the key.
Types of keys:
1) Symmetric: With a symmetric key, the same
value is used to encrypt and decrypt the data.
2) Asymmetric: An asymmetric key has
two components - a private key and a public key. The private key is used to
encrypt data and public key must be used to decrypt the data.
Implementing
TDE?
To
implement TDE the following four steps need to be followed:
1)
Create
a master key
2)
Create
or obtain a certificate protected by the master key
3)
Create
a database encryption key and protect it by the certificate
4)
Set
the database to use encryption
HOW TO Create a Master Key?
A
master key is a symmetric key that is used to create
1)
Certificates
2)
Asymmetric keys
Execute
the following script to create a master key:
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD =
'Pass@word1';
GO
|
Note
that the password should be a strong one (i.e. use alpha, numeric, upper,
lower, and special characters)
and you have to backup (use BACKUP MASTER KEY)
and store it in a secure location.
HOW TO Create a Certificate?
Certificates
can be used to create symmetric keys for data encryption or to encrypt the data
directly. Execute the following script to create a certificate:
USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO
|
HOW TO Create a Database Encryption Key?
A
database encryption key is required for TDE. Execute the following script
to create a new database and a database encryption key for it:
CREATE DATABASE mssqltips_tde
GO
USE mssqltips_tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE
TDECert
GO
|
In
order to work with TDE the encryption
key must be encrypted by a certificate (but not by a password) and the
certificate must be located in the master database.
How TO Enable TDE?
The
final step required to implement TDE is to execute the following script:
ALTER DATABASE mssqltips_tde
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM
sys.databases
GO
|
You
can query the is_encrypted column in
sys.databases to determine whether
TDE is enabled for a particular database.
It is important to emphasize that TDE only encrypts the
content of data and log files. It does not encrypt the data as it is
being passed between the client and the database server.
No comments:
Post a Comment