Native Data Encryption

Encryption is a mechanism for protecting data by obfuscating its content by a kind of masking or encoding. This process can be reversed by unmasking or decoding the content. This process can be symmetric or asymmetric depending on the key used to encrypt and decrypt. Of the two, asymmetric encryption is more complex and performance impact is better. Users can use both methods to doubly protect data.

There are two keys used in asymmetric encryption known as a public key and a private key. Access to the private one is available to the owner while the public key does not have this umbrella of protection. When data is encrypted using the public key, decryption can be done only with the private key. Users receiving a encrypted message can ensure that content has not be tampered with, by using the public key but they cannot access the content.

Closely associated with the process described above, is the Digital certificate. The digital certificate is a digitally signed piece of software that associates a public key with the identity of the private key owner. The signature of the certificate is created by an algorithm using the private key of the Certificate Authority and the corresponding public key is available with the recipient. A number of trusted certifying authorities have been included in the operating system known as Trusted Root Certification Authorities. This list can be added to or modified by the user. Each certificate has a number of properties that reflect the purpose and the subject’s public key and digital signature of the issuer. The application must verify the certificate validity by referencing revocation lists. SQL Server  has built in encryption functions to perform such checks.

All required encryption components are available natively. Each SQL server instance has its Service Master Key and is created during the setup. This is encrypted with a Data Protection API provided by the Windows Operating system. The system data is secured using passwords at instance-level settings. Service Master keys secure each Database Master key and within each database, its master key is used to create certificates or asymmetric keys. These can be finally applied to protect data directly or further extend the encryption hierarchy. All certificate management tasks are handled internally by SQL Server 2005. Encryption and decryption are done by function pairs—EncryptByCert() and DecryptByCert() for instance.

The master key is created using the CREATE MASTER KEY ENCRYPTION BY PASSWORD= ‘password’. The administrator defined password is encrypted and stored in the sys.symmetric_keys catalog if CONTROL permissions to the database are available. Simultaneously the database master key is encrypted with the Service Master Key and stored separately in the sys.databases which facilitates automatic opening. The creation of certificates and asymmetric keys can now be done using the CREATE CERTIFICATE DDL T-SQL statement.

A certificate can be new or existing. It will be stored in a file as a signed executable or an assembly. It can be protected using a previously defined database master key or password. If password is used, it must be included in the certificate. The EncryptByCert function takes the certificate identifier and a string of characters as parameters for encryption purposes. It follows that the DecryptByCert function also takes the same parameters to retrieve and decrypt the data.

Certificates are also useful in securing the Service Broker authentication and messaging mechanisms or protecting SQL server authentication, signing of modules or encryption of data. Similar capacities are offered by asymmetric keys though the process is more complex.

In addition to the above functionalities the .NET Framework also allows the creation of user defined functions for encryption.

Code and Module Signing

Signing is the process of verifying authenticity of origin of data and its integrity. Like encryption it relies on certificates for functionality. Verification of integrity involves applying a public key to it and comparing the outcomes with the digital signature. Additionally SQL Server ensures that a particular resource can be accessed only via a module(such as a stored procedure). This functionality has been enhanced with the potential to use T_SQL statements. The digital signatures provide the mapping of user with digital certificate without recourse to ownership chaining.

SQL Server Agent Operations

SQL server no longer requires the Agent to be a member of the local administrators group. It is possible to create multiple proxy accounts and assign separate accounts to individual steps of the Agents jobs. Permissions on SQL Server Agent jobs has also been changed. The user must be a member of the SQLAgentUserRole in the msdb.Management for the purpose of creating roles. Proxy accounts can be created by Sysadmin fixed server roles.

Monitoring and Auditing

SQL profiler can be run without granting membership in the SysAdmin fixed server role. Auditing includes SQL Server 2005 activities and events generated by SQL Server 2005 Analysis services. Enhanced auditing is possible by assigning triggers to Data Definition language operations.

In this lesson we have attempted to provide the reader with a broad overview of the new features of SQL Server 2005. No attempt has been made to demonstrate the usage of these various components since the same was beyond the scope of this tutorial.

First Page: Security Features in SQL Server 2005 for the Developer