Easier security is central to database upgrade
Database administrators can institute more effective practices using the new features in SQL Server 2008.
In summary:
| • | Microsoft SQL Server 2008 includes expanded and easy-to-use security features. |
| • | TDE eases the challenges of encrypting data. |
| • | New key management tools offer more flexibility. |
Security is more important than ever as networks increase their reach and as more value is derived from the information residing in corporate databases. Over the past few years, the IT community has worked to better define security and ensure that security can be more easily achieved. Microsoft has been at the forefront of this movement with efforts such as the Trustworthy Computing Initiative, a long-term, collaborative effort to create and deliver secure, private, and reliable computing experiences for everyone, which guides all software development at the company.
As part of this effort, the security capabilities of Microsoft SQL Server have continued to advance—and when you can get extensive security built into a database product, all the better, says Randy Dyess, practice manager, strategic initiatives, at Solid Quality Mentors, a Microsoft Gold Certified Partner that provides education and solutions. For starters, he praises Microsoft for providing a data encryption option starting with SQL Server 2005.
Unencumbered encryption
Yet in that earlier version, encryption was still a cumbersome, multi-step process, Dyess admits. Data had to be selectively encrypted before being stored in a table. Once encrypted, it could not be used again until it was decrypted. This meant that even something as basic as the SQL search function would ignore encrypted data. "If you had to look up a customer using their Social Security number—a piece of sensitive data which would probably be encrypted—you couldn't do it," he explains.
In contrast, Microsoft SQL Server 2008 offers a much more potent and usable approach to security. In particular, a new feature called Transparent Data Encryption is designed to provide protection for the entire database without affecting other applications. No complex preparations are required. Instead, TDE simply encrypts everything. That means all data types, keys, indexes, and so on can be used without worrying about losing data.
When TDE is enabled, the database is marked as such and the server starts a background thread that scans all database files and encrypts them. When an encryption scan is completed, all database files on the disk are encrypted, as are all the database and log file writes to the disk. The process works in reverse when TDE is disabled.
SQL Server 2008 also includes Extensible Key Management (EKM). This feature enables elements of the cryptographic key hierarchy to be managed by an external source, also called a cryptographic provider—for instance, the Hardware Security Module (HSM). Using these keys, the encryption and decryption operations are handled by the cryptographic provider. This gives users common key management and flexibility in picking cryptographic providers. Furthermore, TDE supports asymmetric keys that are provisioned by EKM.
Policy-based framework
Dyess says there are other appealing aspects of the latest release, particularly for database administrators concerned about security. The policy-based framework within SQL Server 2008 provides the ability to define policies that apply to servers, databases, and other objects in your data environment. The framework delivers the following specific capabilities:
| • | Management by Intent provides a logical view of the system configuration. That means administrators can more easily define the configuration of data services proactively rather than waiting for an issue to arise before responding. |
| • | Intelligent Monitoring monitors the system and actively prevents changes that deviate from the planned configuration. |
| • | Virtualized Management supports management across multiple servers, which simplifies the implementation of consistent configuration policies across the organization. |
"This gives DBAs the ability to standardize and control," says Dyess. Taken with other capabilities, such as the ability to query multiple servers at the same time and a resource governor to help classify incoming connections by work type, Microsoft SQL Server 2008 offers a much improved experience.
 | Alan R. Earls is a contributing writer for Momentum, the Microsoft Midsize Business Center newsletter. |
