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.
May 21
I will be teaching a half day course on SQL Server security and auditing for the Midlands Chapter of ISACA.This is a course intended for auditors and will be a technical deep dive on the SQL Server security features and how to audit activity properly. The purpose is to give IT auditors detailed information on what they need to look at within SQL Server so their audits are more efficient and less time consuming for both the auditors and us DBAs.
May 28
I will be speaking at the Augusta Developers Guild on Baked In SQL Server Security. I had been originally scheduled to speak in March, but was sick and had to cancel. I'm looking forward to getting out to the user group and meeting some folks in the Augusta area!
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.
The standard best practice answer when it comes to connecting to SQL Server is to use Windows authentication. However, SQL Server-based authentication exists, and there are some times when it's a best practice (or only practice) to use it. Wait a second... what do you mean by having two best practices? Well, as with most things in security and architecture, what is the best practice in a given situation depends on the situation. So let's look at two common reasons why you would use SQL Server authentication over Windows authentication.
Third Party Products
This one is a no-brainer. You've purchased a third party application and it only uses SQL Server-based authentication. If that third party application is important to your organization, you're now stuck between a rock and a hard place, right? After all, the recommendation is to use Windows authentication! Well, not really. The fact of the matter is you can't do it. The application doesn't support it. So you have to make do with what the application supports. Either that, or you've got to eject the application from your environment. That's not happening? Then SQL Server authentication it is. And that reminds me of a baseball analogy.
When I played Little League I was blessed with some really good coaches. They didn't just teach the skills, they taught how the game worked. Simple things which seem obvious to me as an adult I realize wasn't so obvious as a 10, 11, or 12 year-old kid. Like one time my team was down by more than 4 runs. The bases were loaded and I was going up to hit. Before I left the batting circle, my coach motioned to me so I could look at him. He said, "Make contact, get a hit. Don't overswing and end up striking out." I think I remember saying, "But if I get a homerun, we score 4 runs!" To which he said, "Even if you hit a homerun, we're still losing the game. It's more important to get a base hit. So keep your eye on the ball and get a good swing. Don't overswing!" And it clicked what he was saying. So I went up, put the ball in play and got a base hit, drove in a couple of runs, and didn't get an out. Unfortunately, we lost that game (we lost every game that season).
The same idea is true here. If it can't be done, it can't be done. The application doesn't support Windows authentication and you can't make it do so. You can request a future version support it, but until the vendor writes that into the application (if the vendor does so), you use SQL Server authentication. If it represents an exception to your environment, save the installation/configuration documentation. Do a short exception write-up about where in the documentation it says you have to set it up to use SQL Server authentication. If need be, get acknowledgment from the appropriate powers that be and save that evidence with those documents. And then move on.
Applications in Untrusted Domains or in the DMZ
Using Windows authentication assumes you have a connection coming from a trusted computer. The simplest example is when everything is in the same domain. There are cases where a computer may be coming from an untrusted domain or the computer isn't in a domain at all. In these cases, you can't use Windows authentication. The most common example is a computer in the DMZ, the area firewalled off from the Internet, but firewalled off from your internal network. Computers in the DMZ are usually accessible from the Internet.
For instance, a web server that is your company's presence on the Internet is likely in a DMZ. By rule, these computers are supposed to be off any internal domain. The reason is simple: if I compromise this DMZ computer from the Internet and it's on an internal domain, I can launch a direct attack against at least one of your internal domain controllers. And that's not good. By being on the domain it's got to be able to talk to domain controllers. And the way it'll talk to a domain controller is the same pathway I would use to attack that domain controller. Therefore, these computers are usually put in workgroups. Since they are in a workgroup, they can't use Windows authentication to connect to SQL Server. They must use SQL Server authentication.
What About Shared Services and Resource Pooling?
It's not unusual to have a case where multiple systems hit a particular resource and that resource makes a connection to the database server. A good example is a web service which multiple applications use. The web service may authenticate the individual incoming connections, but for the purposes of efficiency you want to make a connection back to the database server using a single account. This means the connection string can be the same every time and this means you can take advantage of resource pooling. A perfect candidate for SQL Server authentication, right? Well, not necessarily. If the web service runs on servers where you can establish that trusted connection to SQL Server, a better option is to use a domain user account, only make sure it doesn't belong to a particular user. A lot of folks call this a service account. A savvy domain administrator can even lock down what servers that account can run against, meaning if Joe Smith somehow got the username/password and tried to run it from his workstation, it would fail. In this case, Windows authentication is the best option. You still get resource pooling. You're not relying on a SQL Server-based login. And you can apply additional security options to the account to help make it harder to be abused.
