Another one of the security problems with SQL Server 2000 and its predecessors is that any user with access to the server or a database could view the metadata for the server or database. In other words, query access to most system tables was unrestricted, simply because the permissions infrastructure in earlier versions of SQL Server wasn't capable of keeping track of who had what kind of permissions on what objects. The result is that once a hacker got almost any kind of toehold into the server or a database, she was able to explore the structure almost without constrainta huge security problem.
Two things have changed in SQL Server 2005 to relieve this problem. First, the granular permissions that SQL Server now has, allowing fine-grained control over what principle has what permissions on what objects. Second is that the system tables as we have grown to know and hate them are largely gone from SQL Server. (They are still around for backward compatibility but have not been enhanced to support new features.) In their place are a set of catalog views that provide most of the same functionality as querying a system table in SQL Server 2000. For the most part the names are similar to the old system tables, with names like sys.columns
, and sys.servers
, along with many new views to expose information about new features. Hopefully you now recognize that sys is a system-level schema that contains the system catalog views.
Using catalog views instead of relatively direct access to system tables has two important effects, one good and the other really
good. First, because the catalog views are read-only, you won't have to worry about learning all kinds of new hacks to directly modify system tables and potentially crash the server. Second, SQL Server can filter the results of querying a view to display information for only the objects that the caller has permission to see. This feature is officially called Metadata Visibility Configuration. For example, if the PlainJane
user has access to a database but no permissions on its Customers
table, a query to sys.tables
in that database will show nothing about the Customers
table. As a result, a hacker that gets access to the database by hijacking the PlainJane
user without ownership or admin privileges will never know about the Customers
Even better, an attacker can't even guess about the existence of a Customers
table. A blind attempt to drop the table receives this error message, which neither confirms nor denies the existence of the table.
Msg 3701, Level 14, State 20, Line 1
Cannot drop the table 'Customers', because it does not exist or you do not have permission.
This level of protection extends to system stored procedures that expose information about objects, such as sp_help
By itself, metadata security won't directly prevent attacks. But it does mean that when someone does get unauthorized access to the server or a database it will be much harder to find juicy data or worthy attack targets.
What Else Is There?
I've barely been able to scratch the surface in this article about the security enhancements in SQL Server 2005. But hopefully you now have a feel for just how fundamentally more secure this version is, with plenty of options for locking down the server, databases, and the data within. There are plenty of other security features to explore, such as:
- Secure deployment of the server, with all but the most critical, core database features either not installed or turned off. For example, CLR integration is turned off so that you have to explicitly turn it on to be able to run .NET code within the server.
- Strong password policies that are tied into the Windows password policy. If you use SQL logins, you can have minimum password complexity standards, expire passwords periodically, and lockout a user after too many incorrect login attempts.
- Strong authentication for HTTP endpoints if you use SQL Server as a Web service endpoint in a Service Oriented Architecture (SOA) application.
- A new Computer Manager tool that replaces the Client Network Utility, Network Utility, and Service Manager.
- A dedicated administrator connection that allows access to a server even when it is otherwise not responding or is unavailable.
- New SQL Management Objects (SMO) that replace SQL-DMO and provide programmatic access to every nook and cranny of SQL Server, including security settings.
- New proxy accounts for SQL Server Agent and flexibility on permissions.
- Code signing using encryption that prevents anyone from getting access to tables and other securable objects other than through the signed code.
- DDL triggers that fire when the target object is changed, such as when a field is added to a table. This can help detect unauthorized changes to the database by attackers or by well-meaning but destructive power users.
- Database snapshots that let you quickly recover from problems, security-related or not.
- CLR security that addresses the nervousness that some database administrators have confessed to when contemplating the prospect of .NET code running inside a database. From a developer's perspective, this is a cool feature in how it blends the security infrastructure of SQL Server with that of .NET's Common Language Runtime (CLR).
- And, at the risk of offending the cliché police, much, much more!
The release of SQL Server 2005 in early November will present a brave new world for developers and administrators who have to deal with protecting data. SQL Server will be newly equipped to withstand the most insidious of today's attacks and ready to adapt to those that we haven't even dreamed of yet. Assuming that Microsoft comes through on all its promises, data will have never been so safe.