Securing your application. Part 2 - Securing your data

We've just had a look at how to secure your connection in Part 1 so obviously everything is now all *** dorey on that end.

Some of the other areas that you naturally have to look at is the environment/infrastructure/network and your data itself. It's not much use to encrypt and secure the data that you're transferring if the actual data itself isn't protected.

What i hear you say? my IT guys got that under control..firewalls up the wazzooo...everything's patched and service packed!

Of course that's not really enough and we know this - we just sometimes choose to leave that in the hands of those other people - network engineers.

If we look at SQL Server 2008 then there's a lot of new capabilities available to us for data security.

SQL Server 2008 Encryption Capabilities

  • in-built cryptography hierachy for the creation of assymetric, symmetric keys as well as certificates
  • Transparent Data Encryption (using database encryption keys - DEK)
  • Signing of code modules (using keys or certificates)

Creating certificates

 It's not that  complex to create a certificates (btw, all SQL Server certificates comply wit hthe IETF X.509v3 certificate standards) on SQL Server 2008., it's as simple as using T-SQL.

Self-signed certificate

CREATE CERTIFICATE name ENCRYPTION BY PASSWORD = 'strong password goes here' WITH SUBJECT = 'subject goes here' EXPIRY_DATE = 'expiry date goes here'

Certificate from a signed executable file

CREATE CERTIFICATE name FROM EXECUTABLE FILE = 'file path goes here.dll'

Certificate from a file

CREATE CERTIFICATE name FROM FILE = 'file path goes here.cer' WITH PRIVATE KEY (FILE = 'file path goes here.pvk', DECRYPTION BY PASSWORD = 'strong password goes here')

Using Transparent Data Encryption

TDE is a real-time, physical I/O encryption/decryption of both the database file and the log file - this is done by using the database encryption key (DEK).

The DEK is stored in the boot record and is a symmetric key which is also secured by a certificate.

It's important to note that TDE does not encrypt the transfer of data (eg. it encrypts the data itself, but not the connection/network communication) and that you should always back up the certificate and private key for it.

The simple steps of using TDE are:

  • Create master key
  • Create certificate protected by said master key
  • Create database encryption key and protect it with the certificate we created earlier
  • Set ENCRYPTION to ON for the database

This is again done with T-SQL:

USE master; GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong password goes here'; GO
CREATE CERTIFICATE name WITH SUBJECT = 'subject for the certificate goes here'; GO
USE database_name; GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = algorithm_choice_here ENCRYPTION BY SERVER CERTIFICATE name_from_certificate_we_just_created; GO

There's several types of algorithms you can use to create the database encryption key:

  • AES_128
  • AES_192
  • AES_256


Securing your application. Part 1 - Securing your connection
Securing your application. Part 2 - Securing your data

Securing your application. Part 3 - Securing your code (coming)

Published Tue, May 4 2010 8:01 by Brian Madsen