Welcome to this semester's security week at SQL University. In previous semesters we've covered the technical aspects of SQL Server security but at some point you've got to put it all together. So this week we're going to talk about putting together an architecture that meets the needs of business and keeps your job safe (at least from the security side). Let's start with the basics of what we're trying to cover, usually referred to as the three As:

• Authentication - Who are you?
• Authorization - What are you allowed to do?
• Auditing - What do I need to track of your activities?

Let's talk about each one, starting with Authentication.

SQL Server's Authentication Options:

For applications connecting in, two types of authentication are supported: SQL Server-based authentication and Windows-based authentication. Generally speaking, Windows-based authentication is preferred. And this always raises the question, "Why?" Here's why:

• SQL Server-based authentication stores a password hash in the master database. In earlier versions (SQL Server 2000 and below) this can be (and was) exploited using brute force attacks, especially since there was a hash weakness. There were actually two hashes... one from the actual password and one from the all uppercase version of the password. Well, that was a problem because the all uppercase version effectively removes 26+ characters from the options and makes it easier to find a match. Once you get the all uppercase, it's a trivial exercise to try all upper- and lowercase combinations until you get the right password.
• In earlier versions (SQL Server 2000 and below) the way the password was transmitted across the wire was trivially "encrypted" (a lot of security folks don't consider using an XOR operation encryption, which is effectively the mechanism SQL Server used after the high and low bits were flipped) and was thus easily deciphered if it could be intercepted.
• SQL Server-based authentication means you have to create an account on the SQL Server. If a given user needs access to more than one server, now you're creating and managing multiple accounts per user.
• SQL Server-based authentication means account management is not centralized. So if I have a user I suddenly need to disable, now I have to go into Active Directory *and* every SQL Server where an account was created for that user.

So when would we look to use SQL Server authentication over Windows authentication?

• When a trusted connection cannot be made. For instance, if you have a server which cannot or should not be in a trusted domain, such as a web server on the edge.
• When one of the things you're monitoring is domain availability and you need to record when that drops. Obviously, if you're using Windows authentication and the domain drops, you're not connecting via Windows authentication.
• When you're left with no other choice because of how an application is built.

We'll look at the details for authentication more with 4 different scenarios we'll consider the rest of this week. Now on to authorization.

I Know Who You Are, but What Can You Do?

Identifying the connection coming in is only part of the overall security solution for an application or system. We also need to determine what rights/permissions said account should have. There are two basic principles we'll apply whenever we plan the authorization model. The first is the Principle of Least Privilege. The easiest way to understand this is the permissions an account needs to do the job - no more and no less. I say no less because of the second basic principle we need to follow, and that's the information security C-I-A triad, which stands for: Confidentiality, Integrity, and Availability. Security folks tend to concentrate on Confidentiality (only the properly authorized accounts/processes can see the data) and Integrity (only the properly authorized accounts/processes can change the data), but Availability is critical. If the system isn't available (or if the account can do its job because it doesn't have enough permissions), then the system is broke. Yes, this sometimes means we have to open things up more than we'd like, and some of this will go into the discussion we'll have later in the week, but remember that we have to provide enough rights to do the job. That's non-negotiable.

I've been careful to say account and not user. In a lot of applications, the end user connects to the application and the application uses a single security account to make the connection to SQL Server. We see a multiplexing situation here, and we need to make sure that the account being used has the permission that every user needs to do, meaning we are relying on the application to provide the appropriate levels of security. This is part of the discussion that has to happen between the DBAs and developers or DBAs and system integrators (in the case of a 3rd party application).

And you can probably guess by what I just said that authorization is where we get into the nitty gritty of the security model. This is where we consider things like ownership chaining, securables, database roles, and the like. In some cases you have options on the authorization, especially when it's a home grown application. This isn't always the case, even with home grown applications. Sometimes you come into a project late and the developers have already done things that are either too costly to undo or are not possible to undo and still make the project delivery date. We'll look at authorization more as well when we consider each scenario.

You're In, You're Mucking with Stuff, and I Need to Track You:

The A of the three As which is the most often forgotten is Auditing. Auditing is usually an after thought. SQL Server provides scenarios for auditing behavior, depending on version and edition. In some cases none of these solutions are acceptable and we end up going to a third party solution to provide a fix. The key here is to understand from the business what it wants you to track and then determine whether that's doable within SQL Server or with a purchasable solution.

One thing I will say here is too much auditing is as much a problem as too little auditing. Case in point: if you've ever looked at a Windows security event log, especially in Windows Server 2008, you may be overwhelmed with the sheer number of events that are there. Speaking from my previous experience as a directory services administrator and security architect, tracking failed logins on a domain controller is not a pleasant experience without proper tools to extract and filter out the events I needed. Even with the proper tools it can still prove migraine-inducing. So one of the things you need to try to negotiate it here is a reasonable level of auditing to meet the true business need. A nice to have that bloats your auditing logs (whether they be in a file or a table) actually impairs your security because it means folks are less likely to look at those logs on a regular basis and when they do have to look at those logs, they can miss the important details due to the amount of "noise." Sometimes this is hard to get across to some well-meaning security types. But still, you must try.

The Scenarios:

So we've convered the high level of the three As, and we'll discuss them in more detail as we look at the following scenarios:

• Implementing a 3rd party solution with little or no flexibility
• Implementing a 3rd party solution with some options, especially around authentication
• Building a home grown solution when you've been brought in late in the development cycle
• Building a home grown solution when you've been brought in at the very beginning

We'll discuss what we typically see with each case and what we can do to implement the three As in the best way possible. Obviously the last scenario is the ideal, because all of the options are available, which is why we'll save it for last.