Thanks for hanging in there for Day 4 of Security Week here at SQL University. Today we're going to look at the most frustrating scenario that we often come across, and that's where a development team has built an application, but there are few if any options for us on the security side. If you're not familiar with the discussion, look back at Day 1 where I covered the basics of what to plan for with respect to a security architecture.

Today's mantra is going to be "Document, document, and document." Usually when the DBAs are brought in and there are no options, it's because the DBAs are brought in late on the development cycle. There are several usual suspects as to why this happens:

• The development team thinks they understand SQL Server security properly and comprehensively. This may be true. In my experience it usually is not.
• Coding began before the project was approved and since there was no project, they really couldn't go ask for resources on other teams.
• The development team has never had to consider security very greatly and it doesn't occur to them this is an issue. This still happens.
• The development team has been backed into a date to complete the coding and went immediately to work. This still happens to, and far too frequently.

In two of those cases (#1 and #3), you're dealing with ignorance on the part of the development team. In one case you're dealing with a potential ethical issue because somebody is circumventing the rules and procedures that are in place. If the procedure doesn't work such that people ignore it, it's not a real procedure. If you want the auditor speak on this one, we would either call it an ineffective control or be flat-out blunt and say it's "not a control." And in case #4 you are dealing with another ignorance case, but this time on the part of the requestor. So #1, #3, and #4 deal with ignorance and the best way to overcome ignorance is with education. #2 is harder, but as a DBA or database professional, you still have a vested responsibility in protecting the organization. That again means education.

Why We Document:

We document to clearly state the risk to the organization for what we see as security lapses. If possible, we need to provide concrete examples with each risk statement we make and an estimate as to likelihood and cost. Also, if we can, we need to propose the solution that mitigates or eliminates the risk and how much that is estimated to cost. It's not just dollars (or your choice of currency) here that we're interested in, we're also talking about estimated time to fix. Since your primary purpose here isn't to be right (you certainly should be, but personal vindication isn't the goal), but rather to inform the organization so it can make proper decisions, facts and numbers will speak more clearly than an impassioned speech. Also, should the organization not choose to correct an issue, and that becomes a problem later on, you've got the documentation that shows you pointed out the issue, so you're off the hook. But it also contains the recommended fix in case you have to act immediately to remediate, and that's extremely valuable, too. So now that we've got that out of the way, let's look at the three As.

Authentication:

If it's an internal application running on the local network, likely the answer here is, "Windows authentication." Now, let's go a step further and ask, "Under whose context?" If you need to know exactly who is accessing the data (part of the audit requirement) or you're determining what data to return based on the user, a good question here is whether or not the app was built to handle this in a multiplexing mode using a service account (many users hit the app, but the app uses a singular account to connect to SQL Server and knows how to filter the data properly). If the app isn't, and it's relying on some mechanism within SQL Server to do the filtering (permissions to determine objects, homegrown row-level security to determine data returned), then you're looking at using Windows groups which are tied to the users coming in. I say groups here because that's important if you're shop has any size to it. Managing individual accounts is fraught with peril and eventually leads to a situation where you have accounts in SQL Server that no longer exist in Active Directory if there's any kind of turnover in your organization.

And that leads to a discussion of individual users versus groups. In small organizations, managing by individual users may be okay, because there's just not enough people to deal with the headache of creating and managing groups. Of course, organizations this size don't tend to run SQL Server. However, after about 10 users, it typically starts becoming cleaner and easier to use security groups, if done right. Why security groups?

• Once you determine the permissions to give a group, should someone else need similar permissions, you put them in the group. If at a later time they don't need the permissions, you take them out of the group. This is speaking organization wide.
• On the file server and in SQL Server, you aren't having to add people in every time they join the organization and you aren't having to delete them every time they leave the organization (technically on the file system you don't have to, but they you might see the "ghost" SIDs).
• If a user needs access based on multiple reasons, and those reasons are defined by the groups, then SQL Server is going to aggregate the permissions, meaning you don't have to piece it together. Likewise, if this is the situation and they suddenly lose one of those reasons, you're not having to pick the permissions apart.
• It allows Active Directory to be the central security system, meaning it's cleaner, easier to audit, and less likely for there be a security issue because of human error (though, yes, once an error is made, it could be bigger because it's in the single system).

Another thing to consider is if you have multiple physical tiers (virtual machines are considered "physical" for this discussion) and the users' credentials have to be passed through via impersonation at multiple levels, you may also to ensure Kerberos authentication and delegation is working properly. Most of the work isn't done by DBAs. But a DBA should know what's expected on the SQL Server side.

When do we use SQL Server based logins? There are a few times when it's most appropriate:

• The server is untrusted, such as an edge server not on the domain.
• When the system is monitoring domain availability, meaning if the domain drops, it needs to record that. Obviously, a Windows based account in Active Directory fails here.
• When you're connecting from non-Windows systems which do not have the ability to generate the security token and pass it to SQL Server as part of authenticating.

Otherwise, Windows authentication is what you strive for.

Authorization:

A lot of developers love to run as the database owner. Why? Because then pesky security doesn't get in their way. That's a problem if you're trying to run a tight ship (and hopefully you are). If there's not much time left and you don't know what the application really needs, how do you get enough information to actually compile risk? First, realize that you may not. But you can certainly document what risk dbo or a db_owner account is, and certain what risk being a member of the sysadmin fixed server role is, especially if the database is on a shared SQL Server.

If you have a friend on the development team, especially one who cares about his or her craft, this is usually a good person to talk to about the application and what it does. Compile that with some tracing of activity and you get an idea of what it will require. Compare that to what has been asked for and you've got your talking points to give to your boss. Keep an open mind here. There may be things done that you don't necessarily understand. If you don't, try to ask nicely to understand why it was done that way. There may be a legitimate reason. The DBA isn't always right (I know, it's rare that this happens, and it may never have happened to you, but it's always a possibility). What we're striving for is the principle of least privilege: permission to do what is required - nothing more, nothing less. Database roles which tend to get requested which you should avoid, if possible:

• db_datareader - implicit permissions, means an extra couple of steps in auditing and compliance reporting
• db_datawriter - see db_datareader. For these two, manually create a role and explicitly grant the permissions needed.
• db_ddladmin - why does something need the ability to change code?
• db_owner - can do anything in the database... this is almost always NOT needed
• db_securityadmin - can change security in the database.

I've not included server roles because it's very, very rare that I've seen a home-grown application require any server role. If one is asked for, you need to investigate why - immediately.

Another thing is to use searches to scan for obvious warning signs:

• use of xp_cmdshell
• use of linked servers
• use of OPENROWSET
• use of sp_OA* stored procedures
• use of other extended stored procedures
• the dreaded word "CURSOR"

Sometimes the use of tools in this list is legitimate. However, verify that it is worth fighting over. Case in point: recently a developer came up with a process to do a data conversion. He wrote it entirely in T-SQL. It uses several cursors to do the job. Yes, I'm sure he can do it another way. But we're talking about a conversion that will be run once in production and on the slower, older development machines, it runs well within the window time allotted. And it runs isolated from other processes (actually before any other process will be started up), so it's not going to cause a performance issue with other parts of the application or with other applications. I could kick it back, but what would be the point? You can do this against with queries against syscomments in SQL Server (works with 2005/2008 still) or, if you have access to the source code repository, let its search work for you.

Auditing:

The options we discussed in Day 2 are all on the table. What's more, since the database is being design by your organization, if you have to put something in the database to help with auditing in order to meet requirements, it's doubtful the development team can say no and get away with it. Though a typical preferred approach would be to have the auditing built in at all the layers, as required (meaning in the app code, in the stored procedures, etc.), if you're coming in late, chances are you aren't getting that. In this case, it's better to treat the application as a 3rd party originated one with respect to how you approach implementing the proper auditing. Yes, it means more work on your part, but that may be far less than what it would take to recode the application throughout. If the application is using a service account and you need to know who is coming in, one option is for the application to use CONTEXT_INFO (introduced in SQL Server 2000) to pass in the information, however, the application's database code still needs to know what to do with it. If you're building triggers, you can use it, provided they populate it when they make calls that change the data. I'm not a big fan of this technique, as I'd prefer to pass the user information in intentionally as a parameter on a stored procedure, but I've seen solutions where it works. This is obviously something you won't be able to make happen on a 3rd party application on your own.