This was actually spurred by a post from Ted Krueger (@onpnt), which led to a short, but hearty, discussion on Twitter. He was discussing removing access from a local server admin to the SQL Server. My discussion was in regards to the fact that this isn't a successful preventative control. A system administrator who knew what he or she was doing can bypass the lack of access and still get access to SQL Server. I've gone into how previously, so I won't rekindle that debate here. But that does raise another question, and another controversy.
Should DBAs have local administrative rights on their SQL Server? DBAs would argue that it makes their jobs easier. Indeed, it does. But server admins and security folks would argue that it makes their jobs harder. I've been on both sides. I'm back as a senior DBA now, but I spent the previous seven years as an infrastructure and security architect. I've seen some crazy stuff done by folks who have admin rights and who don't understand the implications of what they are doing. Notice I was quick not to limit it to DBAs. Case in point, a particular IT pro (not a system administrator type) created a share on a production system where Everyone had read permissions through the share and at the NTFS level Authenticated Users had read rights as well. Meaning anyone on the domain could access that share. And that share periodically contained sensitive information. Internal audit caught it and flagged it immediately. But you get the idea.
The argument against system administrators having rights inside SQL Server is that they don't necessarily have a full understanding of what they are doing. But couldn't that same argument apply in reverse? Yes, yes, it could. When I start talking about GPOs, about where things are in registry, about what services are critical, about NIC configuration, about shares and NTFS permissions, quite a few DBAs start getting that glassy-eyed stare. The same stare you get when you start talking recovery models, rebuilding indexes, securables, and the like when talking to most system administrators. So if the argument applies in one direction (no to sysadmins because of the lack of knowledge), it must apply in the other (no to DBAs because of the lack of knowledge). Meaning DBAs aren't local administrators on the SQL Server (and incidently, neither is the SQL Server or SQL Agent service, since it's a simple matter to privilege escalate using them if you're a DBA). Yeah, I said it. I know it's not popular. But it's the logical argument carried back in the other direction.
Another point that is made to keep system administrators out is separation of duties. Sysadmins shouldn't be touching the SQL Servers because it's not in their job duties. As a matter of fact, to prevent one person from stealing everything, the duties are split and so are the permissions. Now, realistically this doesn't work, but it's a good argument. And if it's a good argument as applied to system adminstrators, it's a good argument when applied to DBAs. Meaning DBAs have the rights to their SQL Servers, but not to the servers themselves. Again, yeah, I said it. And again, I know it's not popular. But again, it's the logical argument carried back in the other direction.
So does this mean we should just forget the whole thing? Or does it mean we should just lock everyone down, start building trenches, and lobbing mustard gas at each other (though that's banned)? Well, it depends. Yeah, I said that, too. It depends on your organization. It depends on the data. It depends on the job functions. It depends on the other controls in place. There are enough factors that you can't give one of those "best practice" answers and move on. You really have to consider each and every situation independently. Is that a cop out? No, that's reality. I know of cases where controls say production DBAs can access a particular server but development DBAs can't. That's even more stringent. But if you're talking HR data or intellectual property, maybe that makes sense for your organization. But if you're going to consider stripping local admins from getting into SQL Server for security reasons, then I would think that if you're serious about security, and not just about building personal fiefdoms that you've got to look at the other side, too. You might come to the conclusion that DBAs need to main admin rights. And if that's fine with your organization, there's nothing wrong with that. Just as long as the question is considered in the first place.
Every so often I run into a fellow DBA or database developer who isn't crystal clear on how file/folder and share permissions play together. If you're an MCSE or an experienced system administrator, this should be old hat for you. However, given that I've come across it enough, I thought it might make for a good blog post.
File/Folder Permissions
First, separate file/folder permissions from share permissions. We'll bring them back together in a moment. Let's just start with file/folder permissions. And we'll keep it simple. The ones that often come into play are the following:
Most of the time you're only looking to use read or write in a ETL task or the like. Full control should almost never be needed. You don't usually want your process modifying permissions on files and folders. Now, once we understand the basic permissions, let's talk about how Windows takes those permissions to determine what a user actually has. Unless there is an explicit DENY (like in SQL Server), the operating system aggregates the permissions. For instance, assume user John is a member of the following security groups ETL Users and Support. The permissions assigned to these groups are:
When we put those together, John will have both read and write permissions since the permissions are aggregated. Now, let's say that John is placed in another group, called DBAs. And DBAs have full control. Again, when we aggregate the permissions, John will end up with full control because it comprises read, write, and more. Let's take it one more step and say John is placed in the Developers group. Now the Developers group has a DENY on write. So if we look at all the permissions, we get:
John will have all the permissions given to full control EXCEPT write permissions. The operating system and SQL Server treat the DENY permission the same: it acts as a trump, regardless of the other permissions.
There is one other thing I need to speak on here and that's the concept of inheritance. A file in a folder or a subfolder will, by default, inherit the permissions of the parent folder. You can add to these permissions, but if you want to continue inheriting permissions, you can't take any permissions away (though you could use DENY). So if the parent folder gives a group Full Control but on the subfolder you want the group to only have Read, inheritance isn't going to work. With that said, it is possible to break inheritance. And what this does is allow you to set whatever permissions you want on that file or subfolder, regardless of what the permissions are on the parent folder. However, you must then set and maintain the permissions manually. For instance, if I have a folder called C:\ETL where the security group ETL processes has Modify permissions (the ability to read, write, and delete files) but there is a subfolder C:\ETL\archive where I only want them to have Read permissions, I will have to break the inheritance on C:\ETL\archive and then I will have to set the permissions I want manually.
Share Permissions
Now that we've got the basics of file/folder permissions, let's look at share permissions. Share permissions are like file/folder permissions in that they aggregate and they handle DENY permissions in the same way. With respect to share permissions, there are only a few of them and they are rather straight-forward:
I won't go through an example since share permissions function the same way as File/Folder permissions.
Putting the Two Together
If the file/folder access is happening on the same computer, not going through the share, then only file/folder permissions apply. For instance, if I'm looking to read C:\temp\someDoc.txt on my local system, only file/folder permissions are in effect. I'm not going through a share, so the operating system won't consider the share permissions. But once I go through a share, such as if C:\ETL had a share on it, ETL$, and I access files through it, then both sets of permissions come into play. Here's how the operating system resolves what the effective set of permissions are:
So if I have Change permissions on the ETL$ share (which exposes C:\ETL from the example above), but I only have Read file/folder permissions on the archive subfolder (C:\ETL\archive - see the explanation of inheritance above) that I'm accessing through that share, the most restrictive permission is Read. So when accessing the archive subfolder, I only have Read permissions. At the root of the share, which corresponds to C:\ETL (again, see the explanation of inheritance above), I have Modify permissions. Modify is effectively the same as Change, so I would have the ability to read, write, and delete files at that root level.
Another example is where a user has Full control permissions at the file/folder level but has Read permissions at the share. If the user is accessing a file on the computer locally, then the user can do anything he or she wants. But if the user comes through the share, the user can only read the file. That's because the share permissions are most restrictive.
And there you have it, a quick primer on file/folder/share permissions.
In my security presentations, another basic I talk about is defense-in-depth. The idea here is to produce multiple layers of protection against a particular attack. For instance, imagine malicious code against your home computer. This is a case where you likely already practice defense in depth, as this illustration shows.
Now if you're using a Mac or if you aren't using Vista or if you're using Firefox with NoScript there may be more or less or slightly different layers. But hopefully the picture conveys the idea. Some attacks will get stopped right away. But other attacks, might require multiple layers. For instance, a worm that tries to infect via RPC but can also be triggered by starting an executable would require all the layers, and in some cases even that may not be enough (for instance, if the user double clicks said executable before AV definitions become available and accepts the prompt by the Vista UAC to run as an administrator). Blaster and its variants come to mind right away, though they proceeded Vista. The idea is that the more different layers we have, the more ways an attack is going to have to work or the more security measures that attack is going to have to beat in order to be effective.
With respect to SQL Server we can put multiple layers in place. Here are some ideas:
Now these layers don't help us if a web server with access to the SQL Server is compromised, but hopefully defense in depth has been practiced there, as well. But what it does do is make it that much harder for a rogue system on the network to do any direct damage to the SQL Server. There is always the possibility of coming through shared components (in this case the domain controller is the weak link), but if the DC is up-to-date on patches and the IDS/IPS is up to snuff, it may be very difficult to exploit that system to get around to the SQL Server. That's why we should plan a strategy that involves defense in depth when considering the security architecture for a system. We want to make it as difficult as possible for an attack to succeed while still staying within the constraints of what business has provided for overall security of a system.
Another reason to consider defense in depth is when any one control isn't particularly strong or is flat-out missing. For instance, if a third party application has a hard-coded and weak password for a SQL Server login (I've seen it), then the valid credentials to logon just isn't there. After all, anyone who owns the application (or has supported the application) has that login/password combination. As a result, the IPSEC policy restricting what IPs can connect, along with the hardware firewall doing the same thing, and the NAC/NAP ensuring that a rogue system can't grab that IP may all be what we call compensating controls to counteract that login weakness.
This month there were 3 security bulletins released and 1 re-released:
Microsoft Security Bulletin Summary for March 2009
First, let's tackle the bulletin which was re-released. MS08-052, which was issued for a remote code execution vulnerabilities in GDI+ (graphics rendering). The bulletin was re-released to cover situations where Windows XP SP2 had been applied, then the security patch, and then Windows XP SP3, or Windows Server 2003 SP1, then the security patch, and the Windows Server 2003 SP2. In these cases the new service pack would have overwritten the files, rendering the system vulnerable once again. The new patch release covers those situations. So if you had Windows XP SP2 or Windows Server 2003 SP1 on a system and patched when the security patch came out but then upgraded the service pack, you will need to re-patch the system.
Next, let's look at the three new security bulletins for the month of March. The first two (MS09-006 and MS09-007) affect the Windows operating system. MS09-006 deals with three kernel vulnerabilities and is rated critical. Of these vulnerabilities, the most serious one can allow remote code execution on the system through a specially crafted graphics file. MS09-007 corrects a spoofing vulnerability which would allow an attacker to gain access to an end user certificate and therefore the attacker could then use the certificate to authenticate as the user when certificate-based authentication is used. The last one, MS09-008, affects DNS and WINS Servers. The security patch corrects 3 DNS Server and 1 WINS Server vulnerabilities. Two of the DNS Server vulnerabilities would allow for DNS cache poisoning to be more likely to succeed. The remaining DNS and the WINS vulnerabilities deal with Web Proxy Auto-Discovery (WPAD) records and the lack of proper validation on the submitter. This would allow an attacker to submit a system as a valid web proxy for any computers doing autodiscovery to locate a proxy server to use. As a result, the attacker could then redirect traffic accordingly. While none of the three vulnerabilities affect SQL Server directly, all three require a system reboot, thereby rendering a potential outage of the SQL Server during the reboot process.
If you're an IT pro but not really a security pro, there is a new resource to help you understand the security bulletins as they are released. It is a podcase entitled:
Security Bulletins for the Regular IT Guy
I've listened to the first one and it is easy to understand, covers the new vulnerabilities, and is over in less than 15 minutes. You can download the podcast directly or subscribe with your Zune or iPod.
There is an Adobe Flash Player available to address a security issue. The bulletin shows as being released February 24, 2008, however, my system didn't show an alert for it until this week. Details here:
Flash Player update available to address security vulnerabilities Vulnerability identifier: APSB09-01
The reason I flag it is there are worms going around hitting the social networking sites and one of them tells you that Flash Player is out of date and needs to be updated. It's a new variant of Koobface (Worm_Koobface.AZ) and it spreads by you receiving a link about a new movie from a friend. When you go to click on the movie, it tells you that you need to update Flash and, of course, the executable you download is not an update to Flash but malware. It's a straight-forward social engineering attack but it is effective.
Given that, the best bet is to go directly to www.adobe.com and update Flash Player from that site directly. If you get a different notice saying Flash needs to be updated, you know it's bogus.
Microsoft's advance security bulletin has come out and it looks like they are planning on releasing 3 security bulletins on Tuesday, March 10, 2009. However, none are for the Excel malformed file issue that is currently being exploited. Of the patches, one is a critical rated remote code execution vulnerability. The other two are listed as spoofing and rated Important. The critical vulnerability appears to affect all production operating systems from Windows 2000 through Windows 2008. More information is available here:
Microsoft Security Bulletin Advance Notification for March 2009
Some things and readings today reminded me of this: a security control inconsistently applied is not a control. The whole point of a security control is to provide a check in order to catch malicious behavior. Some controls are preventative. Others are detective or compensating. An example of a preventative control is before entering a building, all employees must badge in to ensure they are valid personnel. However, the way this security control can be inconsistently applied is if an employee allows someone to "tailgate," that is, enter the building without badging in. And here's how you can have a breakdown in the whole control.
The first person to a door has a valid badge and swipes it to be allowed in. The second person is allowed in by the first person and doesn't swipe a badge. Now, in a lot of organizations, if the second person just looks to have a valid badge, then it's considered okay to let 'em in. The control that's been given to the employee is "look for a badge and if they don't have one, send them to the security guard." But what if they do and said badge is forged? The instructions the employee has been given is if the person has a valid badge, let said person in. And it could be tht said person has other equipment/uniforms that make him or her look valid, such as this teen who impersonated a Chicago police officer. In that particular case, there were some things missing (badge, gun, etc.) that should have been noticed but because the kid was in a proper uniform (sans badge or firearm), knew enough of the jargon to sound authentic, etc, he was accepted until near the end of the shift when one suspicious person caught on to the ruse. This is the perfect example of social engineering because the control was not consistently applied (such as asking, "Where's your badge?" A more likely scenario for this discussion is someone coming towards the end of the work day dressed as a member of the cleaning crew. The employee sees the uniform and lets said person in, even though cleaning crew folks were supposed to be issued badges. The lack of a badge was never checked. You have an inconsistently applied control, meaning you have no control.
So what about IT security? If you have a control in place and it isn't consistently followed, how can you be sure it's going to be followed when someone intends on something malicious? You can't. Let's go with a classic example. A user (we'll go with a business analyst since DBAs are sometimes accused of picking on developers) is considered trustworthy and needs to troubleshoot a reporting problem in production and the DBA is in a hurry. Maybe the user only needs to hit 25 out of 500 tables in a particular database. None of those have any sensitive data. The procedure says to only grant access to the tables needed. However, the DBA is busy as there are other fires to put out, so he or she takes a short cut and makes said user a member of the db_datareader role. That means the user not only has access to the 25 tables needed to troubleshoot the problem, but all 500. And some of those 500 tables do have sensitive data. But the user is trustworthy and it's not an issue, right? After all, the user will figure out the problem and then the rights will be revoked. "No harm, no foul."
Fast forward a bit. On break, the user was browsing around, went to what was a legitimate site, but the site had been compromised. Malware taking advantage of a previously unknown or a known but not yet patched vulnerability was present and the system is compromised. While the user might be trustworthy, the person who set up the malware and now has the ability to control said computer is not. And remember, the user is logged in with his or her credentials. And those credentials have access to said database where the user has db_datareader rights. Which the attacker may notice (by running a netstat -an | findstr /i :1433 on the system and noticing the connection to the default instance of a SQL Server) . And the attacker puts in a script to interrogate said server, finds said database where user has db_datareader, and realizes he or she has access to some juicy info. At which point the hurried action of the DBA has now cost the company.
While I realize that the example I gave is a drawn out one and not likely to happen very often in practice, the problem is when it does happen, how do you put the pieces together to realize what was compromised? This is just a single example, but it reveals a bigger problem. Let's assume that the malware is discovered quickly on the workstation and it's sent down to be re-imaged. No one does any forensics because it's a malware problem that happens every day and the standard procedure is not to fool with it but just get the box back up in a timely manner. The problem is that no one other than the DBA knew what rights the user had. No one suspected the user had db_datareader rights because that would have violated a security control. Of course, the DBA is not privy to the day-to-day workings of the workstation support team (nor does he or she care to be), so said DBA has no idea the user's workstation was compromised. See the problem?
Scenarios like this are why an inconsistently applied control is not a control at all. There were controls in place to protect data security. They were bypassed because the user was "trustworthy." And because there was a security compromise that, at first glance, seemed no big deal, not only was there the potential for sensitive data to be lifted from the organization, but no one was the wiser to it. That is, until some of that data starts appearing on the Internet or a batch of folks get hit for identity theft and investigators trace it back to said organization. Had the data security control been followed appropriately in the first place, then there would truly have been "No harm, no foul" with respect to the sensitive data.
Today, Microsoft release a security advisory about a new vulnerability in Microsoft Excel. This one affects both PCs and Macs. The Microsoft Security Response Center blog has a post there as well. SecurityFocus has a bit more information, basically indicating Symantec is detecting files containing an exploit to the vulnerability as Trojan.Mdropper.AC.
Basically, the vulnerability is an attacker can create a malformed file which can then execute code on the system. That code will run in the context of the user opening the Excel document.
Thus far the indications from both Microsoft and Symantec is the attack is not very widespread. However, as with any attachment or download, make sure it's from someone or somewhere you trust and make sure it's expected before opening said file.
There is an active attack in the wild for the newly announced Adobe Acrobat and Adobe Reader vulnerability. While the attack isn't widespread, there is always the possibility of copycat attacks. One of the recommended suggestions is really easy to do, so I'm posting it here, and that's to disable JavaScript from within the Adobe application. For instance, with Adobe Reader 9, if you select Edit | Preferences, you'll bring up a dialog menu, one of the options of which is JavaScript. If you click on it,you'll have the option of disabling JavaScript within Adobe Reader. To do so, uncheck the box as show here:
Right now there's not a patch out, but this is one way to minimize your exposure which shouldn't drastically affect productivity. Adobe has said they'll have a patch for the version 9 builds of these applications by March 11, 2009, with patches for versions 8 and 7 to follow afterwards.
After some recent talks with security folks and auditors, one of the things I have had a hard time getting across is that you must trust those folks responsible for account and server management when it comes to securing your data. Yes, you can put in a lot of deterrents, but at the end of the day most of them can be beaten by a wily admin. So if you're not hiring trustworthy people, that's the first and most important weak link in your chain. Now this isn't to say that you won't have to deal with the case of someone who has gotten disgruntled or who has gotten in a situation where they can be manipulated. Though rare, those things happen. But at the end of the day, you must trust your server and account administrators.
Let's look at an attack vector to explain why. Best practices with Windows security says to do permissions management using security groups. So typically you'll have a Windows security group for your DBAs' elevated accounts (if you aren't using elevated accounts, here's why you should). What's to stop said account administrator from creating an account and dropping that account in said group? The account only needs to stay there long enough to get the data and get out. So the account admin does just that. Creates a new account (to try and avoid non-repudiation), adds it to the security group, and then logs on to the SQL Server with sysadmin rights. Now everything on that server is accessible. Yes, even the database you encrypted using Transparent Data Encryption (see previous post about how this doesn't stop an admin). So you've got to trust the folks working account administration.
What about server administrators? You've got running SQL Server in single-user mode, as I described last post, but there's another trick possible, too. It used to be the recommended advice for handling the permissions was to do the following:
Windows User Account -> member of Domain Global Group -> member of Server Local Group -> Access to Resource (file share, SQL Server, etc.)
If you're still following that model, the server admin can simply make a new account a member of that local group on the server. And the account could even be a newly created account on said server, for the same reason the account administrator might do so. So the admin now has access to SQL Server and the data. If you're not following that model, and you're only using domain security groups, then there are the attacks I gave before still work. In addition, for SQL Server 2005 installs which aren't on a cluster, there are the MSSQLUser and SQLAgentUser local groups which have sysadmin rights within SQL Server as well.
So it sounds like you can't do anything about it. And to a certain point that's true. If someone is bound and determined to get the data and has those types of rights, they're going to be able to create a way. By following best practices you will cause those who aren't hell bent on getting in to stop, especially after they see any sort of resistance. But what about that 0.1 or 0.01% that will do whatever it takes? The catch then is to go from prevention to detection. Solid auditing is required. There are a few event IDs you'll need to watch for in the Security event logs:
These event IDs need to be monitored on the domain controllers as well as any servers where SQL Server is running. The events themselves will need to be investigated to ensure that the the appropriate groups aren't being changed. And if you're talking about the local to the server groups, you'll also need to be getting the events off of the server and into a secure repository in as timely a manner as possible, because the admin could always purge the security event log. And if you've been thinking along the attack vectors I've given already, you've thought of the idea of creating a local account, making it a member of the local Administrators group, and flushing the Security event log under that user account, thus erasing who created the account in the first place.
Now back to my original premise, you can automate all of this and have a great auditing system, but at the end of the day you must be able to trust the people you hire into these positions. Even if the person gets caught, they've still gotten the data. You may be able to prosecute them and recover your losses from a financial perspective (to a certain extent), but you've still taken a reputation hit and if it's privacy information, you've got a bunch of folks who are now at risk. If it was intellectual property, then it could be quickly sold or just given away to another entity. Trust them, but check up on them with sound auditing.
This is a little old (five days based on the last update), but TrendMicro put on their blog about fake LinkedIn profiles which have links leading to malware.
The basics:
With respect to the last point, it's still disturbing. Again it's a case of attackers going after a legitimate and trusted site to get users to be directed to malware. In this case they aren't hacking the site, rather using the functionality provided to end users.
Hopefully by now everyone has seen this, but if not, here's a reminder to continue to spread the details. Denis Gobo made a post earlier today and Security MVP Randy Franklin Smith sent out a newsletter after being prompted by his MVP lead.
The worm is known as Conficker or Downadup and here are the details from Microsoft. It attacks a vulnerability that was patched in MS08-067 (released in October) by attacking the Windows Server service, which, by default, all Windows machines have running (even if you're running a workstation... that's how you can have file shares and share out your printer). One of the nasty sets of things it does is disable automatic updates as well as AV / Malware protection that would try and stop it. It will also potentially block certain security sites (like where AV downloads from) based on a string search (more information in the details link).
In addition, it will flood the network and launch password hack attempts against user accounts, resulting in locked user accounts if there is any account lockout policy in place (and there should be). I've already had one friend whose network got hit by this and it was a hard clean-up of their environment as a result. Therefore the best way to stop this thing is to patch. After all, that patch was released in October. That was over 3 months ago now.
One last attack vector, and that's via removable drives. That's why I blogged about disabling autoplay. Not only does it stop those annoying splash screens when you insert a CD or DVD or the dialog window asking what to do when you insert the USB drive, it also ensures that by inserting a removable drive you don't get a social engineering based attack in that dialog window.
I was browsing through the new titles that are on Safari and saw some planning guides around Windows Server 2008 (Active Directory Services, File Services, etc.). Of course, all of these are published as solution accelerators, because they are designed to assist IT professionals understand, plan, and implement solutions quicker (hence the term, solution accelerator). Some of these documents have been around for a while, but the Solution Accelerators section groups them all into one place. If you've not run across them before, you can find them here:
Microsoft TechNet : Microsoft Solution Accelerators
Among them are the Infrastructure Planning and Design Guides. There's guidance on Windows Server 2008, virtualization, and even IIS. But unfortunately, none out there yet for SQL Server. If you're looking on the security side, there are the OS security guides from Windows XP on up, including for Vista and 2008. Sorry, no Windows 7 yet.
If not, you should. Because autoplay being on means any time a removable data device, such as a USB stick, is inserted into the computer, autoplay fires up. Typically this is usually taken advantage of by CDs/DVDs to get us to a start menu. However, attackers are now writing worms which spread to removable devices and take advantage of autoplay and a social engineering trick to try and infect the system. More details here:
SANS Internet Storm Center: Conficker's autorun and social engineering
Me personally, I have autorun disabled because I don't want to see some pop-up window every time I stick a USB drive onto my computer, as I switch out between several on a daily basis. I also don't care for CDs/DVDs autostarting on me when I may be simply looking for a couple of files in a directory on the CD (such as documentation). If you want to know how to disable autorun, here's a Microsoft KB article which explains the steps (you'll need admin rights on your system):
KB953252: How to correct "disable Autorun registry key" enforcement in Windows
Don't be thrown off by the name. It was written to correct another KB article. And in the article it has the steps on how to disable autorun. You'll want to do it for all drives if you go the GPO route (which is the simplest).
Microsoft tries to stick with the second Tuesday for security bulletin releases and has only released out-of-band a handful of times since going to that release cycle. Well, today's the day as it looks like another out-of-band release will come out:
Microsoft Security Response Center blog post
Microsoft Security Bulletin Advance Notification release
The information provided indicates it's a remote code execution vulnerability affecting Microsoft Windows that's rated critical.
