Securing SQL Server

Protecting Your Database from Attackers

Denny Cherry

• Publisher: Syngress
• Year: 2015
• ISBN:
  9780128013755
• Edition: 3
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

SQL server is the most widely-used database platform in the world, and a large percentage of these databases are not properly secured, exposing sensitive customer and business data to attack.

In Securing SQL Server, Third Edition, you will learn about the potential attack vectors that can be used to break into SQL server databases as well as how to protect databases from these attacks. In this book, Denny Cherry -a Microsoft SQL MVP and one of the biggest names in SQL server - will teach you how to properly secure an SQL server database from internal and external threats using best practices as well as specific tricks that the author employs in his role as a consultant for some of the largest SQL server deployments in the world.

Fully updated to cover the latest technology in SQL Server 2014, this new edition walks you through how to secure new features of the 2014 release. New topics in the book include vLANs, setting up RRAS, anti-virus installs, key management, moving from plaintext to encrypted values in an existing application, securing Analysis Services Objects, Managed Service Accounts, OS rights needed by the DBA, SQL Agent Security, Table Permissions, Views, Stored Procedures, Functions, Service Broker Objects, and much more.

• Presents hands-on techniques for protecting your SQL Server database from intrusion and attack
• Provides the most in-depth coverage of all aspects of SQL Server database security, including a wealth of new material on Microsoft SQL Server 2014.
• Explains how to set up your database securely, how to determine when someone tries to break in, what the intruder has accessed or damaged, and how to respond and mitigate damage if an intrusion occurs.

Chapter 1

Identifying Security Requirements

Abstract

This chapter talks about how to figure how what security objectives need to be identified.

Keywords

security

objectives

Information in this chapter

• What are security objectives?

• When should security objectives been identified?

• How to identify security objectives?

What are Security Objectives?

There are two kinds of security objectives that need to be dealt with. The first is the easiest, identify the data which must be protected and why it must be protected. This includes data such as personally identifiable information (PII) such as names, email addresses, usernames, passwords, date of birth, etc. The second is a little more complex as it requires identifying any potential application design problems.

Plainly stated, we need to define the security objectives and locate the security holes within an application.

Personally Identifiable Information

As your application data design process begins the personally identifiable information should be identified, a decision made as to which data should be encrypted (which should be all of it), and plans made to encrypt that data to ensure that the data is protected. This information is generally defined as anything which can be used to identify a specific person. Often times it is assumed that you want to encrypt the users’ password and that is it. In the modern times of data security we need to take a very close look at the data which our customers trust us with. This means that we need to look at encrypting data such as the following.

• Username

• Password

• Customers Name

• Customers Address

• Social Security Number

• Tax ID Number

• Email Address

• Spouse’s Name

• Employer

• Spouse’s Employer

• Social Media Accounts

• Bank Account Information

• Telephone Numbers

• Item Purchase History

• Password Reset Answers

Note

Item Purchase History?

We want to encrypt the item purchase history (you can read more about data encryption in Chapter 4) to ensure that any items which might lead to personally identifiable information disclosure would be encrypted. We also want to include any special notes or customizations which can be made via special order to ensure that anything which makes the information personally identifiable is protected.

A great example of this would be if your website sold among other items blood sugar test kits. Selling someone blood sugar test kids tells you that the customer has diabetes which could means that were that the company could be responsible under HIPPA for patient privacy..?

When working with any sort of medical data there is a whole bunch of additional data which needs to be looked at. This includes data such as:

• Doctor Patient Relationship

• Insurance Carrier

• Diagnosis

• Test Results

• Tests Ordered

• Digital X-Rays, MRIs, Cat Scans

Note

Why Tests Ordered?

Lab tests results makes perfect sense to encrypt as all medical information is private, but why may be asking yourself why you would need to encrypt information about any tests which were ordered. The answer is that if you know what tests were ordered for a person that may tell you what medical condition that person might have. As an example if you see a lab test for a “T Cell Count” that tells you that the patient is an HIV positive patient which is the exact kind of information which must be kept private.

When Should Security Objectives been Identified?

