f you care about your data, you must upgrade to SQL Server 2005 the day it is released. There simply is no other option.
An outrageous assertion? Perhaps. I tend to split my time equally between praising and bashing Microsoft, but the new security features and tools in SQL Server 2005 will be mandatory for protecting your data from today's increasingly sophisticated attacks. Most importantly, SQL Server 2005's many layers of security provide for defense in depth in which layer after layer of protection helps keep data safe.
This isn't to suggest that simply moving to the new database will automatically and fully protect your data. You'll still need to design database applications wisely and administer them proactively, knowing how to avoid creating custom security holes. But SQL Server 2005 provides the tools for making your data as safe as it can be against modern, sophisticated attacks.
In this article, I will explore several of the security features that are of interest to developers designing and implementing SQL Server-based applications. So, for example, I won't talk in any depth about server deployment and DDL triggers, since those are of more interest to administrators. I won't specifically cover the SQL Express editions, which replace the hard-to-use-and-deploy MSDE. I also won't cover ADO.NET 2.0. Much of what I'll cover here applies to these peripheral technologies as well, but their security issues deserve separate articles. I'll focus on the core database engine here, since the major add-on features such as Reporting Services and Service Broker each have their own set of security issues and features. Hopefully I can drill down into these other features in the months to come.
I'll start out with the features that help secure the database since that is what is most important to most developers. Then I'll look at some server-level features that will affect how you write your applications and I'll finish this article with a quick summary of other security features you should at least be aware of.
This article is based on the state of SQL Server 2005 in the June Community Technology Preview release. This release is allegedly close to what the final shipping version will look like, but SQL Server Books Online still has many placeholders for information to come.
The SQL Server 2005 Security Philosophy
|SQL Server 2005 is the first major release of the product since Microsoft got the security religion and began implementing its Trustworthy Computing vision. And boy, does it show!|
SQL Server 2005 is the first major release of the product since Microsoft got the security religion and began implementing its Trustworthy Computing vision. And boy, does it show!
There are four pillars on which the security features in SQL Server 2005 are founded, which you'll find traces of in every nook and cranny of the product:
- Secure by design. Microsoft performed thorough threat analyses that attempted to reveal every security vulnerability exposed to contemporary attacks. They took what they learned and made vast improvements to the "attack surface" exposed by SQL Server.
- Secure by default. Out of the box in a default installation, SQL Server 2005 is far more secure because many non-critical features are left uninstalled by default and those that are installed are turned off. This means that to use Reporting Services, for example, you have to explicitly install it. As a result, undiscovered security problems in features that are either not installed or are not activated do not provide an attack vector.
- Secure in deployment. Microsoft is providing various tools that help you make the right decisions about configuring the server and then monitoring it to make sure that it remains secure. For example, you can use the SQL Server Configuration Manager to set multiple settings to secure the database server. SQL Server 2005 is also going to be part of the Microsoft's online update services so that you don't have to manually download and apply security patches and service packs.
- Communications. Microsoft has pledged to make timely communication of threats, countermeasures, and tools so that you can respond to new and emerging threats.
Okay, this all sounds great and makes for some marvelous marketing material and conference demos. But let's step back a moment and run the B.S. detector over this stuff.... Surprisingly it rates midway on the scale, an historic low for Microsoft, even for server products where it tends to be more candid than other product classes. So what is the truth?
The fact of the matter is that SQL Server 2005 is an incredibly complex piece of software that is impossible to make 100% secure. You have my ironclad guarantee that there will be security issues that arise within days of its release. The issues will be a mixture of creative new attacks that no one can even envision today as well as attacks that probe old security problems that we all thought were fixed long ago. And even if SQL Server were perfect, inexperienced developers who are under intense deadline pressure will unwittingly build custom security holes in their applications that provide attack vectors that attackers dream about.
What's different with SQL Server 2005and why my opening statement is sincereis that Microsoft seems to have produced a marvelously secure product that doesn't provide easy attacks and has made it resilient enough to provide defense in depth so that one security flaw that leads to one successful attack doesn't mean complete collapse and automatic data loss.
The bottom line: It is far more likelydramatically more likelythat you'll be able to withstand attacks using SQL Server 2005 than any prior version of the product. No guarantees, of course, but at least attackers are going to have to work far harder to get at your data.
Let's look at why this is the case, starting at the database level.