Printed 2017/08/23 10:25AM

SQL University: Architecting SQL Server Security, the Big Picture


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:

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:

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

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:

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.