How to Cheat at Securing SQL Server 2005 (eBook)

Mark Horninger (Autor)

eBook Download: EPUB

2011 | 1. Auflage
412 Seiten
Elsevier Science (Verlag)
978-0-08-055554-6 (ISBN)

Infos

The perfect book for multi-tasked IT managers responsible for securing the latest version of SQL Server 2005.

SQL Server is the perfect product for the How to Cheat series. It is an ambitious product that, for the average SysAdmin, will present a difficult migration path from earlier versions and a vexing number of new features. How to Cheat promises help in order to get SQL Server secured as quickly and safely as possible.

* Provides the multi-tasked Sys Admin with the essential information needed to perform the daily tasks
* Covers SQL Server 2005, which is a massive product with significant challenges for IT managers
* Emphasizes best-practice security measures

Inhalt

Front Cover 1
How to Cheat at Securing SQL Server 2005 4
Copyright Page 5
Contents 10
Chapter 1. Introduction to SQL Server Security 20
Introduction 21
Security: Why Worry About It? 21
Installing SQL Server 23
Building Security into Your Application 32
Managed Code 33
Summary 35
Solutions Fast Track 35
Frequently Asked Questions 37
Chapter 2. Surface Area Reduction 40
Introduction 41
SQL Server Surface Area 41
Summary 60
Solutions Fast Track 60
Frequently Asked Questions 61
Chapter 3. Roles 64
Introduction 65
Roles 65
Situational Examples 76
Summary 78
Solutions Fast Track 78
Frequently Asked Questions 80
Chapter 4. Authentication and Granular Access 82
Introduction 83
Understanding the SQL Server Authentication Modes 83
Endpoint Security 88
Configuring Kerberos Support for your SQL Server 98
Auditing Authentication Attempts 104
Understanding Granular Access 105
Summary 116
Solutions Fast Track 116
Frequently Asked Questions 118
Chapter 5. Schemas 120
Introduction 121
Understanding Schemas 121
Changes Due to the User-Schema Separation 131
Designing Schemas 136
Managing Schemas 138
Summary 162
Solutions Fast Track 162
Frequently Asked Questions 164
Chapter 6. Password Policies 168
Introduction 169
Password Policies in SQL Server 2005 169
SQL Server Scenarios 181
Summary 191
Solutions Fast Track 191
Frequently Asked Questions 192
Chapter 7. DDL Triggers 194
Introduction 195
DDL Triggers Explained 195
Implementing DDL Triggers 201
Managing DDL Triggers 210
Scenarios for Deploying DDL Triggers 214
Summary 223
Solutions Fast Track 223
Frequently Asked Questions 225
Chapter 8. Data Encryption 228
Introduction 229
Data Encryption Explained 229
Summary 270
Solutions Fast Track 270
Frequently Asked Questions 271
Chapter 9. Reporting Services, Analysis Services, and Integration Services 272
Introduction 273
General SQL Server Best Security Practices 273
Securing Reporting Services 274
Securing Analysis Services 298
Securing Integration Services 302
Summary 308
Solutions Fast Track 308
Frequently Asked Questions 310
Appendix A: Group Policies 312
Group Policies Overview 313
Software Installation and Maintenance 333
Summary 345
Solutions Fast Track 345
Frequently Asked Questions 346
Appendix B: Securing Active Directory 348
Introduction 349
Designing an Access Control Strategy for Directory Services 350
Designing the Appropriate Group Strategy for Accessing Resources 388
Summary 395
Solutions Fast Track 398
Frequently Asked Questions 399
Index 402

Textauszug

Chapter 1

Introduction to SQL Server Security

Solutions in this chapter:

■ Security: Why Worry About It?

■ Installing SQL Server

■ Building Security into Your Application

■ Managed Code

☑ Summary

☑ Solutions Fast Track

☑ Frequently Asked Questions

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.

Best Practices According to Microsoft

■ Install only those components that you will use immediately. Microsoft recommends that you create a list of components that you will be using, and only enable those. If the need arises, you can install the additional components at that time. The components in a SQL Server installation are the Database Engine, Analysis Services Engine, Reporting Services, Integration Services, Notification Services, and Documents...

Produkt-Details

• Erscheint lt. Verlag: 18.4.2011
• Sprache: englisch
• Themenwelt: Sachbuch/Ratgeber
• Themenwelt: Mathematik / Informatik ► Informatik ► Betriebssysteme / Server
• Themenwelt: Informatik ► Datenbanken ► SQL Server
• Themenwelt: Informatik ► Netzwerke ► Sicherheit / Firewall
• Themenwelt: Mathematik / Informatik ► Informatik ► Theorie / Studium
• ISBN-10: 0-08-055554-3 / 0080555543
• ISBN-13: 978-0-08-055554-6 / 9780080555546