Welcome back to day 2 of Security Week for this semester of SQL University. If you missed yesterday's introduction, you'll probably want to review it first.

The first scenario we'll look at is probably the easiest to consider, because our options tend to be rather limited. This is where as a DBA (involuntary or not), you're having to support a 3rd party application with no options on how to configure the security. There are several scenarios that could bring you to this point:

• The third party application is only written to work one way. Anything else breaks it.
• The third party application *may* support other security options other than what the vendor has specified, but you're stuck because the vendor won't support any other way.
• The way your organization is going to implement the third party organization prevents you from making any changes to the security architecture.

Let's first talk about how to handle each situation to try and create some leverage, and then what to do to meet the three As (Authentication, Authorization, and Auditing) the best you can when you have none.

It Only Works One Way:

If it only works one way, you've got no immediate leverage unless it's a small vendor and you're a big customer (relative to the vendor). If that's the case, you need to get with the folks on your side who are dealing with the vendor. Explain any issues you've found and the risk to your organization if those issues are not resolved. Let me give you an example where this works.

Once upon a time we were dealing with a vendor who had a nice web application for our end users. The interface was to their liking. But we red flagged the application when they asked us for the username and password by which the application would connect to the back-end SQL Server. The application was going to use SQL Server authentication, and this was expected, because it sat on the edge (Internet facing). But we balked when they asked for the username and password. It was a Microsoft.NET web application and there should have been no reason for them to need to know the login information. We should have been able to specify that in web.config (well, technically it's stored in the registry in encrypted format but how to get to that info is stored in web.config in encrypted format and this is seamless to the application). It turns out that they were going to hard code the username and password into the complied code-behind code. Um, no. This took a little bit of explaining to our end users, but once they understood how insecure this was, they went back to the vendor and said, "No deal. Fix it or you don't get our business." Because the vendor was small and we were "large," they fixed it.

But what if the vendor won't budge and the powers that be want to go forward anyway? Again, another example. This one involved a large security company and we realized that their product required the use of sa. Did I mention that it was an internal network-based application, meaning Windows authentication should have been the preferred mechanism? Right. Well, we needed the application. We weren't going to get a change any time soon. So we did the only thing we could with respect to the application: we opened a trouble ticket requesting a change for a future version. Not exactly what we wanted, but it's all we could do, with hopes it would be fixed in the future. It eventually was. There are no guarantees on this, but you do what you can do with respect to the vendor. Well, what about the organization? That's a good question.

The answer is you set it up the only way the application is going to work. Obvious, right? Well, a lot of DBAs go kicking and screaming at having to accept this path, and I'll admit that as a security architect I've done my fair share of flailing around, too. It's natural if you care about what you do. But the reality is that if the organization is going to implement the application and there are no options on the security configuration, you've got three choices:

• Implement the application, but document and share the risk to the organization (preferably with with risk mitigation strategies included).
• Implement the application and say nothing.
• Find another job.

Needless to say, the first and the third are the only eithical choices in that list. I said document and share the risk, I didn't say beat people over the head with what you see. Our job is to communicate the risk and any strategies to mitigate that we see. We need to try and get our boss to understand what we're seeing and then let him or her go fight the battle, if it needs to be fought. Knowing there is an issue and saying nothing isn't ethical. And if a security incident comes about later and it is determined that we knew and didn't say anything, then we could be involuntarily released from our jobs. The last choice is when you ethically can't support what's going in. It's always an option to consider. It may not be an option to implement in your mind, but remember, you always do have a choice (well, unless you work for the military, then that's debatable).

You Can Make It More Secure, but the Vendor Won't Support It:

This one is a hard one because it's probably the most frustrating. The first one is easier to swallow because that new cliche, "It is what it is," fits. In the case you've figured out how to lock things down more than the vendor's configuration, but they won't support it. What can you do? One thing to do is to try and go through support (or better yet, your sales engineer if there was one), and try to get someone who can understand what you're talking about. I know, don't hold your breath. But there are times when this does work. I've had limited success with this approach. So what if you don't have that success? What then?

• You go with vendor's supported configuration, and this basically puts you into the first scenario.
• You use your configuration, understanding you'll be out of support.

If you choose the second option, make sure your organization is on board. Make sure everyone understands the risks in doing so (also make sure you have this in writing from someone who has the authority to approve the out-of-support configuration). And make sure you have that script that reverses all the changes to put things into a supported configuration. By the way, this script isn't used to fool the vendor into supporting you unknowingly. This script is to check the system in the vendor's supported configuration. It may be that even if you put things back the way the vendor has said it must be that the vendor still won't support it. That's why it is so important to understand the exact details of a vendor's support policy. But any time you need to do an upgrade or a patch, always assume that said upgrade or patch is only going to work in the vendor's supported configuration. Also, if something is not working, the vendor might have come across this and that's why the supported configuration is what it is. By putting the configuration back, you'll see this.

It's Not the Vendor. It's You All:

Or as we say in the South, y'all. There are times when the way the organization chooses to do it a way that the DBAs don't agree with, although there are options. Really, the only choice here is to document the risks of both approaches (the way the organization wants to do it and the way the DBAs would prefer to do it), the mitigants, and let the organization decide. It may be there are business drivers or other reasons why the organization is going to insist on the way they are headed. If that's the case, you've already documented the risk. That leaves you with two options: implement the application or find another job. As a security person, there have been times where I advocated a more stringent security solution than what was chosen. It never got to the point where I felt the organization was baring itself to great risk, but as a security person I realize that security, like everything else, is a business decision. Our job is to inform the business decision makers, ensure they understand the options, and then leave the choice to them. Naturally there will be times when we disagree with the choices made. That's reality. And when we disagree, we're left with those two options. If it's something you can't live with, it's time to find another job.

The Choices Have Been Made, So What About the Three As?

Authentication and authorization have already been decided if you've gotten to the point where there's only one real supported configuration. Now, obviously, if you can use Windows authentication, you tend to do so. If they let you use accounts or roles with lesser privileges, you do so. Case in point: System Center Operations Manager has a low-security model for checking in and reporting on SQL Server health. Needless to say, when presented with this option, go with it. But chances are you don't have options in these two As. Here you grin and bear it.

What you do have some option on is Auditing. Some vendors will allow you to make limited modifications to their database structures, so long as they can be rolled back. If that's the case, using additional tables and triggers, you can audit for data changes on key tables. If that's not the case, you still have some options.

First and foremost there is always the option of server side traces, even if we're talking SQL Server 2000. The key here is to trim down to only capture what you need with the right filters. Profiler can be used to build this trace properly. This, however, is probably the option of last resort.

A second option is a third-party product that acts as a shim to SQL Server. These products can even capture activity locally, and as a DBA this is a good thing because it also provides an evidence trail that shows you didn't do something that you could be accused of. Ever hear this? "The data is wrong. No, we couldn't have miskeyed it! You must have changed it." With these solutions, you don't have to go and find a log explorer solution to prove your innocence. These auditing applications are typically called data loss prevention solutions and their functionality and price vary.

Finally, SQL Server 2008 adds options that are not available in the previous versions. You have the option of using Extended Events to do auditing in a better way than with traces and if you have Enterprise Edition, the Audit object is also available.Which you use will depend on your needs and the best thing to do is experiment with them in a safe environment so you understand the options they provide as well as they limitations they have. Understanding what you have to work with is key to building the best architecture possible, even when your options are limited.