Proper database and application security begins at the beginning of the development process by first identifying what kinds of data will be stored, and how people will access that data. From then we can identify the risks associated with storing that kinds of information and those kinds of access patterns. Once the risks are identified technical solutions to mitigate those risks can be designed and implemented into the application design.

The most general security objective that we can start with is that “all data should remain secure.” While this is a very broad statement, and an excellent goal, it does not provide us with any insight to the application’s purpose, the data which we will be processing and storing, and it does not give us any guidance to a technological solution to ensure that the objective is met. For many application development cycles this is about as much thought as it put into security objectives. This is because for many development teams they need to focus on the core business objectives of the application, which is almost never data security. At best the developer is left to self-identify the fields which should be protected through data security, as well as to identify the method by which the data should be encrypted. Often times the security aspects of the application design, beyond items like the ability to log into the application, is left to “the next phase” of the project. But by the time the next phase comes around there are business critical processes which need to be worked on instead of dealing with the security holes which were left over from the prior phases of the project.

The big reason for this approach is that the business drives the development process either directly through project plans or indirectly by paying for development time. Due to this the business will only want to pay for development time for items which they see adding value to the customers or the users of the application. As security is typically something which cannot be seen by the customer or the end user the business typically sees no need to focus on security while preventing work on other features which they see as being more important to be worked on.

As time passes the business gets another reason to push back on identifying and implementing security objectives into the application development process. “If it has been broken this long, why should be focus on fixing it now?” The problem with this kind of thinking when it comes to security is that by the time the lack of security has become an issue it is too late. The attacker has already broken into the system and exported the data which they want to export. Patching the holes in the system at this point while good is useless for doing anything about the attack.

When systems are smaller with less uses that is the ideal time to fix security problems within the application. Often security features are hard to implement and often require huge amounts of data change. When applications are smaller with less data in the system this becomes the ideal time to fix these problems has the data changes which need to happen are very small compared to after the application has been in use for months or years.

How to Identify Security Objectives?

When using more legacy software design methodologies (basically anything besides SCRUM and Code First) you need to examine your data sets and your tables and identify anything which could be used to identify a single customer, and not just from a login perspective.

Note

Changing the Thought Process

When most systems are designed the only items which are really worried about when it comes to data encryption is the username and password. This is because we are concerned with someone exporting the usernames and passwords then logging into the customer’s account and using the account.

However, we need to understand that the data which is housed within the system could at some point be exported in its entirety and that information could then be used to attack other websites or to attack the customer’s financial information in other ways. While we as the software developer have no personally responsibility to ensure that breached data is not used to further other attacks that does not mean that we should not do our best to ensure that the data is not able to be used to further other attacks.

There have been many data breaches in recent years where massive amounts of information have been compromised and released. Sometimes, such as in the case of the cellphone and username release from SNAPCHAT the information which was breached could be used to make breaking into other websites easier. The news of SNAPCHAT having the data breach problem was bad enough, but imagine how bad the news articles would be for SNAPCHAT if it was found out that the information which was from their breached system was used to make an attack on another website much easier than it should have been.

Now the data breach from SNAPCHAT’s systems was not due to an actual systems breach, but instead from someone abusing their APIs (which we will talk about later in this chapter), but the end result is the same to the end user; loss of trust and respect for the company.

Once you have gone and identified the fields which can be used to identify a single customer the same information should be reviewed by the company’s legal department to see if there are any other fields which they feel should be encrypted. When working with the legal department it is best to give them not just the names of the columns, as those may or may not mean anything to them but give them the description of the field as well as several rows of realistic sample data so that they can evaluate the actual data to see based on the data if it needs to be protected.

Note

Why Should We Talk to The Lawyers?

While the lawyers are not going to be doing any of the coding of the application we still need to bring them into this process. The biggest reason that we need to talk to them is that as the developer or database administrator you do not want to be the one who is interrupting the various federal, state and local data privacy laws which apply to your company and the systems that is builds and/or buys. That task is precisely why your company has lawyers. Let them research all the various laws which are in effect, and which are coming into effect s...