SQL Server 2005 Express Edition - Part 6 - Post Installation Configuration Tasks (Encryption)

Friday Sep 14th 2007 by Marcin Policht

Part 6 of this series continues the discussion of network-based configuration tasks, describing other activities that alter default connectivity settings applied during standard installation, focusing in particular on encryption.

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, 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_MACHINE\MY -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.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site