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 see a post in a forum or on Twitter about the use of xp_cmdshell. Usually it is someone looking for a reference as to the security risk of doing so. So let's look at what using xp_cmdshell means.
In SQL Server 2000 we used to have a recommendation to drop the extended stored procedure if you didn't need it. The problem is it was included as part of xplog70.dll which had other extended stored procedures which were needed. That meant you couldn't just move or delete the .DLL that was referenced. So an attacker only had to re-add the extended stored procedure. The default permissions for xp_cmdshell was that no one has it. And that meant only members of the sysadmin role could execute it (since members of the sysadmin fixed server role bypass permission checks). But members of the sysadmin role could re-add the extended stored procedure if you dropped it. So there wasn't much you could do to remediate this one.
In SQL Server 2005/2008 we have the ability to disable xp_cmdshell. Again, the default permissions is that no one has the ability to execute the stored procedure, meaning only members of the sysadmin server role can. And if you're a member of that role, you can re-enable xp_cmdshell. With SQL Server 2008 we have a bit of additional control in the form of policy-based management, but it's still do-able. Again, there's not a whole lot one can do to prevent its use by those with that level of permissions.
So what's the big deal about restricting its use? The main reason is the context in which xp_cmdshell runs and what it gives access to. First, the level of access. If xp_cmdshell is being called by a member of the sysadmin role, it is going to execute in the context of the SQL Server service account. So what ever the service account SQL Server is running under, a member of the sysadmin role can execute as through the use of xp_cmdshell. So if DBAs don't normally have administrative rights on a server, but SQL Server is running under a service account that does, then through the use of xp_cmdshell they have effectively escalated their rights to be at that level. By the way, this also means any processes which run under an account that is a member of the sysadmin role, such as a SQL Server Agent job owned by sa, runs at this level, too. And this is the first reason we are usually hesitant
Second, and finally, is what it does. The xp_cmdshell effectively drops you to a command prompt. You just have to know what command you're executing first, but you get the idea. So just about everything you can do at a command prompt, you can do through xp_cmdshell. When you couple this with the permissions that the SQL Server service account is likely to have, you're talking about the potential for some major damage. At the very least, an attacker could use xp_cmdshell to shut down key services and processes on the server (especially if coupled with something like pskill) rendering the operating system in an unstable state where it forces itself to reboot. You get the idea.
So when you couple level of access and what it can potentially do, that's the reason xp_cmdshell is disabled by default in SQL Server 2005/2008. Yes, a member of the sysadmin role can undo this, but generally you should auditing, policy management, etc., to enforce this configuration setting. Plus there's this whole idea that if it is something that needs to run from the command prompt, use a batch job or an appropriate Operating System (CmdExec) job step within a SQL Server Agent job.
The other day my wife and I were driving around and noticed that a Little Caesar's location had shown up near our house. The kids love their pizza and at the price, you can't beat it. So we decided after karate tonight that we'd pick up pizzas from there and head home to consume the delectable product. So we do just that, only after pulling up to the location we're confronted with the sign, "Coming Soon." Argh. No Little Caesar's pizza. We end up going another place, but talk about dashed expectations.
I feel the same way whenever I look at sys.sql_logins and realize that there's not a last logon column. I understand how difficult this can be (and how useless the information may be) when it comes to tracking this information for Windows-based logins. After all, if we grant access to SQL Server via a group, then the only login entry we'll see is for the group itself. So what is the best way to track the login information? Whenever any Windows user that is a member of the group logs in? If so, does this really do us any good? Probably not.
But when it comes to SQL Server-based logins, I frequently see questions about how to determine when a login last connected to SQL Server and how best to determine that information. The answer we always have to give is that the information isn't available within SQL Server. You might be able to track it in the SQL Server error log or the operating system's application event log, but you'll need another system to keep track of that information, especially as events roll information out of those two sources. So we end up talking about building workarounds like logon triggers that fire and store the information and we have to implement those solutions on every SQL Server we build. I would prefer that it was built-in and that we didnt have to build a custom solution every time.
To put things in perspective, we do track an account's last logon in Active Directory. This information is stored locally on each domain controller, meaning to have truly accurate information, you have to query every domain controller. As of Windows Server 2003 Active Directory, this information is replicated on a regular basis, but not quick enough to be accurate for audit purposes. I can understand the rationale. There's a lot going on within Active Directory (AD) and a lot of information that needs to be distributed, especially in a multiple master model which reflects Active Directory's domain controller architecture. And when you consider that there are potentially hundreds of domain controllers, you try to make sure what gets replicated is what absolutely must be replicated (like security group changes, account deletions, etc.).
A SQL Server login doesn't suffer from the same issues with respect to the multiple master model. The login is only valid for the specific server. I don't know if there are the issue is a potential locking issue or something of that sort which prevents the storing of such information. However, AD would have the same issue and handles it. Within SQL Server, if auditing is turned on, there is already some work having to be done on this front, except the information is being written to a couple of files. So maybe I'm not seeing something that I should, but I would think that adding last logon information is doable. I know from an audit perspective it would be helpful. From an administration and security perspective it would be, too, because it would indicate to me whether or not I can delete a login based on inactivity. And along those same lines, if I have an application that has a SQL Server-based login, folks have made changes so it's either using another login (such as a Windows login) and I can't really afford to have that app have any functionality unavailable, then having that last logon information will tell me if all the changes were a success without an outage for my users. So that's another reason I'd like to see it implemented in a future version of SQL Server.
Welcome back to our last class this term on security in SQL Server here at SQL University. According to the syllabus, you should be picking up history next week with Jorge Segarra. SQL Server has a rich history. I know you'll enjoy the coming week. However, before we move on, let's close on some intermediate security topics. First let's talk about something I call "multiple paths."
"Hi, my name Is... What? My name is..."
Within Windows, a Windows user can be a member of numerous security groups. And the way permissions work, permissions can be assigned to any, all, or none of those security groups. For instance, when I was an infrastructure and security architect, I wore several hats. Each of those hats was mapped to a security group. So if you looked at my membership, here's what you would have seen:
It's entirely possible to allow multiple security groups to connect to SQL Server. For instance, on a particular SQL Server, Server Admins could be allowed in as well as DBAs. DBAs would have full rights over the SQL Server. Server Admins would have rights to use a couple of databases, but not all the databases on the SQL Server. So when I come into SQL Server, who am I? It looks like a complicated mess, but really it's not. I'm both a member of DBAs and a member of Server Admins. So as far as SQL Server is concerned, I come in as both. That's right, both. So that raises the next question: What are my permissions?
If you remember back to last class, I talked about how you could have multiple permission sets. We used the example of three sets, which I called sets A, B, and C. And we talked about how the permissions added up, unless there was a DENY involved. Well, what we were discussing then is possible based on the situation I'm presenting now. I'm authenticated by SQL Server against the login for Server Admins and against the login for DBAs. And since the DBAs have complete control over the SQL Server, I have complete control over the SQL Server.
Let's take another example. Assume I'm not longer in the DBAs security group. Let's say the Server Admins have access to a database called ServerInventory. The Incident Response Team Members have access to a database called IncidentResponse. And there's another database called DBAsOnly that only the DBAs can get into. Since I'm a member of Server Admins, I have access to ServerInventory. Since I'm a member of the Incident Response Team Members I also have access to IncidentResponse. But since I'm no longer a member of DBAs, I do not have access to DBAsOnly. Make sense?
Another way to think of it is imagine a row of lockers, like back in high school. Being a member of Server Admins gives me a key to one locker (database). Being a member of Incident Response Team Members gives me a key to a different locker. The school (SQL Server) looks and sees I'm a member of both groups. Because of this, it hands me both keys. But since I'm not a member of DBAs, I'm not handed the key to the locker for DBAsOnly. Hopefully that gives you a visual picture of how SQL Server handles who you are. This is done both at the server level as well as at the individual database level. And because I can hold multiple keys (logins), those can map to multiple permission sets, like what we covered on Wednesday. Really, this is no different than file and folder permissions at the operating system level. It works the same way in SQL Server as it does in Windows.
"Know your role!"
The Rock had many sayings when he was in World Wrestling Entertainment (formerly the World Wrestling Federation), but among them was, "Know your role!" When it comes to SQL Server, this saying is important. At the server level, it is possible to put a login into several pre-defined roles which we call fixed server roles. These give a blanket set of permissions. Take, for instance, the sysadmin fixed server role. That role allows you to have complete control over the SQL Server. If a login is a member of that role, it can do anything within that SQL Server. And just like at the server level, there are roles at the database level, too. There are fixed database roles which have preset permissions, just like server roles. There are also user-defined database roles. These are role you can create. They are just like Windows security groups in that they are designed to group together database users. Also, you can assign permissions against the role, just like with security groups. Therefore, at the database level, the best practice recommendation is straight-forward:
Ownership Chaining
Ownership chaining is a feature of SQL Server. Basically if you have two objects, and they have the same owner, they can form an ownership chain. For instance, let's say I have a table and a view. The view refers to the table. If both objects have the same owner, then I can have an ownership chain. Let's say I have the ability to issue a SELECT against the view. But I have no permissions against the table. If I try to issue a SELECT directly against the table, I'll get an Access Denied error. But if I issue a SELECT against the view, it works. It works even though the view hits the table. Why? Because SQL Server allows an ownership chain to form.
Basically, SQL Server sees that the same user owns both objects. So it assumes that if the owner has built one object to refer to another, like our view referring to our table, the owner meant for the reference to work. And as a result, when you go from the view to the table, SQL Server won't check permissions on the table. It will assume the owner knew what he or she was doing when the reference was created. We can use this to control access to the base tables in our databases. By controlling access through functions, stored procedures, and views, we can control how the data is displayed and how the data is manipulated. With ownership chaining, access to the base tables happens, but only through the methods we've explicitly built. So, for instance, this won't work:
DELETE FROM dbo.SomeVeryBigTable; GO
If an end user tries to execute this, that person will receive an Access Denied type of error. This is good because imagine if they were able to execute it. Then we're stuck in recovery mode and that's bad. Now imagine we built a stored procedure like the following:
CREATE PROC dbo.ControlledDelete @SomeID INT AS BEGIN DELETE FROM dbo.SomeVeryBigTable WHERE SomeID = @SomeID; END; GO
In this case the end user can still delete rows. But our stored procedure only allows the deletion of one row at a time, and you need to know the right ID. We now only have to grant EXECUTE rights against this stored procedure and everything will work as we'd like. We don't have to grant any sort of permissions against the table itself. All right, enough on the theory. Let's round out the week with a few demonstrations. Afterwards, look to our coach for some drills to help you get stronger in SQL Server security.
Practical Demonstrations:
It's good to see all your bright and chipper faces here at SQL University. Hopefully you've digest Monday's lesson on Authentication. Today we're going to discuss Authorization. Now Authorization can only take place after Authentication has been accomplished. If SQL Server doesn't know who you are, it can't determine what you have access to. Therefore, if you've not gotten the class notes and looked at the videos, it may help you to review before proceeding further. If you're caught up, let's continue. Authorization is simply the permissions a particular person has. With respect to SQL Server, SQL Server is basically saying, "Once I know who you are, I can tell you what you are allowed to do."
Permissions - A Game of "Simon Says"
If you've ever played the game of "Simon Says," you know that you only react to the instruction given when Simon says to do so. If you react to any other instruction, you're out of the game. If Simon says to do something and you don't, you're out, too. SQL Server works kind of like Simon says. If you tell SQL Server that a certain permission is to be given to a user, then it's like you played Simon says, only you are Simon. SQL Server will carry out that permission exactly as you said. So if you tell SQL Server that Jimbo has the ability to read data from a particular table (granting SELECT permissions on that table), then SQL Server will allow Jimbo to read data from that table whenever he asks for it. Likewise, SQL Server will not allow access if you do not give explicit permission. SQL Server is the ultimate Simon Says player. So if you don't tell SQL Server that Jimbo can read data from that table, SQL Server won't let him (with the exception of ownership chaining, which we'll look at on Friday).
Securables - A Whole Lot of Tupperware
When I was growing up, Tupperware was expensive. It's not like today when I can run down to the Dollar General and pick up disposable containers for a couple of bucks. In SQL Server, starting with SQL Server 2005, there is a new concept called securables. Securables, in a nutshell, are anything you can assign permissions against. There are also special types of securables called scopes, which are nothing more than securables that can contain other securables. Within SQL Server there are three scopes:
Servers have securables like logins and endpoints as well as the securables/scopes databases. Databases have securables like users and symmetric keys as well as the securables/scopes schemas. And schemas have the securables we normally think of like tables, views, and stored procedures. That's the hierarchy. Now, normally we grant permissions directly against tables, views, and stored procedures. So if I grant SELECT on a table, then the person can execute a SELECT query against the data in that table. If I think of Tupperware, my wife has given me permission to open up the Tupperware containing the salad. But the Tupperware containing the lasagna, well, that's off limits. So I have SELECT rights against the Salad Tupperware but I have no rights against the Lasagna Tupperware. SQL Server is my wife, but SQL Server is always on duty. So if I try and open up that lasagna, I'm going to immediately get an Access Denied!
Now let's take this a step further. If you do it right, you can put smaller Tupperware containers inside larger Tupperware containers. This is the concept of using scopes in SQL Server. Imagine that the lasagna container fits inside the salad container. My wife has given me permission to open up the salad container. Lapsing back to my past as an 8 year-old boy, I interpret her permission as saying, "You can eat anything in the salad container. So I take the lasagna container and put it inside the salad container, dumping any salad that prevents the true prize from fitting. I close up the salad container and then say to myself, "Well, time to eat what's in the salad container." And then I proceed to open both containers, first the salad one, then the lasagna one, and eat the lasagna. Sure, my adult self knows I'm in trouble as soon as my wife catches on, but we're talking lasagna! SQL Server operates as our eight year-old selves do. If you give the permission on the larger container, or scope, it applies to the securables contained within. For instance, if I give SELECT permission against a schema, all the tables and views contained in the schema that permission applies to as well. So we need to think about that when it comes to giving out permissions. We can either give it directly to the object (securable) in question or to the scope that contains it.
GRANT, DENY, and REVOKE - Understand the Interaction
GRANT gives the permission. So if I GRANT SELECT on a table, the person can read from the table. DENY blocks the permission. So if I DENY SELECT on a table, the person cannot read from the table. Also, DENY is a trump. If you've played the game of Spades, all spades cards trump cards of any other suit, regardless of numeric value (so a two of spades will trump a 10 of diamonds or an ace of clubs). When you've got spade against spade, it's all about numeric value. As a result, unless you're playing with house rules which include jokers, nothing trumps the Ace of spades. The DENY is like the Ace of spades. It doesn't matter what the other permissions are, if a particular person has a DENY, it will trump any GRANTs that the person may already have. And that bears explanation.
It is possible for multiple sets of permissions to apply to a particular person. We'll get into the how on Friday, but assume that three different sets of permissions apply to a particular person. Set A grants SELECT against a table. Set B grants INSERT and UPDATE against the same table. SQL Server will aggregate the permissions and give you the sum of all of them. So if we're just considering sets A and B, the person has SELECT, INSERT, and UPDATE on the table. If you've worked with permissions at the file and folder level on the operating system, you should be familiar with this type of behavior. Now let's look at set C. Set C has a DENY on UPDATE. Remember, DENY trumps everything. So the DENY on UPDATE in set C is going to trump the granted UPDATE in set B. So once we consider all 3 permission sets, the person really only has SELECT and INSERT.
By the way, you can replace one for the other. So, for instance, assume set C should have been a granted UPDATE instead of DENY. If you decide to replace DENY with the GRANT, you can do so simply by executing the GRANT permission. So when applied in the same manner (again, covered on Friday), they will replace each other. So if that's the case, why do we need REVOKE? REVOKE is a giant eraser. If the person has a particular permission, REVOKE removes it. It doesn't matter if it's GRANT or DENY. Instead of overwriting the permission, it simply gets rid of it. So if we go back to set C, and we didn't want to grant UPDATE permissions, but we wanted to get rid of the DENY, we could use REVOKE. If set C had permissions for other tables, we might not want to get rid of set C altogether. But we do need to correct the mistake without granting any additional permissions. In this case, REVOKE is ideal. It gets rids of the permission altogether. If the person doesn't have permission via another mechanism, we're back to that game of Simon Says again. Since Simon (or John or Marie or whoever you are) didn't say the person should have access, then the person doesn't gets access. If, however, the person had access through another means, such as set B, then the DENY is no longer there to block them.
Practical Application:
Okay, enough theory. Like last class, let's see some of this in action. Again, the videos are around 5 minutes or shorter.
Welcome back to class, folks, here at SQL University. This week we're going to take a look at the basics of security for SQL Server. Since this is an introductory class, we're going to focus primarily on SQL Server itself, only delving into the operating system when we have to do so. So let's start with first steps. In security there are usually three As we're concerned with:
While auditing is important, that's a more advanced topic, so we'll stick to the first two As: authentication and authorization. If you remember back to your first week of class, we briefly talked about authentication. That's what we're gong to focus on today. Authorization will be for Wednesday's class. And for Friday we'll look at both in a bit more detail. Now, onto today's material. One of the things we discussed is that either SQL Server can handle the authentication or it can pass it off to Windows. Windows authentication can also be called integrated security. Since Microsoft recommends using Windows authentication, let's talk about why.
Windows Authentication - Ease of Use for the End User:
Imagine for a second that you're the manager of an apartment complex and let's say you've got 250 different apartments. Now, you've got two choices on how to handle your access to these apartments you're responsible for. Either you can have 250 different keys or you can have one master key. Which would you choose? If it were me, I'd want one master key for me. Sure, the system needs to be able to handle issuing 250 different keys so one apartment dweller can't get into the apartment of another, but for me and my maintenance staff, I need the master key. Otherwise I could sit there forever trying to figure out how to get into the one apartment where the toilet is overflowing and flooding the apartment below. Not a good situation if I have to sort through 250 different keys, right?
Well, imagine your internal network is like a huge apartment complex. And let's say you have 250 different systems. If your end users have to enter a username/password combination for every system, your users would quickly grow upset. Even if we're talking 5 to 10 systems, you're going to hear grumbling. If you make it worse and they have to have different passwords for every system, you've really got trouble. Sooner or later they're going to do things like keep a sticky note on their monitor with all of their combinations recorded. But if you can give them one key to access all of the systems, they'll be very happy with you. And that's one reason to use Windows authentication. Your users already have to use a username/password combination to log onto your Windows domain. If we can take those credentials and re-use them, they'll be very happy. It also means they are less likely to do something like that sticky note trick. I did say less likely. Get used to it, because no matter how easy you make it on your users (unless you take passwords away altogether), someone is going to do it. In times like those, take the time to show 'em right. Help them understand why it's dangerous. And if you don't get through the first time or the hundred and first time, keep at it. It's important.
Windows Authentication - "One Stop Shop" Lockdown:
But you know, it's not just about ease of use for our end users. Imagine you went the 250 key method. Now you were trying to be smart and you got keys marked Do Not Duplicate. While this isn't an effective way to ensure they keys aren't duplicated, let's say, for example's sake that it is. And one of your maintenance folks was caught doing something they ought not be doing (like stealing from one of the apartments). You now need to get all the keys back from that maintenance person. And he hands you 250 keys which look right. The question is, "How do you know?" The only way you're going to be able to tell is to go through and try each key in the locks until you're sure you have them all. After all, there's nothing stopping the maintenance person for substituting several keys that look authentic but aren't. And then your maintenance guy still has access to some of the apartments in the building. So unless you take the time to immediately verify all the keys, you have a problem. The same thing is true if you have folks with different username/password combination all over the place. You might be able to turn them off in one place, but unless you go system by system, you've got the same problem if someone's access needs to be turned off.
Now let's go back to that master key idea. If you only issued them master keys, and you have some way of verifying they weren't able to duplicate the master keys, once you get it back from the maintenance person you're letting go, it's a simple matter to try it in a handful of apartment locks to verify it's real. And then you can kick the guy to the curb. Well, if Windows user accounts are being used, specifically domain user accounts, you have the same idea. You've got an employee who has been doing something they ought not do, and it's time to take away their access as HR processes them out the door. If all access is through the domain, then as soon as I lock down that person's domain user account, I have confiscated the master key. They aren't going to get in anywhere. And that's what we want.
Windows Authentication - Security Forces at the Gate:
As a member of the United States Air Force, one of the job types I came into contact with every day were what are known as Security Forces (like the guy to our left). These are people whose job it is to provide base and flightline protection. Because they did this job, I could focus on my job: computers. Now, they weren't just base and flightline defense, either. They also were law enforcement personnel. If you were speeding on base, it would be these guys who would pull you over in a heartbeat. And while I know that some civilian law enforcement personnel have the discretion to give a warning instead of a ticket, I never met one of these guys who did. They took their job seriously, whether it was pulling people over for speeding, preventing looting of a base devastated by a hurricane (Keesler Air Force base after Hurricane Katrina), or trying to ensure no one is trying to get on base that shouldn't be there. Well trained and well motivated, they will always do a better job than someone like me, because they are trained for it, constantly do it, and constantly retrain on it.
And that describes Windows authentication. The Windows operating system already has authentication functionality built-in. It has to do so. And if you're in a domain environment, there are special computers, called Domain Controllers, where authentication actually takes place. Those systems tend to be treated more security than regular servers, even SQL Servers. And therefore, if the username/password combinations are going to be stored on the most secure systems in the domain, that would be on the domain controllers (as a side note, a hash is what is actually stored there, not the password itself). If you let Windows do the authentication, then SQL Server doesn't have to store anything related to a username/password combination. And it's up to Windows to check the security. In other words, SQL Server is handing off this sort of authentication to processes which do it constantly. That's good, because it also means that if you're a DBA and your organization is large enough to have someone else managing the domain security, then you can leave the authentication to them, which is something they're doing constantly. And you have the option of focusing on what you do best: SQL Server.
So Why SQL Server Authentication?
Because you have to. Yup, I said it. The classic example is a third-party application that only supports SQL Server based authentication. You can pull out your hair and gnash your teeth all you want, but if the vendor isn't going to change the product and you need their product, you're stuck. And you'll have to allow SQL Server to manage the authentication. That's not as bad as some may make it seem. SQL Server does a secure job of things. However, you lose the advantages I've mentioned above.
Another classic example is when you have a system that isn't on the domain that needs to talk to the SQL Server. For instance, you have linux servers that you want to connect back with and in that case, SQL Server authentication is the most straight-forward way to go. Another classic example along these lines is a web server that sits in the DMZ. Not the one between North and South Korea, mind you, but where you have your Internet facing systems located. These systems are partially protected from an Internet attack and your internal network is protected from most attacks that could originate from these DMZ servers, should those servers be compromised. So it's a buffer zone between your internal environment and the Internet. In this case, it can be considered too risky to put said web server in the DMZ on the internal Windows domain. If that's the case, you can't use Windows authentication to connect. So you've got to use SQL Server authentication.
Some Practical Instruction
Okay, let's depart from the theory and look at some of this stuff in your SQL Server environment. Here are some videos which explore the topic of authentication a bit further. They're all short, five minutes or less, and should give you a basic foundation on how folks come into your SQL Server:
I'll be doing three talks this week, starting tomorrow (Tuesday).
If you're able to make it out for any of the three talks, I'd love to meet you. Whether your new to the crowd or an old hand in the community, relationships are what make it work.
For Charleston, I'll likely have my family with me. I'm a Citadel graduate and my wife is a College of Charleston and The Citadel Graduate College graduate (she has an M. Ed., which makes her the smartest one in our household), so we get back as a family whenever we can. At some point I'm going to get my oldest son involved in helping with the presentations because he loves doing things with me and he's about the right age to be able to help out. But that will probably be starting next year. I need to start his SQL Server education properly, but that means starting with the idea of set theory and functions and building from there.
And if you can get out to the Carolina Code Camp in Charlotte, you won't be disappointed. Each block is chalk full of great sessions and the only problem you'll have is deciding which session you want to go to the most. There isn't a single block where there isn't at least two sessions I would like to be at, so it looks like I'll have to do some random determination if I can't decide. So come on out, meet others who are in the same field, and start building connections and bonds with others who are passionate about technology.
Jorge Segarra (@SQLChicken) has put together an idea to have on-line basic lessons on SQL Server called SQL University. The idea is to present two to three lessons each week in a somewhat academic manner, one that will hopefully encourage learning. It is targeted at the new or "accidental" SQL Server DBA. In this endeavor he has asked around for others in the community to pitch in and help. I've volunteered to take on Security Week (sounds like Shark Week, a nod to my friend and fellow SQL Server MVP, Frank Kalis, who loves sharks).
Jorge has already gotten the first week of lessons up. In addition, he should be making an annoucement soon as to some of the other faculty members as well as the athetic director so stay tuned here:
SQL University
In this presentation we'll look at the use of triggers for added database security. We'll start with the typical DML triggers used to audit changes to data and best and worst practices with their implementation. Then we'll move into SQL Server 2005 and 2008 with the use of DDL and Logon Triggers which allow us to prevent changes to the database schema, to report on certain types of login behavior and to even block logins if we see them from unauthorized applications. In addition, we'll briefly consider the Audit object, a feature of SQL Server 2008 Enterprise Edition, which allows us to finally perform audits on SELECTs against critical tables without having to resort to SQL Server traces.
I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
SELECT CASE dp.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE dp.state_desc END + ' ' + dp.permission_name + ' ON ' + CASE dp.class WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']' WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']' WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']' END + ' TO [' + USER_NAME(grantee_principal_id) + ']' + CASE dp.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' ELSE ';' END COLLATE DATABASE_DEFAULT FROM sys.database_permissions dp LEFT JOIN sys.all_objects o ON dp.major_id = o.OBJECT_ID WHERE dp.class < 4 AND major_id >= 0 AND grantee_principal_id <> 1;
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.
but I don't mean with respect to privacy. But I do mean with respect to the time it takes securing a system based on its usability. Here's a quote:
"What many fail to grasp is that security is a zero-sum game: the easier it is to use something, the more time and efffort must go into securing it." - Hacking Exposed Windows Third Edition
I couldn't agree more. The reason I bring this up is I've seen and heard of colleagues who have a system that the business wants to keep wide open, but the business also wants it as secure as possible. No problem, but it's going to take time. The problem is often that the business has a false expectation of how much time it should take. This corollary basically points out that if the system is wide open, expect that it's going to take time for the technicians to lock down the system. Actually it's going to take a while to figure out how to go about securing a system without affecting usability in a noticeable way. And it's usually not as simple as dropping everything into one group's lap and it's done.
When it comes to SQL Server, this all holds true, too. So if you want everyone in the organization to query the data warehouse and you are worried about ensuring they don't walk away with your critical data, it's not so simple to try and dump this on the DBAs. And it's not going to be something that the right personnel are going to be able to secure overnight. Some things they are up against in this usability scenario:
From what I've just described, none of those exploits are really within the domain of the DBA. You've got workstation admins, network security personnel, etc. involved now. And you've got multiple layers of defenses that are going to have to be planned, test deployed, debugged, and then rolled out to try and prevent these and other methods of walking off-site with that sensitive data. Because now they have to walk that line between usability and time to secure. You don't mind them impacting usability? Fine, they can lock things down quick. But you want to make sure business users aren't negatively impacted, or if they are, only minimally so? You're now talking about a lot more complexity, a lot more planning, and a lot more scenarios that need to be evaluated. And that all takes time. And sometimes lots of it.
The other day a colleague and I were shadowing a vendor who was installing their 3rd party application on a hosted server. The vendor went ahead and created an alias for the application but initially created it with Named Pipes. Needless to say, the alias didn't work. Given that this application is hitting against a SQL Server 2005 installation, the Named Pipes protocol is disabled by default for remote connections. Only TCP is enabled. The vendor originally went back and configured the alias to use TCP, which did work. But this got me to thinking, "Why did the vendor choose Named Pipes?"
If we look in Books Online, we see the following quote:
Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.
This caused me to pause. Over the network I've seen issues with Named Pipes, such as unexpected disconnects that I've not seen with TCP/IP. I saw this last year, as a matter of fact, when an application would periodically disconnect from the database. The problem was that this application did not attempt to reconnect nor did it fail gracefully. It crashed out every time it lost the database connection. This application commited one of the fallacies of distributed computing: assuming the network is reliable. And this is on a modern switched LAN at typical LAN speeds.
So I'm not in agreement with the assessment given in Books Online. As a matter of fact, I would definitely disagree with respect to SQL Server 2005. In SQL Server 2005, only the TCP/IP protocol supports Kerberos authentication, which overall is a better security protocol than NTLM. Named Pipes has to use NTLM. Until SQL Server 2008, there is no option to use anything but. And even in SQL Server 2008, it means being aware of a potential additional SPN that's not needed for TCP/IP.
Which all leads to the question that's still nagging at me, "Why use Named Pipes? What's the benefit of doing so over the default of TCP/IP (or shared memory, for local connections)?"
When it comes to securing a system, it's important to understand how it might be attacked. That's what surface area is all about. The surface area is the parts of the system which are exposed. For instance, in the case of a default instance of SQL Server 2005 or 2008, we typically see two TCP ports open:
We also typically see the Shared Memory protocol enabled, meaning an attack is possible if you can get on the server itself. Since the DAC is certain limitations as to what can be run (but it also allows access to things that you can't get to with a normal connection), the connection using TCP/1433 or Shared Memory might be preferable. And in some cases we see Named Pipes on as well. If that's the case, this protocol can be used as well. All of this can be checked with the SQL Server Configuration Manager. And all of this is part of a SQL Server's surface area. If you're in charge of administering a SQL Server and you've never checked all of this out, there's no time like the present. And depending on what other features you have enabled, you might see more (such as a Service Broker or HTTP endpoint which exposes more TCP ports).
But what about once you establish a successful connection? What then? I've already mentioned Service Broker and HTTP endpoints, which can expose even more access to SQL Server. Within the context of SQL Server there is the ability to enable other features which increase the surface area. For instance, you can flag DAC so it's also available remotely. That means I don't have to compromise the server SQL Server is running on any longer to attempt to connect to the DAC. I can try and hit it remotely. You can allow certain features like OLE automation or access to xp_cmdshell, which are not normally accessible to any users, not even member of the sysadmin fixed server role (though they certainly have the capability of turning these on). It's important to know what features are on and which are off. In SQL Server 2005 there's SQL Server Surface Area Configuration (SAC) but in 2008 the tool is no longer present. Instead you've got to use Policy Management with the settings you care about. The advantage in 2008 is SQL Server can enforce these settings. SAC can read when you start it up and it can apply changes to settings, but it's not a continual monitoring type of tool. That's the reason 2008 uses Policy Management to maintain surface area configuration.
The point of all of this is to understand how an attacker might get into your system. the starting point is understanding the surface area: what's on and what's not. You need that to go to the next step, which is figuring out how an attacker might use an exposed interface or feature to attack said system. For instance, once I've determined that SQL Server is up on TCP 1433, how might the attacker go about using that? If there's a web server in the DMZ which connects to that SQL Server, then by compromising the web application I may be able to come in on that port. That's the attack. But since I know what's exposed and a potential attack, I can think of appropriate defenses. For instance, I can have IDS/IPS looking at the network traffic for certain key things where that web server talks to that SQL Server on TCP 1433. If I see xp_cmdshell, I know that's not right. And I can flag it and do something about it. But it all starts with me understanding the surface area.
Tomorrow night, May 28th, I'll be speaking the Augusta Developer's Guild. This is a make-up from earlier in the year when I got sick. Feeling just fine and looking forward to talking about SQL Server and security. If you're in the area, please come on by. I'd love to meet you!
Note: Since there have been several comments on this, I'm using parameterization at the application layer in the security sense of using the CreateParameter method. I'm not talking about parameterized queries with respect to execution plans or the specific use of sp_executesql. I thought I made that clear with me saying "proper parameterization at the application layer" but since there have been several comments on that, it must not be.
One of the main defenses touted against SQL injection attacks is to use proper parameterization at the application layer. But while this gets most of the cases, there are clearly examples where this alone fails. For instance, consider the stored procedure:
CREATE PROC dbo.usp_ExecuteSQL @SQL NVARCHAR(4000) AS BEGIN EXEC(@SQL); END;
Now I know the natural response by most folks is, "I would never see that in a production application." Perhaps not. But I have. In one organization's main application I found this procedure not once, but twice (albeit with different names). There was a standard in place that all database access from the application had to be done through stored procedures. The standard was met. But naturally because of such access, you can imagine what the permissions looked like within the database. And you can imagine the abuse that could have been performed through these types of stored procedures.
I know this is an extreme example, but it reflects that just focusing on parameterization isn't enough. For this stored procedure, the parameter would be defined properly, but that wouldn't stop SQL code from running in the back-end. Now I know the argument is, "Well, it's because the stored procedure used dynamic SQL." True enough. However, there are lots of cases out there where dynamic SQL solutions are running in production. For instance, I've seen things like the following:
USE [AdventureWorks2008]; GO CREATE PROC dbo.usp_ReturnPeople @Sort NVARCHAR(100) WITH EXECUTE AS SELF AS BEGIN DECLARE @SQL NVARCHAR(MAX); SET @SQL = 'SELECT Title, FirstName, MiddleName, LastName, Suffix FROM person.Person ORDER BY ' + @Sort; EXEC (@SQL); END
This stored procedure falls prey to situations like where @Sort can be sent in as '1;DELETE FROM person.Person;' which would effectively delete all the data in that table. And since @Sort would pass the parameterization check, using parameters doesn't serve as an effective control to prevent the SQL injection. Now I know there are techniques involving XML or using the CASE statement in the ORDER BY clause, but these aren't immediately obvious. As a result, it is possible to see an example like I've given above than those solutions. And yes, one could pare down the size of the parameter and that would help greatly, but when folks are trying to be flexible, these things aren't necessarily on their radar. Nor would a consideration be made to use EXECUTE AS with a defined user account that only has SELECT permissions against the table. And unless there is stringent code review, these types of things may slip into production and once they are there, they have to stay there until there is time to do another build.
Therefore, if we rely strictly on parameterization as our defense, we can still be beaten, even without someone working maliciously on the back-end code. Both examples I've given were written by well-meaning people who were trying to develop a reasonable solution. And the solutions they came up with solved their problems and they moved on. They didn't consider the security ramifications. Maybe they weren't aware enough to them to code defensively against them. Or maybe they were under a stringent deadline and were trying to get the job done. Whatever the situation, the vulnerability is there. So how do we guard against that sort of thing? Two things come to mind:
Now none of this is new. In fact, David Litchfield talked about second order SQL injection attacks at BlueHat and has published work on lateral SQL injection attacks in Oracle. The crux of all of this is to say there isn't one single vaccine to cure SQL injection. Parameterization is effective, yes, but a cure all it's not. There can be structures in the database that permit SQL injection attacks despite parameterization. And therefore, we must not stop at just using parameterization, but we must investigate further to ensure that we catch those vulnerabilities.
