Encryption and SQL
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.