Next month, Microsoft adds a star--studded cast of security features to its database system.
When SQL Server 2005 debuts next month, you'll see a lot more than the new feature functions you may have heard about, such as native XML support and .NET integration. A lot of the buzz around this release is security, as this is the first major SQL Server release since Trustworthy Computing was instituted in 2002.
Some of these improvements are incremental, such as enhancements to existing SSL support and network login security, while features such as data encryption and user/schema separation bring powerful new protection capabilities.
Security managers are bound to stand up and take notice of these and other dramatic security enhancements to the popular database server.
We'll review some of the most significant of these new and improved features, starting with one of the philosophical mantras of Trustworthy Computing, "Secure by default."
|Off by default|
These SQL Server 2005 features are turned off out of the box, so DBAs can choose to run the ones they need and can prevent attackers from exploiting unused services.
Use Only What You Need
Windows Server 2003, the first showcase for Trustworthy Computing, placed a strong emphasis on disabling non-essential services out of the box. In the past, Microsoft turned on most services by default.
The problem was that users often weren't even aware that these unused services were running--but attackers were. These services increased the surface area of the network, further exposing it to vulnerabilities.
Off by default is the new rule applied religiously to SQL Server 2005. Like its predecessors, SQL Server 2005 arrives with a plethora of new features. DBAs can cherry-pick what they need, enabling features individually through the product's new Surface Area Configuration utility, which enumerates potential exploitation points and turns off unnecessary functionalities.
An example of an exploitable point turned off by default in SQL Server 2005 is xp_cmdshell. This extended stored procedure allows highly privileged users to run Windows commands (like the "dir" command) against the underlying operating system; it's also used by some intrinsic features like replication.
Other examples of off-by-default features are entry points into a SQL Server instance using SQL Server Service Broker (a new messaging system), defined as a Service Broker endpoint. Endpoints aren't defined by default, but through certificate- or Windows-based security. Another security precaution: Broker messaging between instances must be encrypted.
Secure in the knowledge that potentially dangerous services are disabled until they decide otherwise, organizations should conduct a complete analysis to determine when, where, and how to make use of the new SQL Server 2005 features--or whether to use them at all.
For example, when you first install SQL Server 2005, features that must be enabled include services and connections (with separate SQL Server browser and Integration Server services), ad hoc remote queries, non-SQL programming components like COM auto-mation, xp_cmdshell and SQLCLR, and endpoints for Web services and service broker communication. From there, the configuration utility allows you to turn on other SQL Server services, such as Analysis Server and Reporting Services (see chart above).
Perhaps the most noticeable--and most requested--new security feature is built-in data encryption and its associated key management.
More and more industries are implementing selective, if not blanket, encryption for security and privacy -- a trend driven by increasing identity theft, privacy initiatives and compliance with regulatory controls. This is a major advance for SQL Server, allowing implementation of a wide range of encryption algorithms. Key and certificate creation and management are now handled natively, without purchasing third-party products.
SQL Server's implementation of data encryption is very granular, allowing data in the same column but different rows to be encrypted with different keys; data--based, rather than column--based, encryption permits highly flexible and specific applications. For example, doctors at a hospital could see private data for their own patients, but only the name and assigned doctor for other patients. Each doctor would be assigned a certificate or symmetric key, which would be used to encrypt and decrypt patient information.
Encryption keys are stored as ordinary database objects; they are maintained with the rest of the database. The keys are stored in a multilevel hierarchy of database secrets, subject to SQL Server permissions as with all database objects. The hierarchy enables the administrator to change a specific or master key at the database or instance level without having to re-encrypt every secret in the database.
SQL Server 2005 can create and store symmetric keys, asymmetric keys and certificates. It can also store asymmetric keys and certificates obtained from a flat file, and extract and store these keys from a .NET assembly or an executable file. Each instance has a master key generated at installation that's used to encrypt instance-level secrets (for example, an SSL certificate used for SQL Server's native Web service support) and database master keys, which are password-protected and used to create keys and certificates.
More Secure Logins
Early versions of SQL Server allowed users to log in through Windows logins or a SQL Server-specific user ID and password. The Windows login was much more secure, but SQL logins were occasionally needed to solve separation of domain issues in firewall configurations. If you're using Windows Server 2003, you can now implement common, stronger policies for integrated Windows/SQL Server logins.
SQL Server 2005 features tough policies for passwords available through Windows logins, such as password complexity and history, and account lockout rules (see chart below); these stronger policies are now on by default.
The network login protocol for SQL logins has also been tightened, requiring logins to be encrypted across the wire. There is also a hard--coded password complexity policy when SQL Server 2005 is installed on earlier operating systems, such as Windows 2000 and XP.
|Security policies for windows & SQL server 2005 logins|
When used with Windows Server 2003, SQL Server 2005 can enforce strong, integrated login policies--a must have for security administrators.
In previous versions of SQL Server, you could learn a lot about a database just by perusing and retrieving metadata information, which lists items such as the tables in a database and the SQL code of database objects.
In past versions of SQL Server, these were public--users (or hackers)-- could see lists of table names even if they weren't permitted to access the tables themselves. This information serves as a kind of reconnaissance to help attackers determine where to focus their energy.
All this changes in SQL Server 2005. The metadata has been revised, but old as well as new metadata tables and functions that list SQL code (like sp_helptext) have permission-based access. No one without access to the table can see the metadata without special permission.
Separating Users and Schemas
In previous versions of SQL Server, tables were named after the user that created them. For example, when a database user named Fred created a table called "table1," the name of that table was fred.table1. The exception to this rule occurred if Fred was a "database owner" (DBO).
While user-named tables are convenient to create database objects belonging to a specific user, they open databases to considerable risk and management head-aches. If Fred changes projects or leaves the company, the DBA has to transfer his objects to a different user.
From a security point of view, the administrator might forget to remove Fred's ownership of these database objects, leaving unauthorized legacy privileges. In a worst-case scenario, Fred might be a disgruntled former employee, perhaps working for a competitor. And, if programmers had used multipart database object names (such as pubs.fred.table1) in their source code, the code would also have to be changed and coordinated with object name changes. Besides being a DBA's nightmare, it's easy to miss names of a former employee's tables in data access code, especially if it's long and complex, opening up potential security holes and perhaps causing the code to break.
Rather than deal with these headaches, most DBAs require all database objects to be owned by a DBO. However, this only changes the nature of the problem. Application packages, which create database objects, either have to be installed and maintained by the DBA, or the package installer has to be given the DBO role. If the installer is given the DBO role, he then has complete access to all database code, data and functions, leaving the door wide open to exploitation. SQL Server 2005 solves this problem by separating schemas from user names, making them more secure and easier to manage.
Schemas, which are really containers for database objects, can now also be owned by a database role in addition to a user. (Application roles are also available, but they have no users.) In addition to the privilege of creating tables, the user must also have access to a schema in which to create them. The fact that schemas can be owned by database roles reduces the security risks we've described and is a real administrative convenience.
So, if Fred is installing a payroll package, the DBA can create a payroll schema owned by the payroll database role. If payroll is Fred's default schema, that's where all of his objects will be created. If Fred leaves the payroll department but stays in the organization, the DBA can just remove Fred from his old role and add him to the new one. If Bob takes over Fred's responsibilities, the DBA adds Bob to the payroll role. No transfer of object ownership or code change is required, and that means less chance to inadvertently expose secure data.
Ultimately, good database security depends on sound policy, best practices and DBAs who value security. That being said, SQL Server 2005 gives organizations a far more robust security tool set than its pre--decessors, and Microsoft's prevailing "secure by default" philosophy will prevent a lot of nasty surprises.
Dig Deeper on Database Security Management-Enterprise Data Protection
SQL Server management trick: Connecting when admins are locked out
FAQ: How to troubleshoot and grant SQL Server permissions
SQL Server high availability when upgrading to SQL Server 2005By: Matthew Schroeder
Track changes to SQL Server 2000 and 2005 with one simple utility