Introduction to SQL Server Security

Solutions in this chapter:

Introduction

This chapter explains why you should be concerned with SQL Server security and introduces some of the more generic ideas, such as the principle of least access. It also covers the concept of planning for security in the design phase, building security into your application from the ground up, as opposed to “bolting it on” afterward, and installing and configuring SQL Server features. We also discuss the security risks associated with managed code in SQL server. CLR integration is the feature that allows managed code to be run in SQL server.

Multifaceted SQL Server Security

Security in SQL Server 2005 is multifaceted, and it can seem impossibly complicated. SQL Server 2005 security starts at the ground level and builds upon itself. This chapter discusses producing the foundations required to begin thinking in a natively secure manner, upon which the rest of the security principle in this book can be built. This chapter also starts you on the learning curve required to implement SQL Server 2005 security by providing a guide in your journey into SQL Server 2005 security.

Security: Why Worry About It?

In February 2000, the company RealNames informed its customers that its database had been broken into and that information including credit card numbers had been taken. The thought of being the person in charge of security on that database is enough to make anyone break into a cold sweat. How exactly do you go to your boss and tell him that the database that fuels your company and holds your customer’s information has been broken into?

Then there were the W32.CBlade and W32.Digispid worms. These worms attacked SQL Servers using the SA account and a blank password. The fact that either of these two worms could get into systems spoke volumes about the security of the databases they were attacking. The one positive aspect was that when the SQL Slammer worm hit in 2003, IT security professionals had some knowledge of how databases are attacked by worms. Even more fortunate was that even though the Slammer worm was one of the most aggressive worms to date, it was dedicated to creating a denial-of-service (DoS) type attack where the goal was to flood the Internet with traffic, versus a database breach.

In 2001, the World Economic Forum had a database breach. Some of the information from this breach ended up on a Swiss newspaper’s Web site. The data, including Bill Gates’s e-mail address, PepsiCo Beverages CEO Peter Thompson’s credit card number, and Jeff Bezos’s home phone number were taken. Additionally, some 800,000 pages of data from people like Bill Clinton, Vladimir Putin, and Yassir Arafat were accessed using the passwords acquired when the database was breached.

Other companies whose databases were breached were PetCo.com and Guess, both of which fell victim to SQL Injection attacks on their Web sites. The attack on Guess netted the attackers an unknown quantity of credit card numbers. PetCo.com’s Web site was later detected to have the same vulnerability. This vulnerability was detected by someone randomly checking sites for this issue, and would have provided about 500,000 credit card numbers to anyone less honest who discovered this vulnerability.

Information is money. Other than credit card numbers, there are people willing to pay for phone numbers, e-mail addresses, physical addresses, client information, social security information, and just about every other form of client or personal information available. With this as a driving force, people looking to make money, see databases as a bank vault full of money. Just as banks build their buildings with plans on how to secure their vault, you need to protect your information in your databases the same way.

Now that the Sarbanes Oxley (SOX), Gramm-Leach-Biley Act (GLBA), Health Insurance Portability and Privacy Act (HIPAA), Basel II, Code of Federal Regulation (CFR) Part 11, and the Japan Privacy Law (J-SOX) regulations are becoming the model for governments, private companies, and public companies across the globe, more and more companies are being affected even if these regulations do not apply directly to them. These regulations do not state what needs to be done in crystal clear terms. They hold you liable for the security of your information, but leave it up to you on how to interpret what they are saying.

It seems that in all of the preceding cases, there were security precautions that could have been taken to prevent the breaches. In most cases, applying the best practices of securing a SQL Server would prevent breaches from ever happening. It is just a matter of knowing what to secure, which is where this book comes in.

The Principle of Least Access

The principle of least access is a simple way to make databases more secure. Whenever you are presented with a choice of how to configure permissions, choose the method that provides the least access to the database. This goes for everything at every level. If you are asking yourself, “Do I need to install this feature?” the answer is either a definite yes or a definite no. If you are thinking maybe, possibly, “well, we might…”, do not install it. If you think that this person might need access to a specific extended stored procedure, then they do get access. This also applies to the level of service accounts that run your SQL services. Start with the most secure setting, and only open it past that point of what you need it to do.

This is now a constant in the world of SQL Server 2005. By default, Microsoft has made things secure for when you start working in SQL Server 2005. But in order for it to work, it means resisting the urge to make sure everything works as it should by turning on everything and giving everyone owner permissions. Yes, it can be annoying when someone keeps coming back to your desk because they need more access to get their job done, but in order to offset this, keep in mind the person in charge of the RealNames database and how his day was when it was announced publicly that the database had been hacked.

Installing SQL Server

Let’s start from the point of installing SQL Server 2005. From the time you put the disk into the machine, think security. At the beginning you will be asked what you want to install. There is the database engine itself, which is only installed when you are going to be immediately housing SQL Server 2005 databases on that server. Next there is the analysis services engine, which is the OLAP/data cubes portion of SQL Server 2005. This should only be installed if you are going to be immediately using OLAP cubes on the server. Additionally, there is Reporting Services, Integration Services, and Notification Services. Reporting Services is SQL Server 2005’s Web-based reporting engine. Integration Services lets you design and deploy SSIS packages, the replacement for DTS. Notification Services provides the engine for keeping people notified. The same principle applies to these—install them only if you are going to use them immediately. Documents and samples is the last optional choice in installing SQL Server 2005, and is also the easiest to decide on whether to install or not. If you are installing on a development box, it is usually a good choice to choose to install both of these. For installation to any other server, never install the Documents and Samples. These are meant for learning, and although they have been reviewed to make sure that they follow Microsoft’s best practices, they provide no benefits when installed to a production environment.

One note at this point has to do with the experience we had installing SQL Server. Many times we have installed SQL Server, adding in extra components because we were told that they would be needed next week, next month, or immediately. Although in every case the person who told us had the best of intentions, about 50 percent of the time they were never used. In the case where they have not been used, you end up having to make sure that they are configured correctly, are patched, and cause general overhead. During a security audit, they have been rightly referred to as a security violation. From this, a policy change has been made that states that no SQL Server 2005 components will be installed until they are required. Once they are no longer required, they are to be completely removed. Keep this in mind as you install components, and try to install them only when you know they will be used.