SQL Server 2005 Security - Part 3 Encryption

Tuesday Feb 22nd 2005 by Marcin Policht

While some encryption functionality existed in the previous versions of SQL Server, they were relatively limited and rarely used. SQL Server 2005 provides significant improvements in this area. Part 3 of this series focuses on the freshly introduced native database encryption capabilities.

After discussing authentication and authorization behavior of SQL Server 2005 Beta 2 in the previous two articles of this series, it is time to look into other security-related changes. In particular, we will focus on the freshly introduced native database encryption capabilities. While some encryption functionality existed in the previous versions (e.g. involving column encryption APIs within User Defined Functions or PWDENCRYPT password one-way hash function), it was relatively limited and rarely used. SQL Server 2005 provides significant improvements in this area.

In order to fully comprehend the native encryption introduced in SQL Server 2005, let's first review its principles. In general, encryption is a mechanism for protecting data, which applies to it a specially designed algorithm, effectively obfuscating its content by making it different from the original. Use of the algorithm involves a component known as the encryption key (a sequence of characters). The process can be reversed by applying an appropriate decryption key and equivalent algorithm, yielding the original data. Complexity of the algorithm and length of the keys are directly proportional to data security, but inversely proportional to the computational power necessary to complete either of these processes. Encryption can be symmetric or asymmetric, depending on whether the key used to protect the data is identical to the one used to recover it. The need to use the same key in both operations makes the first type inherently less secure (since exchange of data requires transfer of the key, which introduces a potential for its compromise). This can be avoided with the second type, since individuals encrypting and decrypting data have their own, separate keys. However, asymmetric encryption is based on algorithms that are more complex and its impact on performance is more significant (which makes it unsuitable in scenarios involving larger amounts of data). It is possible to take advantage of the strengths of both methods by encrypting data with a symmetric key, and then protecting this key with asymmetric encryption.

The keys forming the pair used in asymmetric algorithms are known as public and private. Access to the private one is secured and available to its owner, while the public one lacks such protection. This way, the public key can be safely used for data encryption, since the ability to perform decryption is restricted exclusively to the holder of the corresponding private key. The asymmetric approach can also be used for digital signatures, where the sequence is reversed. A person distributing data, whose integrity needs to be ensured applies the private key to its content and stores the result with it. Recipients, on the other hand, use the corresponding public key to verify that content of the message has not been tampered with.

Two remaining questions that need further explanation are how the public and private keys are distributed to their intended users and how recipients of a public key can be certain that what they have received truly represents the identity of the holder of the corresponding private key. This dilemma is of significant relevance when securing Internet-based communication, where both sides do not necessarily have direct contact or know each other. The solution comes in the form of digital certificates. A certificate is a digitally signed piece of software that associates a public key with the identity of the private key owner, assuring its authenticity. The signature of the certificate is created using the same asymmetric algorithm already described in the previous paragraphs - i.e. with a private key of the certificate issuer (known as Certificate Authority) for which a certificate recipient has the corresponding public key. As you might already realize, there is an inherent problem with this approach - namely, how to assure the identity of the certificate issuer. To resolve this issue, Microsoft included a number of trusted certificate authorities (known as Trusted Root Certification Authorities), with the operating system. These certificate authorities are well-established institutions with impeccable credentials (the list can be modified, by adding or removing entries according to your preferences). Such institutions are responsible for verifying that organizations requesting certificates (their subjects) are really what they claim to be. They can also grant certificate granting privileges to other certificate authorities, which causes them to be implicitly trusted. Each certificate has a number of properties that reflect its purpose, such as information identifying its issuer and subject, along with the subject's public key and the digital signature of issuer and validity period. Certificates can be revoked by adding them to special lists maintained by their issuer, but responsibility for referencing revocation lists remains with applications that verify certificate validity (note that SQL Server 2005 Beta 2 built-in functions for encryption and signing do not perform such checks).

