Database Encryption


One of the major new features in SQL Server 2005 is the new security model for database encryption. Developers need to understand exactly how the new encryption model functions. One of the most common feature requests in earlier versions of SQL Server was for the ability to natively encrypt and decrypt data stored in the database. As SQL Server became more popular for online shopping and other data storage that required robust encryption techniques, Microsoft decided that it should be a functional part of SQL Server 2005.

The data encryption functionality in SQL Server 2005 is very closely compliant with the international X.509 v3 certificate standard, which means that any application built using X.509 certificate encryption can utilize SQL Server 2005's built-in encryption methods. The database encryption is built on a hierarchy of certificates, keys, and data, as shown in Figure B-16.

image from book
Figure B-16: The SQL Server 2005 database encryption hierarchy

Certificates

A public key certificate is a digitally signed object that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate. Certificates contain the following information:

  • The public key of the subject.

  • The identifier information of the subject, such as the name and e-mail address.

  • The validity period. This is the length of time that the certificate is considered valid. A certificate is valid only for the period of time specified within it; every certificate contains Valid From and Valid To dates. These dates set the boundaries of the validity period. When the validity period for a certificate has passed, a new certificate must be requested by the subject of the now-expired certificate.

  • Issuer identifier information.

  • The digital signature of the issuer. This signature attests to the validity of the binding between the public key and the identifier information of the subject.

A primary benefit of certificates is that they relieve hosts of the need to maintain a set of passwords for individual subjects. Instead, the host merely establishes trust in a certificate issuer.

When a host, such as a secure Web server, designates an issuer as a trusted root authority, the host implicitly trusts the policies that the issuer has used to establish the bindings of certificates it issues. In effect, the host trusts that the issuer has verified the identity of the certificate subject. A host designates an issuer as a trusted root authority by putting the self-signed certificate of the issuer, which contains the public key of the issuer, into the trusted root certification authority certificate store of the host computer. Intermediate or subordinate certification authorities are trusted only if they have a valid certification path from a trusted root certification authority.

The issuer can revoke a certificate before it expires. Revocation cancels the binding of a public key to an identity that is asserted in the certificate. Each issuer maintains a certificate-revocation list that can be used by programs when they are checking the validity of any given certificate. SQL Server 2005 encryption uses the following key types for encryption:

  • Asymmetric keys An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are fairly expensive operations, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database.

  • Symmetric keys A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast and is suitable for routine use with sensitive data in the database.

Configuring SQL Server for SSL

One of the most common uses of certificate-based security in SQL Server 2005 is configuring and utilizing Secure Sockets Layer (SSL) communication between clients and the server. SQL Server 2005 can use SSL to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients.

SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server. Encryption with a self-signed certificate is possible, but a self-signed certificate offers only limited protection.

Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance. When all traffic between SQL Server and a client application is encrypted using SSL, the following additional processing is required:

  • An extra network round-trip is required at connect time.

  • Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.

  • Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

For SQL Server to utilize certificates for SSL encryption, they must be valid and must meet the following conditions:

  • The certificate must be in either the local computer certificate store or the current user certificate store.

  • The current system time must be after the Valid From property of the certificate and before the Valid To property of the certificate.

  • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).

  • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).

  • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

To configure SQL Server to use SSL, you must perform the following steps in order:

  • Install a valid certificate into the certificate store of the SQL Server machine.

  • Start the SQL Server Configuration Manager, as shown in Figure B-17.

  • Expand the SQL Server 2005 Network Configuration node, right-click on the Protocols node.

  • Select the certificate you want to use from the Certificate drop-down list box and click OK, as shown in Figure B-18.

image from book
Figure B-17: Using the SQL Server Configuration Manager

image from book
Figure B-18: Using the SQL Server Configuration Manager to install a certificate

Once SQL Server is configured for SSL communications, clients can be configured to support encrypted communication between them and the SQL Server, ensuring that the data transmitted is secure.




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net