SQL Server 2005 - Managing Certificates

SQL Server 2005 - Managing Certificates

In this tutorial you will learn about Managing Certificates in SQL Server 2005, SQL Server 2005 makes significant improvements in two areas—Native encryption and certificates. Encryption is a mechanism that is intended to protect data using a specially designed algorithm for converting content into a format different from the original. Reversal of the process requires an appropriate decryption key and algorithm that converts the data back into its original content.

The key and the algorithm required for encryption and decryption may be identical or different. The process may be symmetrical or asymmetrical. The complexity of the algorithm increases data security but requires huge computational facilities to complete the process.

Asymmetric algorithms have a pair of keys that are known as public and private keys. Private keys are available only to the owner while the public one lacks protection and can be used to perform data encryption. Decryption can only be performed by use of the private key. This approach is also used for digital signatures where the sequence is reversed.

A step forward in resolving the issues related to private and public key distribution has been resolved by the creation of digital certificates. Microsoft has created a number of certificate authorities known as Trusted Root Certification Authorities with the operating system. These are institutions with impeccable reputation assigned with the responsibility of verifying the authenticity of those requesting certificates. They are also empowered to delegate the power of issuing certificates to other authorities.

The characteristics of the certificates reflect the purpose of issue and the public key, the digital signature of the issuer and the validity period. These certificates can be revoked and added to a special list maintained for the purpose by the issuer. However, the responsibility of verifying certificate validity vests with the application.

In SQL Server 2005 encryption components are available natively. Each instance of the Server has a Service Master Key which is automatically created during setup and encrypted with a Data Protection API provided by the Windows Operating system. This secures system data. The Service Master key secures the Database Master Keys which is the basis for creating certificates or asymmetric keys. These keys can then be used to protect data and extend encryption hierarchy.

Pairs of functions have been created to handle the process of encryption and decryption such as EncryptByCert() and DecryptByCert(); EncryptByAsymKey() and DecryptByAsymKey(); EncryptByKey() and DecryptByKey(); and EncryptByPassPhrase() and DecryptByPassPhrase().

The database master key is created using the syntax CREATE MASTER KEY ENCRYTPION BY PASSWORD= ‘password’.

The Administrator defined password encrypts the key and stores it in the sys.symmetric_keys catalog and the Service Master Key encrypts the database master and stores it separately in sys.databases for facilitating automatic opening. Certificates and asymmetric keys can, then, be created.

Certificates are generated using the syntax CREATE CERTIFICATE DDL T-SQL statement. Users can create new certificates or use existing ones with the password included in the CREATE CERTIFICATE statement.

The CREATE ASYMMETRIC KEY T-SQL statement allows the users load an existing key pair from a file or an assembly and create a new one using the RSA algorithm with 512,1024 or 2048 bit long private key. If a password is specified, it is used to encrypt the private key else the database master is used to perform the role. This process of using asymmetric keys is very complex and processor intensive.

A symmetric key can be created using the CREATE SYMMETRIC KEY DDL T-SQL statement. In this instance the encryption algorithm can be specified and the user can specify whether the symmetric key will be encrypted with the certificate, another key or password. Symmetric keys in a database are listed in sys.symmetric_keys catalog while open symmetric keys are listed in sys.open_keys catalog.

In addition to the above built in features, SQL Server allows users take advantage of the encryption functionality of the .NET Framework to create user defined functions.