SQL Server 2005 Security – Part 3 Encryption

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

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

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles