Welcome to the Security and Auditing Week for SQL University. Last semester we looked at the basics of SQL Server security. This semester we're going a step further and taking a look at how best to audit your SQL Server environment. This is an important topic because if you've ever had to assume control of a SQL Server, you need to be able to determine very quickly whether there are security holes in your configuration. In addition, you'll need to be periodically auditing your environment to ensure unexpected changes are caught. While there's a lot of automation out there to enforce configuration standards, virtually all of them can be bypassed in some way. As a result, it's a good idea to do a periodic check.

And that brings us to today's lesson: the server itself. I'm not speaking of SQL Server, but the operating system. This is an area that I've seen some DBAs neglect, to their detriment. You need to take a look at the OS side, because a weakness there could lead someone right into your SQL Server. You own the box, you own the SQL Server. It's really that simple. So here are the areas I immediately check when I'm looking at a new SQL Server or reviewing an existing one.

Computer Management:

The main tool we'll use for this auditing is Computer Management. You can either get to it from Administrative Tools or by right-clicking anywhere you have the My Computer icon and clicking Manage, like in Figure 1:

Figure 1:

Auditing Privileged Groups:

Once inside Computer Management, expand Local Users and Groups and click on the Groups folder (Figure 2).

Figure 2:

There are three groups we're particularly interested in:

• Administrators
• Backup Operators
• Power Users

The Administrators group can do anything on the server. Therefore, knowing who is a member of it is key. If you see domain security groups in this group, get with your AD administrator and track down the membership of each group. Keep in mind that as of SQL Server 2005, there's a special switch that allows SQL Server to be restarted in single-user mode. In this mode, any member of the local Administrators group is automatically treated as a member of the sysadmin role within SQL Server. So even if you've gone the extra mile to remove BUILTIN\Administrators (or never added them) from permissions within SQL Server, this is effectively a back door. It's there by design, because it allows an organization to get back into a SQL Server where someone stripped out the security by mistake. But because it's a back door, we do have to watch it.

The Backup Operators can bypass security when it comes to accessing files for backup. Therefore, any members of this group can grab any backup files on disk. If SQL Server is stopped, they can also grab the database files. They have to be using the proper operation, but old tools like robocopy provide such functionality. This is why Transparent Data Encryption has been talked about as a useful item with respect to SQL Server 2008 Enterprise Edition. Even should a backup operator copy the right files off, TDE would prevent said person from reading the files in a useful manner.

Finally, there are the Power Users. Mark Russinovich once upon a time showed how Power Users could escalate to an account with Administrative rights, but more pressingly easy is the fact that Power Users control services. That means they can stop the SQL Server service. Should they do that, you are now down. So a member of the Power Users group can execute a Denial of Service (DoS) attack at any time. Therefore, it is important that you know who these folks are, too.

Checking the File Shares:

As a DBA, I always check the file shares. I never assume they are right. So by expanding Shared Folder and clicking on Shares, I can see all the shares available on a given system (Figure 3).

Figure 3:

I'll then go through each and every one and checking both the NTFS and share permissions. I want to make sure:

• All shares are hidden (have the $ appended at the end), if possible.
• That all shares have the minimum rights necessary for use. If I see Everyone here, we have an issue.
• That there aren't any shares except the admin shares (C$, E$, etc.) that give access to the root of a drive (C:\, E:\, etc.)

Also, I can see based on the last highlighted entry, that FILESTREAM is in use here. I definitely want to verify its permissions.

Checking Other Services:

It's important to know what else is running on your SQL Server. Another service may be a means of exploiting the server, gaining escalated rights, and then attacking the SQL Server. We can check this by expanding Services and Applications and then clicking on Services (Figure 4):

Figure 4:

In this example, I've highlighted IIS Admin, because that tells me IIS is installed. If we're talking a SQL Server system, the only commonly used SQL Server components that require IIS are SQL Server Reporting Services for SQL Server 2000 and 2005. SQL Server 2008 does not. Whether or not SSRS 2000/2005 is installed, I'm now going to investigate if there are any other IIS applications deployed. There have been some weaknesses in IIS in recent years, but this isn't the cause for concern. A poorly written and/or secured application is my main concern. I've seen servers taken over because improper permissions + a buggy web application allowed an attacker to do something that shouldn't have been allowed. It wasn't a weakness in IIS that was seized upon. It was a weakness in the application in conjunction with poor security practices that did. So I want to check this kind of thing out. And I won't just stop at IIS. I'll take a look at every additional service that I see installed that isn't absolutely required.

Write Up What You Find:

As you do your investigation, write up what you find as you find it. You'll want to make a checklist of exceptions to investigate further. There may be a good reason something is the way it is. But you will only know that by asking questions. If you do the write-up, then you add to it based on what you find in your follow-up, you're covered should an auditor come poking around. You've got your documentation in order and you're good. Also, if you see something that gives you pause, you now have the documentation to start trying to work with the right people to correct the issue or document the risk if the powers that be don't want to spend the time to correct it or assess that it costs too much to do so. Keep your documentation and refer back to it each time you perform your audit. It'll save you some work, especially if there is something you flagged in a previous audit that you investigated and couldn't correct. You might have forgotten that and your documentation will remind you so you don't go and investigate the issue all over again.