Introduction
SQL Server 2005 provides encryption as a new feature to protect data against the attacks of hackers. Hackers may be able
to get hold of the database or tables, but they wouldn't understand the data or be able to use it. It is very important to
encrypt crucial security related data when stored in the database, as well while transmitting across a network between the
client and the server.
There are three levels of encryption hierarchy. These levels provide different mechanisms for securing data across
networks and local servers. Different level of hierarchies allows multiple instances of services (e.g. SQL Server Services)
to run on one physical server.
- Windows Level - Highest Level - Uses Windows DP API for encryption
- SQL Server Level - Moderate Level - Uses Services Master Key for encryption
- Database Level - Lower Level - Uses Database Master Key for encryption
There are two different kind of keys used in encryption.
- Symmetric Key - Symmetric cryptography system in which the sender and receiver of a message
share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement and the
sender and receiver either can encrypt or decrypt the messages.
- Asymmetric Key - Asymmetric cryptography, also known as Public-key cryptography, is a system in
which the sender and the receiver of a message have a pair of cryptographic keys - a public key and a private key - to
encrypt and decrypt the message. This is relatively complex system and the sender can use its key to encrypt the message, but
he can't decrypt it. The receiver can use its key to decrypt the message but he can't encrypt it. Due to its complexity, this
is a resource intensive process.
Certificates are used as well for encrypting data. A public key certificate is a digitally-signed statement that
binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. A
Certification Authority (CA) issues and signs certifications.
Please create a sample database that we will use for testing Encryption. There are two different kinds of encryption
available in SQL Server:
- Database Level - This will secure all the data in database. However, every time data is written
or read from database, the whole database has to be decrypted. This is a very resource intensive process and not a practical
solution.
- Column (or Row) Level - This level of encryption is the preferred method of encryption. Only
columns containing important data should be encrypted; this will result in less CPU load than the whole database level
encryption. If a column is used as primary key, or used in comparison clauses (WHERE clauses, JOIN conditions) the database
will have to decrypt the whole column to do operations involving those columns.
Let's go over a simple example that demonstrates the encryption and decryption process done with Symmetric Key and
Triple DES encryption algorithm.
Create a sample table and populate it with sample data. We will encrypt one of the two columns of the table.
The previous code will return the result shown in the next figure.
Figure 1: Result of the SQL query
Every database can have one master key. The database master key is a symmetric key used to protect the private keys
of certificates and asymmetric keys present in the database. It uses Triple DES algorithm along with user provided password
to encrypt the keys.
Certificates are used to protect encryption keys, which are used to encrypt data in the database. SQL Server
2005 has the ability to generate self-signed X.509 certificates.
The symmetric key can be encrypted by using any of the certificate, password, and symmetric key, asymmetric key
options. We can use many different algorithms for encrypting key. Supported algorithms are DES, TRIPLE_DES, RC2, RC4,
RC4_128, DESX, AES_128, AES_192, and AES_256.
Now add a column of type varbinary to original table, which will store the encrypted value for the
SecondCol.
Before using the key, it needs to be decrypted by the same method with which it was encrypted. In our example
we had used a certificate for encrypting the key. Due to the same reason, we are using the same certificate for opening the
key and make it available for use. After it is open and available to use, we can use the encryptkey function and
store the encrypted values in the database, in the EncryptSecondCol column.
We can drop the original SecondCol column, which we have now encrypted in the
EncryptSecondCol column. If you don't want to drop the column, you can keep it for future comparison of the data
when we decrypt the column.
We can run a SELECT query on our database and verify that our data in the table is protected, and hackers will
have no understanding of it if they manage to reach the data.
Figure 2: Result of the previous SQL query
Authorized user can use the decryptbykey function to retrieve the original data from the encrypted
column. If Symmetric key is not open for decryption, it has to be decrypted using same certificate which was used to encrypt
it. One thing to keep in mind here is that the original column and the decrypted column should have the same data types. If
they are of different data types, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for
SecondCol and EncryptSecondCol.
Figure 3: Result of the previous SQL query
If you drop the database after all the processing is complete, you do not have to worry about cleaning up the
database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to
close the key after using it. If keys and certificates are used only once or their usage is over, they can be dropped as
well. Dropping a database will drop everything it contains - table, keys, certificates, all the data etc.
Summary
Encryption is a very important security feature of SQL Server 2005. Long keys as well as asymmetric keys create strong
encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is
lots of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further
with an asymmetric key for further protection and adds the advantage of a stronger encryption. It is also advisable to
compress data before encryption, as encrypted data can't be compressed.
About Pinal Dave
 |
Pinalkumar Dave is Microsoft SQL Server MVP and author of several hundreds SQL Server articles. He has six years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NE...
This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.
|
You might also be interested in the following related blog posts
Custom Editing Behavior for DataGridView TextBox Columns
read more
Query Notifications and LINQ to SQL - Well I'll be, you *can* do it (with caveats)
read more
SQLite, ADO.NET, Prepared Statements, Transactions and Enterprise Manager
read more
Pressing keys simulation in Selenium, RadInput on fire
read more
How to debug a stored procedure in your Sql Server 2005
read more
How to hire a programmer - Part 2 - Improve this code
read more
Script to Inventory Print Servers
read more
Upload Images To SQL Server By Way Of An ASP.NET Web Form
read more
Database Access Using The .NET Data Providers
read more
Five New Security Tutorials Now Available
read more
|
|
Please login to rate or to leave a comment.