PageViews Last Month

Tuesday, 13 October 2015

SQL server Encryption – Transparent Data Encryption

In the previous blogs we saw the use of certificates to encapsulate symmetric keys. How it benefits the user by maintaining the password rather than user having to mention it every time a key is opened for use.
In all the above methods we are protecting data at a granular level like tables and columns. 


What if we wish to protect data at the higher level?  Let’s say no one should be able to see any data in my database until I authorize to do so. I wish to protect all my data which implies all the data that resides in my database files and log files. From SQL server 2008 this functionality was provided through Transparent Data Encryption. As per definition by Microsoft, TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.

Isn’t it amazing even if accidentally someone were to get access to the data and log files they would be able to attach the database and view the information. Or let’s say they get access to the backup files they still wouldn’t get the data by a normal restore operation.

The encryption of files and decryption happens by SQL server in memory. The data at rest is cipher text. It does impact the performance while these operations are performed in memory. Pages are first decrypted and then the data is provided to the receiving client.

How it happens

1)  CREATE MASTER KEY

USE TESTDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1211!pwD##';
GO

2) CREATE CERTIFICATE

CREATE CERTIFICATE TestCertificate
 WITH SUBJECT = 'For TDE for TESTDB’,
 GO

3) CREATE DATABASE ENCRYPTION KEY
     
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestCertificate
 GO


4) ALTER DATABASE SET ENCRYPTION ON

ALTER DATABASE TESTDB
SET ENCRYPTION ON
GO




9 comments:

  1. Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important. Project Center in Chennai It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves.

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.

      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete

  2. Great, Your post is just outstanding! thanks for such a post,its really going great work.

    Microsoft Server 2016 Repair
    Microsoft Server 2016 Migration

    ReplyDelete
  3. The simple term of encryption is now known as "anonymyzation of data". A simple yet informative read, thanks!

    ReplyDelete
  4. The simple term of encryption is now known as "anonymyzation of data". A simple yet informative read, thanks!
    Bellwether
    ISO 27001 Consulting Company

    ReplyDelete