In the previous
installment of our series dedicated to SQL Server 2005 Express Edition, we
have reviewed a number of network-based configuration tasks that are typically
introduced following the initial setup process. In our new article, we will
continue coverage of this topic by describing other activities that alter
default connectivity settings applied during standard installation, focusing in
particular on encryption.
Encryption can be employed in SQL Server 2005 Express Edition in a variety
of ways to protect sensitive data during its transit and storage (with granularity
down to individual field level). In the context of this article, we are
interested in securing communication between the database engine and its
clients. This is accomplished using a mechanism that relies on X.509
certificates. Before we explore its implementation in more detail, let’s take a
closer look at the principles of asymmetric cryptography and its main
components, including public and private keys. A public key is a unique
identifier that is associated with an entity (for example, a business with
Internet presence). Such key, as the term indicates, is readily available to
anyone that is interested in a secure electronic data exchange with its owner.
Each public key has a corresponding private key that is in the exclusive
possession of the same entity and which confidentiality is closely guarded. The
two keys closely interact (by implementing mutually opposite actions via
reverse computing algorithms) in order to provide security-related
functionality, which most commonly involves either encryption or digital
signing. In the case of the former, information to be protected is obfuscated
using a public key (note that this can be done by virtually anyone). Secured
content can then be retrieved by decrypting it with the corresponding private key,
known only to its holder. Digital signatures are intended to ensure data
integrity and confirm the identity of its source (rather than to protect it
from unauthorized viewing). To accomplish this, a private key is used by its
originator to capture message characteristics in the form of a code sequence
(known as digest) and include it with the original content (making it possible
to detect whether any subsequent changes were applied to it). Verifying
authenticity of the signer and data is done with the corresponding, easily
obtainable public key.
While both mechanisms deliver expected functionality, their effectiveness
depends largely on the validity of public keys. After all, how exactly can
various parties utilizing them for secure data exchange be certain that they
actually represent a particular entity? This is where certificates come into
play. Their concept is based on the existence of a trust between a keys issuer
(known as the Certification Authority) and all of its prospective clients (for
example, Windows operating system installations are pre-configured with a
number of well-known third-party certificates as their Trusted Root
Certification Authorities), which means that such clients acknowledge the legitimacy
of the publisher (and, in consequence, validity of all keys published by it).
Every new public key created by a Certificate Authority is signed with its
private key and, therefore, can be safely accepted by everyone who is already familiar
with this publisher (by having a copy of its public key). The outcome of this
process, consisting of a public key identifying a particular entity issued by a
Certificate Authority, which also digitally signed it using its own private key
is called a certificate. In addition to the public key of the designated owner,
a certificate also contains such information as version and serial number,
issuer name, validity period (determining expiration date), subject name
(identifying the owner), cryptography algorithm identifiers (necessary for decrypting
the signature of the publisher and proper use of the public key), and
certification path (this relates to the fact that Certification Authorities can
form a hierarchy, where issuers obtain their own certification from upstream
publishers).
SQL Server 2005 Express Edition not only takes advantage of X.509
certificates but actually requires one in order to start (this behavior can
lead to problems if the installed certificate is invalid or inaccessible, as
explained in the Microsoft Knowledge Base article 900497). By default, this is
an auto generated self-signed certificate, but you might want to consider
installing one issued by a trusted Certificate Authority, since the former, while
capable of encrypting client/server communication, does not facilitate mutual
authentication and nonrepudiation (which introduces a potential for
man-in-the-middle attacks, where a malicious party impersonates a legitimate
host). Depending on your preferences (and according to server settings, which
we will discuss shortly) one of them gets loaded at the database engine startup
(occurrence of this event can be confirmed by reviewing the content of SQL
Server or Windows Application logs).
Even though credentials submitted during remote login session are always
encrypted, you will need to enable encryption explicitly if you intend to additionally
protect data transmitted between a database engine and its clients. This can be
accomplished by manipulating security-related settings on the server and its
clients. When dealing with Native Client software, the easiest way to produce
the desired outcome is via the SQL Server Configuration Manager interface. Its
SQL Native Client Configuration Properties dialog box contains two entries
(each with Yes and No as possible values):
-
Force Protocol Encryption – determines whether all traffic from
this client to every SQL Server instance is encrypted, regardless of the
options assigned on the remote end. The encryption process can utilize either a
CA-issued or self-signed certificate. -
Trust Server Certificate – dictates whether the client will
accept any server certificate (including a self-signed one) or will require one
that was generated by a mutually trusted publisher (in which case, the client
must trust the Certificate Authority that issued the server certificate – or
one of its parents, when dealing with CA hierarchy). To enforce validation of
CA-issued certificates on the client, you need to turn on Force Protocol
Encryption and turn off the Trust Server Certificate option.
On the server side, the SQL Server 2005 Network Configuration node in the SQL
Server Configuration Manager console contains separate Protocol subnodes for
each of the locally installed instances. Their respective Properties dialog
boxes contain two tabs:
-
Flags – which, in addition to enabling the "Hide
Instance" option that we described in our previous article, allow you to
ensure that all traffic is encrypted (with the "Force Encryption"
setting), regardless of client settings. -
Certificate – designates a CA issued certificate that should be
used for encryption. Once you select it with the top listbox, the fields below
(Expiration Date, Friendly Name, Issued By, and Issued To) are filled
automatically, displaying its more relevant properties.
In order to be able to populate the Certificate entry in the Protocols
dialog box for the SQL Server 2005 Express Edition instance in the SQL Server
Configuration Manager, you need to obtain an appropriate certificate and import
it into the local computer’s certificate store. If you have Microsoft
Enterprise Root Certificate Authority installed in your Active Directory forest
to which the system hosting SQL Server instance belongs, the required procedure
is fairly straightforward, since the Rot CA certificate is automatically added
to the Trusted Root Certification Authorities store on all members of its
domains. (If this is not the case, you can employ alternative methods, such as
Web Enrollment services, or even resort to the MakeCert
utility, provided that you limit its use to testing only). Start by launching
Microsoft Management Console (by executing the MMC command directly from the
Start->Run box) and add to it Certificates snap-in pointing to the Local
Computer store. Via the View/Options menu, organize the view mode according to the
Certificate’s purpose. Locate the Server Authentication folder and from its
context sensitive menu select the Request Certificate option (submenu of All
Tasks menu entry). This will display a screen giving you the ability to request
a Computer certificate (suitable for both Server and Client authentication).
With default auto enrollment settings of Certificate Authority enabled, you
will be able to download and install the certificate right away (otherwise, you
will have to wait for an explicit approval from a CA manager). Ensure that the
subject name of the certificate matches the name of the local computer (from
which the request originated), its enhanced key usage entries contain the
Server Authentication OID string 1.3.6.1.5.5.7.3.1, and its CSP is set to
Microsoft RSA SChannel Cryptographic Provider. (You can confirm this by
reviewing the properties of the newly obtained certificate in the Certificates
MMC snap-in).
Go to the earlier described Certificate entry (to navigate there, launch SQL
Server Configuration Manager, drill down to Protocols subnode of the target
instance under SQL Server 2005 Network Configuration node, launch its
Properties dialog box, and switch to the Certificate tab). Provided that your
certificate has been correctly installed and satisfies the criteria listed
above (proper OID string and subject name), it should appear as a choice in the
Certificate listbox. Select it, verify its properties, and confirm your choice
by clicking OK and closing the dialog box. At this point, you will be notified
that the changes will not take effect until the database engine is restarted.
It is likely, however, that if you attempt to do this, SQL Server Service will
fail. This is due to the fact that, by default, a SQL Server 2005 Express
Edition instance operates in the security context of Network Service account,
which does not have sufficient permissions to access the local computer’s
certificate store. To resolve this issue, you need to use Windows HTTP Services
Certificate Configuration Tool (WinHttpCertCfg.exe), available from the
Microsoft Download
Center. Assuming that the subject of certificate (which should match the
fully qualified domain name of the computer hosting your installation) is SQLExpress06.databasejournal.com,
you would execute the following from the Command Prompt (once this command
successfully completes, you should be able to restart the database engine with
the new, CA-issued certificate in place):
WINHTTPCERTCFG -g -c LOCAL_MACHINEMY -s "SQLExpress06.databasejournal.com -a "Network Service"
In order to allow SQL clients to validate a server certificate, you need to
make sure that its publisher’s certificate (in our case, the certificate of
Microsoft Enterprise Root CA) is added to their Trusted Root Certificate
Authorities local computer stores. If the client systems reside in the same
forest as the server hosting target SQL Server 2005 Express Edition instance,
this is already the case (since, as we stated earlier, the Enterprise Root CA
certificate is automatically added to Trusted Root Certification Authorities
store on each member of the same forest). Otherwise, refer to instructions
included in the Microsoft Knowledge Base article 316898. In short, the
procedure involves exporting the Root CA certificate from the server’s Trusted
Root Certificate Store and importing it to the equivalent location on the
client.
In your pursuit of security, do not forget about the impact of encryption on
performance. Employing certificates to protect transmitted data not only
slightly increases the amount of network traffic when a connection is
established, but also, more importantly, places an additional burden on system
resources of the SQL Server computer and its clients. If this happens to be a significant
factor affecting feasibility of your implementation of SQL Server 2005
encryption, consider alternative technologies, such as IPSec, where it might be
possible to offload the majority of processing tasks to a specialized hardware.
In our next article, we will continue exploring the topic of
post-installation configuration tasks, shifting our focus from the area of
security to management.
