Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial
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.
1. Create DB and Table and TableData
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
SELECT *
FROM TestTable
GO
2. Create Master key & Certificate & Symmetric & add Column
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.
-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SQLAuthority'
GO
-- Create Encryption Certificate
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO
-- Create Symmetric Key
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE EncryptTestCert
GO
-- Encrypt Data using Key and Certificate
-- Add Columns which will hold the encrypted data in binary
ALTER TABLE dbo.TestTable
ADD EncryptSecondCol VARBINARY(256)
GO
SELECT *
FROM TestTable
GO
3. Update encrypted data created by certificate and key
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.
-- Update binary column with encrypted data created by certificate and key
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO
-- Check the content of the TestTable
SELECT *
FROM TestTable
GO
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.
4. Decrypt data of the SecondCol
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.
-- Decrypt the data of the SecondCol
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol, *
FROM TestTable
GO
5. Clean up ALL
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.
-- Clean up database
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
-- DROP DATABASE SQL_ENCRYPTION
GO
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.
image002.jpg来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12476590/viewspace-536413/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12476590/viewspace-536413/
3640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
