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.
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.
In a recent forum thread, someone asked to see who created/altered a particular object. Starting in SQL Server 2005, this information is contained in the default trace, provided that the default trace is enabled and the information hasn't rolled out of the trace files. The catch is how to report on the information easily. If you've used it before, you're likely thinking of the Schema Changes History report. The catch is that if you're using SQL Server Management Studio from SQL Server 2005, this functionality wasn't available until Service Pack 2. So if you have just installed the workstation tools and you've never applied a service pack, you won't see the option for the reports because it's not there. The same is true if you have a full SQL Server installation that's still RTM or SP1. The only way you see the change to SSMS is to apply SP2.
Which brings up a good point. You should be applying service packs to your client systems as well. This was a lesson we learned the hard way on SQL Server 7. It seemed like with every new SQL Server service pack, the definition of DTS packages changed. The only exception was if you encrypted your DTS packages with a password. That didn't change. And so what it meant was if you had different SP versions between your servers and your client systems creating said packages, you had a problem. Because ones with later service packs could read packages created with older service packs, but not vice versa. And as I'm writing this, Aaron Bertrand has just put out a blog post talking about an SSMS issue that is fixed with a hotfix, which you should take a look at, especially if you're dealing with mixed environments. So the point here is that in addition to keeping your SQL Servers up to date. you also need to plan on keeping your SQL Server client tools up-to-date as well. Not only are new features provided, but fixes are included with the updates, too.
Another SQL Quiz started by His Evilness, Chris Shaw. Okay, Chris isn't evil; I just wanted to say "evilness" and these quizzes are of value to the community. I was tagged by Jorge Segarra, better known as @SQLChicken. So here goes.
Do you feel that you have a reliable SAN Solution? If so what is the secret?
Yes, I do. The secrets aren't so secret:
Like I said, nothing in any of that is rocket science.
Explain Database Mirroring in layman’s terms
Let's say I've got two very well trained monkeys. They throw and catch pretty near perfect. However, they can only do one or the other at any given time, you just have to tell them which they are doing. If either monkey tries to both catch and throw, we're in for it. What do these monkeys toss around? These monkeys get bits of information in, process it, and then toss it over to the other monkey. And they do this blazingly fast. So at any given time both monkeys should have the same information. In asynchronous mode, that's not guaranteed. The monkey doing the tossing could take a break, go chase a banana, or what have you after he processes some information and forgets to make the throw. Then again, the receiving monkey could be on break himself and not catch a bit of information heading his way. You're in this for speed, not accuracy. But speaking of that, you're only allowed to talk to one monkey. That's one making the throws. You are providing him the info, and then he processes it and tosses it over to the other monkey. Should you try and talk to the other monkey, he's going to ignore you. Now, if the monkey you're talking to decides to wander off, as monkeys are known to do, you've got to go over and specifically tell the receiving monkey that he's up. He'll listen to those words, because he wants to be the thrower. And once he does, you can start talking to him and passing him info.
Now let's say you want to ensure that both monkeys have the same information all the time. This is synchronous mode and now we need another monkey. This monkey we'll call the witness, but he's effectively the ref because he's wearing the zebra shirt. He's carefully watching all the information coming in to the first monkey and ensuring that after it gets processed, that it gets thrown over to the second monkey and processed there, too. Should any of that fail, the witness is going to blow his whistle and call a foul. The first monkey must then take out that bit of processed information. Either both monkeys process it or no monkeys do. As you might expect, this is a bit slower than the other mode. But that's required for data consistency. Oh yeah, the witness can do one other thing if you want. If the witness notices the first monkey keel over, say it was trying to keep up with SQL Rockstar in viewing all 24 hours of PASS, then the ref, er, witness, can call a foul and tell the receiving monkey to start talking. In this scenario, the second monkey can start handling your data and will. However, it sets aside the information in hopes that the first monkey comes back on-line. If the first monkey stays down for the count and the second monkey keels over, too, then you potentially have some of your information lost.
Tagging:
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)?"
I was playing around with the endpoint catalog views this afternoon just looking at ways to do poor man's configuration collection on SQL Server and the options avaliable. The endpoints naturally represent the way in to SQL Server and since TCP is the default network protocol for SQL Server 2005 and 2008, I was looking specifically at sys.tcp_endpoints. Basically, I was looking to execute the following:
SELECT [name] , [state_desc] , [port] , [is_dynamic_port] , [ip_address] FROM sys.[tcp_endpoints];
This query would seem to return a lot of useful information. It returns the name of the TCP endpoint, whether or not it's active, what IP address and port it is listening on, as well as whether or not that port was configured dynamically. However, what I got back was 0 for port, 1 for is_dynamic_port, and NULL for ip_address for several servers I hit. basically, these are the three columns in sys.tcp_endpoints that are not in the catalog view sys.endpoints. They represent TCP specific configuration information. But with one glance it's obvious that the information being returned isn't useable. So I went back to Books Online and checked, and sure enough, there was this proviso:
The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.
So if you're looking at trying to extract the IP address and port SQL Server is listening on from this catalog view, don't. It can't be relied upon. The guidance from Books Online says to use SQL Server Configuration Manager and that certainly works, but it's a GUI tool, and not useful for retrieving the information using an automated process. All of the information on TCP endpoints is in the registry, it's just a matter of parsing the information out. So I'll be looking at writing a quick script that does just that.
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.
Log File Sizes:
It's not unusual to see cases where database backups are taken from production and restored to a development or QA environment. Often times the database in production requires point-in-time recovery, meaning we set it up to use full recovery mode. However, when it goes to non-production, there is no need for that level of recovery. There are also cases when we create a new database and we don't check its recovery mode. By default, the database is going to be created with full recovery. In both of those cases we can see the transaction log grow very large. Sometimes, we can even see cases where the transaction log is larger than the size of the database files. We can see the size of the database files by the following simple query:
SELECT NAME, type_desc, (size / 128) AS [Size_in_MB] FROM sys.database_files;
The size column is the number of 8-KB pages allocated to the file. So in order to quickly determine the value in MB, we can multiply by 8 to get the KB and then divide by 1024 to get MB. That's the same as dividing by 128, hence the calculation on that column.
When running this against a particular sample database I created, here were the results.
With the data file size only being 20 MB and the log file size being 1024 MB (or 1 GB), we have an issue. Likely the database is in full recovery mode. Since it's non-production, it likely only needs to be in simple recovery mode. We can see what databases are not in simple recovery mode by executing:
SELECT [name], recovery_model_desc FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE';
Once the databases are known, they can be changed by using the ALTER DATABASE statement with the RECOVERY option. For instance, for the sample database I cited above, I would issue the following statement:
ALTER DATABASE SampleDB SET RECOVERY SIMPLE;
Once that's done, the next step would be to shrink the files to an appropriate size. The command DBCC SHRINKFILE() can be used. For instance, to attempt to shrink the log file down to 5 MB, I would issue:
DBCC SHRINKFILE('SampleDB_log', 5);
The entire space may not be reclaimed. For instance, after running this command, the log file shrunk to 255 MB. That's not the 5MB I wanted, but it represents recovering about 3/4 of the space used,
Database Ownership:
When a database is created or restored, unless otherwise specified, the owning login for the database is whoever performed the operation. At my organization we prefer to ensure databases are not owned by a login that corresponds to an actual person. The reason being is that if that person is no longer a DBA but still has the ability to log on to the database, then the user will be able to implicitly do anything he or she wants within the database. That's because SQL Server automatically maps the login who owns the database to the dbo user within the database. That user bypasses all security checks within the database. To prevent against this, we periodically check database ownership and re-assign accordingly. To determine who owns each database, a query against sys.databases is all that is necessary:
SELECT NAME, SUSER_SNAME(owner_sid) [Owner] FROM sys.databases;
Once you determine which databases are owned by actual people, changing the ownership is simple. In SQL Server 2005/2008, you can use the ALTER AUTHORIZATION command. For instance, to change the ownership of SampleDB to be owned by sa, I'd execute the following:
ALTER AUTHORIZATION ON DATABASE::SampleDB TO sa;
And if I run the query again, I'll see that ownership has changed.
When it comes to logins to SQL Server, there are basically 3 types:
The latter two we have limited control. We can grant them the ability to connect to SQL Server and we can then make them users within the databases. But with respect to passwords and password expiration/strength, there's nothing we can do within SQL Server. Those accounts (or the accounts which are members of the group, in the case of a Windows group login) are controlled either by the operating system (an account local to the computer) or by Active Directory (domain account). So other than auditing for their existence on a SQL Server and their fixed server role memberships, there's not a whole lot more we do with respect to them at the server level. SQL Server-based logins are a different story.
With SQL Server-based logins, we can (as of SQL Server 2005) control whether or not they adhere to the password policies set at the operating system level (and which the computer may receive from the domain via a group policy). We can choose whether or not password complexity and lockout is enforced as well as whether or not password expiration is checked. In SQL Server 2005 and above, the key to audit this information is to use the catalog view sys.sql_logins. The reason for using this over sys.server_principals is that it only returns information on the SQL Server-based logins and it returns all the information we would want to check related to a SQL Server-based login. It also inherits all the columns from sys.server_principals, meaning we don't have to reference that catalog view as well.
Here are several simple queries that we can use to audit our SQL Server environment:
SQL Server Logins which are currently disabled:
SELECT name FROM [sys].[sql_logins] WHERE [is_disabled] = 1;
SQL Server Logins which do not adhere to the password policy:
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0;
SQL Server Logins which do not adhere to password expiration:
A note about this one. Password expiration is only checked if the password policy is enforced. If the password policy is not enforced, then SQL Server won't check for password expiration, meaning that SQL Server login effectively has a password that never expires. Therefore, we need to check both when there is no password policy enforcement as well as when there is password policy enforcement, but no password expiration enforcement.
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0 OR ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);
One of the videos I did for JumpStart TV is up on the front page:
SQL Server Authentication Modes
It is an introductory video to help understand the two types of authentication SQL Server can perform: Windows authentication only and Mixed Mode. It's primary purpose was to cover the two modes and how to switch SQL Server back and forth between the two. However, I did cover a bit about the SQL Server 2005 attack surface.
It turns out that in SQL Server 2005, you can use an SQL Server login connection attempt to determine whether or not the SQL Server is configured for Windows authentication only or for Mixed Mode. Pick a SQL Server login you don't believe will be a defined login for that SQL Server. Then attempt to login with it. I used TestAuthenticationMode. If the SQL Server is configured for Mixed Mode, you'll get the following error message back:
But if it's configured for Windows authentication only, you'll get a different error message:
Fortunately, this does not work for SQL Server 2008. In both cases, you get the error message indicating the login failed.
