SQL Server 2005 and SQL Server 2008 Encryption

Microsoft SQL Server now provides additional protection for your data. With encryption built in, this feature can be configured by your database administrator. Used in conjunction with certificates and good security practices, encryption ensures that data is protected from point A to point B. Today SQL Server serves a majority of users and consumers. Security is of the utmost importance especially with data breaches happening more frequently.

Encryption is the act of protecting data through a special algorithm. This act of encryption makes the data different than the original. With encryption, a key is provided to encrypt the data and this key can be used to decrypt the data thus reversing the encryption. Different keys can be used to encrypt and decrypt data with encryption, the process can be symmetrical or asymmetrical.

With encryption, encryption keys can be private or public. This process allows for intended users to gain access to the information and data in the database. If SQL is used on the web, a digitally signed certificate is often used.

With earlier versions of SQL Server, certificates were used to verify client connections to the database and its data. With Microsoft SQL Server 2005 and newer SQL models, encryption is available with the software natively. SQL Server now has a Service Master Key which is created during setup of this database applicaion. This (Service Master Key) is now encrypted with Data Protection API for increased security. This key is now used to secure the Database Master keys.

With SQL Server 2005, the process of encryption and decryption is provided by functions native to this database application. Some of the funtions available include EncryptByCert() and DecryptByCert(), EncryptByAsymKey() and DecryptByAsymKey() and EncryptByKey() and DecryptByKey().

EncryptByCert() encrypts data with the public key of a certificate whereas DecryptByCert() decrypts data with the private key of a certificate.

If you need to create or generate a new certificate you can use the CREATE CERTIFICATE DDL T-SQL statement to perform this action. This process is performed with control permissions to the data in the database. The database master key must be created so you can proceed with the creation of asymmetric keys (a more secure methodology) and certificates. Remember that you should include the password in the CREATE CERTIFICATE statement.

Once this process is complete, you can use the EncryptByCert function. By using this function along with the aforesaid functions above, database administrators can ensure that data will be further protected by using encryption.

In order to protect your database, the process of installing a firewall, setting rules, defining roles in the database, system auditing, creating and monitoring system users and properly sharing files is not enough protection. Every database administrator needs to analyze the need for encrypting their database.