http://www.sqlservercentral.com/blogs/brian_kelley/2010/03/19/when-it-quot-has-quot-to-use-sa/

Printed 2014/11/22 10:40PM

When It "Has" to Use SA...

2010/03/19

As an infrastructure and security architect, I cringe any time any one mentions the following:

Those accounts can do anything and usually if an application is written to run only with one of those types of accounts, it means the people doing the coding were lazy. I'm not sorry if that offends anyone because that's the truth. Now I know there are exceptions to the rule. For instance, a system like Quest's Active Roles Server or Symantec's Control Compliance Suite need this level of privileges in order to maximize the use of the product and restrict the security of those who use them day-to-day. Basically, the service account has the elevated rights so the user never has to. But as I said, these are clearly exceptions to the rule.

As a DBA, I hate hearing an application requires sa or a sysadmin level login. In many cases the application doesn't. It's just that the vendor hasn't tested with anything less because it was easier to run with all the rights. Believe it or not, I've seen this with security specific applications, too. And in a lot of cases you don't have time or the leverage to get them to change. So you have to accept running with those rights. So what can you do? Here's what I've tried to do in the past:

Isolate the SQL Server:

This is first up. Since this application is going to access as sa or a sysadmin level account, I want this to be the only application on the server. I know this can get pricey from a licensing perspective, and that's always a consideration. If possible, I've considered the free SQL Server Express. But basically if I can minimize what else can run on this system, I will.

Lock Down Network Access:

Since there's but one application that will accessing the SQL Server, I try to lock down access as much as possible. Usually this means installing the SQL Server locally on one of the application servers. If there is only one server, then I can turn off access to the SQL Server from the network altogether. I've had to do this before and I highly recommend it, if you can get away with it. It will, however, mean that you cannot administer the SQL Server remotely unless you use RDP or some similar technology to get onto the desktop of the server.

If it's not installed locally, I will use an IPSEC policy to restrict the IPs which can talk to the SQL Server. Yes, there's a minor bit of overhead. But if there's no reason for Joe Schmoe in accounting to access the SQL Server, then blocked he is. This is actually fairly easy to do and in cases where you can do it on shared SQL Servers it's not a bad idea.

Different Service Accounts:

Some folks use different service accounts for each server which their SQL Servers are installed. I like this practice a lot. However, if you're the type of organization to use a shared service account, don't do so in this case. Instead, instead on a different service account with a different password. We're trying to isolate this SQL Server, so we don't want someone hopping from it to another under the context of that service account. By ensuring we use a different one from the rest of the environment and ensuring on no other SQL Server does that account have access, we've boxed in an attacker who manages to get into the SQL Server.

Monitor More Closely with IDS/IPS:

If possible, watch the line more closely between the app servers and the SQL Server. The obvious xp_cmdshells and the like should be flagged, unless that's how the application works. And if it does, that's yet another lazy practice, but that's another blog post. Basically, signs that this particular SQL Server may be getting attacked should fire at a higher escalation because we know the application is accessing SQL Server with a higher privileged account than normal, meaning it has the potential to do more damage.

Better Control Administrative Access to the Application Server(s):

If someone has administrative access to an application server, they could piggy-back over to the SQL Server. Obviously, we want to limit who does this. So while administrative users should be under great scrutiny any way, when the application is running under this level of privileges, even more so.

Audit for Local Logins on the Application Server(s):

If you've followed Microsoft best practices, you've got a lot of audit events showing up in your Security event logs. Some of those we're more concerned with than others. Those are the local login events, which means someone is coming in, either by the console or remote desktop. These are the most likely scenarios where the application is going to be hijacked. There are a lot of events, and some have changed with Vista/Windows 7/Windows Server 2008, but this article should get you started. By the way, you should already be doing this on all your SQL Servers.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.