Certificates were used with earlier versions of SQL Server to configure Secure Sockets Layers (SSL) client connections; however, they had to be installed manually on the computer hosting the SQL Server instance. Starting with SQL Server 2005, all necessary encryption components are available natively. Each SQL Server instance has its Service Master Key, created automatically during setup, which is encrypted with a Data Protection API (using the credentials of the SQL Server service account) provided by the underlying Windows operating system. Its main purpose is to secure system data, such as passwords used in instance-level settings such as linked servers or connection strings). Service Master Key is also used to secure each of the Database Master Keys (protected additionally with a password supplied when a Database Master Key is created). Within each database, its master key serves as the basis for creating certificates or asymmetric keys, which subsequently can be applied to protect data directly or to further extend the encryption hierarchy (e.g. by creating symmetric keys). Creation, storage, and other certificate and key management tasks can be handled internally, without resorting to features of the operating system or third party products. Encryption and decryption is provided by pairs of functions complementing each other - EncryptByCert() and DecryptByCert(), EncryptByAsymKey() and DecryptByAsymKey(), EncryptByKey() and DecryptByKey(), as well as EncryptByPassphrase() and DecryptByPassphrase().

The database master key is created using the CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' T-SQL statement, where the administrator-defined 'password' encrypts the key and stores it in the sys.symmetric_keys catalog (this operation requires CONTROL permissions to the database). At the same time, the database master key is encrypted with the Service Master Key and stored separately in sys.databases, which facilitates its automatic opening. Once the database master key is created, you can proceed with creation of certificates and asymmetric keys.

In order to generate a new certificate, use the CREATE CERTIFICATE DDL T-SQL statement. It is possible to either create a new certificate, or use an existing one stored in a file, a signed executable, or an assembly. You can protect it with either a previously defined database master key or a password. In case of the latter, the password needs to be included in the CREATE CERTIFICATE statement. In our example, we will take advantage of an existing database master key (the assumption is that CREATE MASTER KEY has already been executed) and specify the certificate's name, subject, and validity period (running this statement requires CREATE CERTIFICATE permission on the database):

CREATE CERTIFICATE ElmerEncryptionCertificate
       SUBJECT = 'Elmer Certificate',
       EXPIRY_DATE = '01/01/2006 '

At this point, you are ready to apply the EncryptByCert function, which takes two parameters - the certificate identifier (which can be derived from the certificate name using the Cert_ID T-SQL function) and the string of characters (of nvarchar, char, wchar, varchar or nchar datatype) to be encrypted with it, such as in the following sample statement:

EncryptByCert ( Cert_ID('ElmerEncryptionCertificate'), 'Kill the Wabbit' )

The DecryptByCert T-SQL function, which returns original data, also requires a certificate identifier (which you can retrieve with the same Cert_ID T-SQL function) and the encrypted string of characters (if the certificate definition included a password, you would need to provide it here as well). Note that you can restrict the ability to decrypt encrypted data either by making the password known to a limited number of users or by applying an AUTHORIZATION clause when creating it (which specifies its owner).

DecryptByCert(Cert_ID('ElmerEncryptionCertificate'), @secretstuff)

The whole process can be illustrated by running the following sample script:

DECLARE @encryptedstuff NVARCHAR(100)
SELECT @encryptedstuff = EncryptByCert(Cert_ID('ElmerEncryptionCertificate'), N'Kill the Wabbit')
SELECT @encryptedstuff
SELECT CAST(DecryptByCert(Cert_ID('ElmerEncryptionCertificate'), @encryptedstuff) AS NVARCHAR)

