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
ALTER DATABASE database_name SET ENCRYPTION ON GO
There's several types of algorithms you can use to create the database encryption key:
- AES_128
- AES_192
- AES_256
- TRIPLE_DES_3KEY
-----------------------------------------------------------------------------------------
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)