Certificates are useful when securing Service Broker authentication and messaging mechanisms (in addition to ensuring the integrity of messages), protecting SQL Server authentication, signing modules (more about this in our next article), and encrypting data. Similar capabilities are offered by asymmetric keys, which can be set up in a database with the CREATE ASYMMETRIC KEY T-SQL statement. The statement provides the ability to either load an existing key pair from a file or an assembly, or to create a new one using the RSA algorithm with 512, 1024, or 2048 bit long private key. As with certificates, if a password is specified, it is used to encrypt the private key (in its absence, the database master key serves a similar role). Encryption is performed with the EncryptByAsymKey function, which accepts as its arguments the ID of an asymmetric key (which can be obtained from its name with AsymKey_ID function) and a character string to be secured. To decrypt the data, you need to run the DecryptByAsym function, which, as its first argument takes the same key ID as the EncryptByAsymKey, followed by the encrypted string of characters (and, potentially, the password, which has been used to encrypt the key during its creation).


DECLARE @encryptedstuff NVARCHAR(100)
SELECT @encryptedstuff = EncryptByAsymKey(AsymKey_ID('Asym_Looney_Tunes'), N'That''s all folks')
SELECT @encryptedstuff
SELECT CAST(DecryptByAsymKey(AsymKey_ID('Asym_Looney_Tunes'), @encryptedstuff) AS NVARCHAR)

Note that using asymmetric keys associated with certificates for encryption is considerably more complex (and therefore processor intensive) than performing the same task with symmetric keys (so the latter should be applied when dealing with large amounts of data). In order to create a symmetric key, you need to execute the CREATE SYMMETRIC KEY DDL T-SQL statement (which requires ALTER ANY SYMMETRIC KEY database level permissions). The statement allows you to specify the encryption algorithm (your choices include DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, AES_256, although their availability might depend on the underlying operating system). You also can decide whether the symmetric key will be encrypted with a certificate, another key, or a password. For example, in order to create a symmetric key called Sym_Loony_Tunes, with the DESX encryption algorithm, you would execute the following statement:

ENCRYPTION BY CERTIFICATE ElmerEncryptionCertificate

Using such a key involves running first the OPEN SYMMETRIC KEY T-SQL statement. Its format depends on whether you secured the symmetric key with a password, certificate, or another key. Since in our case, we used for this purpose the certificate, the syntax would be as follows:

CERTIFICATE ElmerEncryptionCertificate

All symmetric keys within a database are listed in the sys.symmetric_keys catalog, while the open ones are located in the sys.open_keys catalog. After a key is open, you can launch the EncryptByKey function, providing GUID of the key (which you can obtain by running the Key_GUID function with the key name as a parameter) and the character string (of nvarchar, char, varchar or nchar datatype) as its parameters. Decryption is performed by executing the DecryptByKey function with the input parameter referencing the encrypted string. To complete the process, you need to close the key. We are demonstrating the entire process with the following sample script below:

CERTIFICATE ElmerEncryptionCertificate
DECLARE @encryptedstuff NVARCHAR(100)
SELECT @encryptedstuff = EncryptByKey(Key_GUID('Sym_Looney_Tunes'), N'Be vewy vewy quiet')
SELECT @encryptedstuff
SELECT CAST(DecryptByKey(@encryptedstuff) AS NVARCHAR)

Finally, it is also possible to use symmetric encryption with a pass phrase as the key by executing the EncryptByPassPhrase function. Note that in this case, data security depends on the phrase complexity. To reverse the process, you need to apply the DecryptByPassPhrase function, providing the same pass phrase (along with encrypted data) as its argument.

DECLARE @cleartext NVARCHAR(100)
DECLARE @encryptedstuff NVARCHAR(100)
DECLARE @decryptedstuff NVARCHAR(100)
SET @cleartext = 'I''m hunting wabbits'
SET @encryptedstuff = EncryptByPassPhrase('l00n3yTun3z', @cleartext)
SELECT @encryptedstuff
SET @decryptedstuff = DecryptByPassphrase('l00n3yTun3z', @encryptedstuff)
SELECT @decryptedstuff

In case the built-in features described above do not satisfy your requirements, you can also take advantage of the encryption functionality implemented as part of the .NET Framework (classes within the System.Security.Cryptography namespace) to create user-defined functions.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site