All I want to do is draw a simple diagram with Visio.  But with Visio, nothing's ever easy.  Do the connectors attach to the objects the way I want them to?  No, they do not.  Do they snap to objects when I don't want them to?  Yes, they do.  Inexplicably, Visio's "Detailed Network Diagram" template doesn't contain pictures of any switches or routers.

Seriously.  What the fuck.


Finding the biggest tables in a database

Shamelessly lifted from, with thanks.  😀


SQL injection attacks = fun!

My last few posts about SQL have been about how to exploit a SQL box within the confines of a company network.  I wrote about xp_cmdshell and how it can be combined with a poorly-configured SQL box to produce some very unpleasant results.  This week, someone at work (an apps developer, of course), wanted me to publish a SQL box to the internet.  My answer was an emphatic NO, given this particular app person's history for writing crap code.  This person wanted our network guys to poke a hole through the firewall, sending TCP 1433 to the SQL box.  Talk about a disaster waiting to happen.

Long-time followers of my blog know that I have a Masters degree in Networking and Systems Administration.  One of the subjects of that degree was supposedly about Linux, but actually turned out to be more about security.  At the time I was running an e-commerce site, so I had an interest in securing that site.  Learning about my own site, the server it ran on, the applications that ran on it etc was very enlightening.  I was able to see through the web server logs that my server was being hit 24/7 by automated attack scripts.

The scripts weren't really all that interesting; mostly they were remote file inclusion probes.  Some contained URLs to files on servers that had already been exploited.  I downloaded these files and most of them were pretty stock-standard footprinting scripts.  The sort of thing that details what OS the compromised system is running, what version of its components (eg SSH, which at the time had a pretty nasty vulnerability) etc.  One file contained a huge script that created an IRC bot network.  It was actually fascinating.

But that's not what this post is about.  All of this study and research got me onto SQL injection vulnerabilities.  At the time, there was a poorly-coded osCommerce module called something like Customer Testimonials.  It did not sanitise its inputs, so a properly-crafted URL could reveal information contained within osCommerce's tables…. including credit card details, if the site operator was silly enough to save them (BTW, this is a fundamental breach of PCI standards, which I'll not go into, but is worth learning about if you're interested in e-commerce).  I set up a test VM running osCommerce plus the vulnerable version of the testimonials plugin, and sure enough, I was able to exploit it to view data from osC's tables.

Remote File Inclusion attacks are pretty easy to defend against.  Tell the web server (or relevant module eg PHP) not to run content from URLs or remote file systems.  Defending against SQL Injection attacks can be more complicated.  At its worst, it means checking hundreds of lines of code for silly things like (pseudo-code): 


There are server-level defences to filter out SQL injection attacks, which I won't go into here, but the reality is that bad code will always be bad code.  And bad coders often don't know their code is bad until long after it's in the wild.  With that in mind, and bearing in mind some of the xp_cmdshell exploits I demonstrated in my previous posts, have a wander over to this page.  What I really like about some of the stuff demonstrated here is that one of the examples (example 8) leverages bad SQL security to inject a file, which in turn can be run to perform some malicious task.. or even better, run as part of an IRC botnet.  The possibilities really are endless.  Click some of the links at the bottom of that page to read some very interesting articles on SQL injection attacks.

Bad configuration within a corporate network is one thing.  Sure, it sucks, but it can usually be contained (perhaps by design of other systems such as firewalls, content filters etc).  An internet-facing server, once compromised, can be used to do some pretty bad stuff.  Anyone who thinks publishing a SQL box to the internet consists of only poking a firewall hole on port 1433 needs to rethink their approach to system security.  Actually, anyone who thinks whacking a firewall in front of an internet-facing server will magically protect it.. think again.

What I find really interesting about SQL injection attacks is that they are a multi-layered thing.  They rely on poorly-configured web servers, crappy code, badly-configured SQL boxes etc.  But what's really cool is that the SQL boxes themselves don't need to be presented to the internet.  The only requirement is that a the web server (or more generically, the application server) have connectivity to the SQL box.  Hiding those SQL boxes on a private subnet won't help.  Hiding them behind an internal firewall won't help.  Hiding them on protected VLANs won't help.  It won't matter.  Because once the web server manages to talk to the SQL box, once the web-server-generated query traverses the wire from web box to SQL box, the scope for damage is already present.

What I'm saying, folks, is that security is a multi-layered thing.  I don't claim to have all the answers.  I'm not a security expert.  But I am security-aware.  All good sysadmins ask questions about the code that's running on their boxes.  What does this code do?  Why does it do it that way?  Can it be done in a more secure fashion?  What does it really need access to?  Can the code be run isolated?  Do we have sufficient logging to keep track of breaches?  If this box is compromised, what else can it access?  If there is a breach, what is the scope of information disclosure?  There's so many questions to ask.  Don't be afraid to ask them.  If your apps people can't tell you this stuff off the top of their heads, stamp a great big red DENIED across the request.


SQL 2008 schema explanation

I've been struggling to get my head around schemas in SQL 2008.  BOL has this incredibly useful piece of wisdom to impart:  "Database schemas act as namespaces or containers for objects, such as tables, views, procedures, and functions, that can be found in the sys.objects catalog view".  Erm.  OK.

That actually didn't help me much.  What do they do?  What are they for?  Why not just apply permissions directly?

Finally, I read this article.  I know Googling will provide lots of hits on "SQL 2008 schema", and all of them say much the same as this article, but for some reason, this article made it all click.  Schemas are roughly analagous to folders in a file system.  An individual file can only live within a single folder at any given time.  A folder can contain lots of files.  In order to apply permissions within the file system, one doesn't waste one's time by applying permissions against each and every file within a folder.  Permissions are applied at the folder level, and the files within the folder inherit those permissions.  That's kind of how SQL schemas work.  I think.

I'm still getting it all straight.  But I think this article does a good job of putting it together.  Unless I've got it all wrong.  In which case, it's a terrible article.

Joy from simple things

Two things have happened recently that make me giddy with joy.


  1. I installed the latest beta nVidia driver, and whaddya know, the crashing has stopped.  Thankyou, whichever deity is responsible for this.


  1. I've had a longstanding problem with iTunes not working or playing well with my G15 keyboard.  Every once in a while, a new release of iTunes comes out and it's fixed.. until the next update.  The last two updates have been consistently bad.  The short story is that unless iTunes is in the foreground, pressing "Play/Pause" on the keyboard will pause it for a second, then resume playing.  Very fucking useful.  I mean, if I need to bring iTunes to the foreground for the button to work, it defeats the whole fucking purpose of the button.  I've Googled about this problem in the past, but have never found a satisfactory solution until now.  This post describes a very simple fix.  And damn, it works! 




xp_cmdshell + elevated accounts = A new kind of evil

After my previous two posts about xp_cmdshell exploits, a friend asked this question:

"Same issue if the SQL service account is a domain admin (rather than Local system)?"

My immediate answer to this is, yes – the Domain Admins group is a member of the local Administrators group on every member machine by default.  So it stands to reason they have the keys to the city.  What is interesting about this scenario, however, is not just the risk associated with compromising the local machine.  It's the risk associated with being able to compromise every other member of the domain – including Domain Controllers.

If this doesn't give you a reason to check your SQL Server's service accounts, nothing will.  Consider this scenario, one which I consider to be a near-perfect storm of stupidity:

  1. The SQL Server service account is configured to use an account that is a member of the Domain Admins group.
  2. The SQL Proxy account is configured with an account that is a member of Domain Admins – perhaps the same account as the SQL Server service account
  3. xp_cmdshell is enabled.

You'll recall that xp_cmdshell lets you run pretty much anything under the context of the SQL Server service account (if you are a sysadmin) or the context of the SQL Proxy account (if you're not in the sysadmin role).  As you saw in my last couple demos, this seems to be limited to commands that run non-interactively.  But that doesn't mean there's any less risk associated with it.

Let's first of all try this as someone who's a member of the Domain Admins group.

Let's see if we can use DSRM to trash an OU in Active Directory.

Those of you familiar with Windows Server 2008 R2 will no doubt be pleased that objects created in AD have this option selected by default:


If you're using an ealier revision of Windows for your DCs, or if you have not yet moved up to the appropriate Functional Level, this option won't be available to you.  Let's see what happens if you do not have this option enabled:


It should not really be a surprise that someone who is a Domain Admin, running TSQL commands on a box that is poorly-configured, can do this.  I wonder about a regular user?

Don't forget that for that to happen, we need to create a SQL login associated with that user, the user needs to be granted permission to run xp_cmdshell, and we need to configure the SQL Proxy account.  And because we're talking about a very silly administrator, the Proxy account is configured to use an account that is a member of Domain Admins.

Now when the user runs the whoami command, they see this:


Yay!  A regular unprivileged account can now run commands under the context of the Domain's administrator!  Great news!

And just to prove it…

Oh, fuck.

So, your boss is in your ear about how all the people in the Marketing department have lost their user accounts – and all the things they want to access with those accounts.  Your boss wants to know who did this!  Let's look at the Domain Controller logs:


The event logs are clear.  It's not the unprivileged account that deleted the user accounts.  It was the Administrator account.  Sure, the logs will tell you.. with a bit of hunting.. that the Administrator account was logged onto the SQL box, but by then, it won't really matter.  The finger will still be pointing at the person who's supposed to be safeguaring the Administrator account.  And that's not Mr Unprivileged.  No.  It's a naive sysadmin who should've known better than to configure his SQL box to use elevated accounts.

This could've easily been prevented.  Even if the SQL Server service account was privileged (which it should not be, but for the purposes of this discussion….), using an unprivileged account for the SQL Proxy account would've prevented it:



The regular user account can't access \\dc1\c$\windows\system32.  A good thing, too.  But what if this person is really determined, and makes a copy of dsrm (and its supporting files) somewhere else?


In spite of the poorly-configured service account, this particular disaster was averted because the Proxy account is unprivileged.

Windows, SQL Server, and to an extent, the tools that come with them, prevent us from doing overtly stupid things – so long as we follow a few basic rules.  If we configure our servers loosely, against best practice, ignoring all conventional advice… then we really do deserve what we get.  In the words of the great Forrest Gump, stupid is as stupid does.

Crayon Syntax highlighter – code highlighting that almost works!

I have spent the last two days trying to find a code highlighter that actually works with CKEditor and WordPress.  I've found Crayon Syntax, and it almost works the way I want.  And frankly, that's close enough.

To make it work,

  1. Install it (duh)
  2. Configure it to use its "fallback" (ie default) language as PgSQL
  3. Whenever you want to highlight some code, select it and mark it as "Formatted"

Behold how your SQL code is marvellously highlighted, eg:

I say it "almost" works because it does break when the SQL code includes characters that aren't strictly SQL.  For example, this command:

"exec xp_cmdshell "echo USE [master] > c:\sqlcommands.txt""

gets mangled because of the greater than sign, into:

But I've decided I can live with that.

xp_cmdshell: Some more evil

In my last post, I detailed some conditions under which a poorly-configured SQL server could allow unprivileged users to do bad, bad things.  Today I want to expand on that to show how an unprivileged user can create a new user account that is a member of the SQL sysadmins role.

To recap the conditions under which bad things can happen:

  1. The SQL service account is set as Local System
  2. The SQL proxy account is a privileged account, i.e. one that has Administrator rights over the local machine
  3. xp_cmdshell is enabled under these conditions.


So that allows a regular unprivileged user to run xp_cmdshell, and in turn, to run commands under the context of the SQL proxy account, which has elevated rights over the operating system.

Let's say you have an end user who's more savvy than most.  The naive SQL administrator has created a SQL login for his windows account, and has enabled use of xp_cmdshell for that account.  The end user quickly checks the SQL proxy account:

And confirms what he always suspected about the developers at his workplace.

He tries to create a SQL login with membership in the sysadmin role:

Thankfully this doesn't work:

Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.

Not to be disheartened, this malicious end user does know he has rights to run xp_cmdshell.  And he knows that the commands will run under the context of the silly developer's account.  So let's try something else:

Two things.  I've had to encapsulate the commands in " characters so that SQL parses the commands properly.  Also, I've had to break one command (lines 3 and 4) into two lines because there's a string length limit.  But this can be easily overcome.

So we now have a text file which contains:


We can't just run this code directly at the command prompt.  But we can do something almost as good:

This runs the osql command with a "trusted connection" (-E), which basically says use the windows account (of the SQL proxy account) for authentication/access, and -i which grabs the contents of sqlcommands.txt and parses and runs it.  And -o outputs the results to a file called results.txt.  This is important because we want to see the results of the operation, which in this case happen to be:

Hmm.  OK.  On my test box, this happens to be so.  Silly Developer doesn't have a SQL login.  But we know that in a more realistic environment, this is likely not true.  The Silly Developer more than likely has a SQL login.  And let's, for the sake of this discussion, assume they are also a member of the sysadmin role.  Don't scoff.  I've seen plenty of production environments that are configured exactly like that.  So let's create a SQL login for Silly Developer and make them a member of the sysadmin role.  Let's try again:

This produces a file called results.txt, which contains:

That's a bit different.  Hey, let's see if the login was created.

It sure was.  And it's a member of sysadmin.  Oh dear.

It's now a trivial matter for this end user – who up until now had no special rights – to login using the new SQL login and to have fun making the naive DBA's life miserable.  DROP TABLE, anyone?  DROP DATABASE?  The possibilities are endless.

Let's try this again, this time using a properly-configured server, which you'll recall is:

  1. Configured to use an unprivileged account for its service account
  2. Using an unprivileged account for the SQL proxy account

Gives us:

Great.  Let's try creating that text file again:

Well, that's reassuring.  The account doesn't have rights to create a file on the root of C: on the SQL server.  But that's not to say it couldn't have rights over some other file system somewhere.  Let's say there's a file share somewhere that allows the Authenticated Users group to write to it.  Let's try this again:

I've had to split the lines again due to the length limitation, but the result is a file in \\FILESERVER\OPENSHARE that contains:

OK, let's try running that command:

The command runs successfully and creates a file \\FILESERVER\openshare\results.txt.  Thankfully this file's contents are:

What we should take away from this is that the proxy account really should not have a SQL login associated with it.  But let's pretend it does, and that it is not a member of any special SQL roles:

The results are:

I don't think I need to spend any time explaining why the SQL proxy account shouldn't a) have a SQL login associated with it; and b) If such an account exists, it should not be a member of any special SQL roles.

xp_cmdshell: Baby did a bad, bad thing

My syntax highlighter is a bit broken.  Until I fix it, the TSQL shown below will be full of crud.  Fix it before trying it on your system.  Or just wait until I fix up the highlighter.  Your choice, really.

Much has been written about why SQL’s xp_cmdshell is a bad thing.  It has its uses, of course, and sometimes we just have to enable it for our apps to work.

In my most recent post about SQL, you would’ve detected a certain.. disdain.. for applications developers.  It’s not their fault, though.  They’re just trying to write something that works.  In my workplace, there is a “just make it work” mentality, which can lead to some awful misconfigurations.  That’s why this post is dedicated to just how bad xp_cmdshell can be.

By default, xp_cmdshell is disabled, and with good reason.  If it’s not set up just so, it can be exploited.  The gist of it is this:  if you can run a command from a DOS shell, non-interactively, xp_cmdshell will run it, too.  So it stands to reason that you should lock xp_cmdshell down as much as possible.  But this is at odds with the “just make it work” mentality, and sometimes security is compromised as a result.

If you’ve set up your SQL server per best practice, your SQL Server service account is an unprivileged domain account.  That means it’s just a regular account, not a member of any special domain or local groups.  When you tell the SQL installer to use this account, it sets up file permissions, registry permissions and local policies so that your service account has all the things it needs to run SQL properly.  But by default, the SQL installer doesn’t prompt you for a domain account.  No.  Left to its own devices, it will let you choose from a drop-down:

<insert screenshot here that Crayon syntax highlighter doesn’t like 🙁 >



Interesting.  The local system account is here, as well as the option to browse.  Hey, Local System looks good.  So, for this demonstration, let’s assume the naive person installing SQL selects this account.  For those of you not in the know, the Local System account has the keys to the city over the local machine.  It has no rights on any other system.  But for this demo, that doesn’t matter.  Let’s then assume the “Use the same account for all SQL Server services” is clicked.

So we carry on installing, and voila, we have a running SQL instance.  Let’s say the apps developer asks for xp_cmdshell to be enabled.  That’s easy enough to do:


And we get a successful result!

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Let’s try a command:




Hey, this is great!  It WORKS!!!!!!!!!!!!!!!!!!!!!!

Let’s try another one:



Yay!  More working xp_cmdshell goodness.

We can even use it to write to a text file:

And the result is, funnily enough, a text file C:\FILE.TXT:

appended data
more data


Let’s try something a little more interesting.



We should all breathe a sigh of relief here.  The format command needs to be run interactively.  It wants you to answer yes or no.  For once, Microsoft’s efforts to save us from ourselves have been useful.

And thankfully, by default, diskpart also runs interactively:



Here’s where it can get interesting.  Diskpart runs interactively by default, but can also run in scripted mode.  Let’s jump to a DOS box for a minute to see how that works:



I’ve just deleted the contents of disk 1.  It didn’t take much effort, did it?  Let’s see if we can script that.

First, create a text file somewhere that contains this:

Now, let’s run diskpart scripted:




I wonder if we can do the same thing with xp_cmdshell?

First, let’s create the command file:

Then, let’s run diskpart scripted:



If you don’t think a misconfigured xp_cmdshell is a bad thing, try this on a production system.  See how long you stay employed.

Some notes on the demo above.  I ran these commands using an account that was a member of the Administrators group on the local machine.  That’s why I didn’t have to do anything else special in order for xp_cmdshell to work.

Let’s say I have an unprivileged user that I want to be able to run xp_cmdshell commands.  I need to give them access to the command:

Let’s try this again, this time running it under the context of a standard user account:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information.

SQL is thoughtful enough to protect us from ourselves.  But we want to JUST MAKE IT WORK!!!!!!!!!!!!!!

So, let’s do what every app developer I’ve ever met would do.  Let’s configure the proxy account using a privileged account – maybe even the app developer’s own account (which is, of course, a member of the Administrators group).  This can be done via the GUI or TSQL, but for this demo, I’ll show the TSQL:

Now when we run the whoami command, we get this result:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

OK, fine.  Let’s grant this unprivileged user the rights to use xp_cmdshell:

Let’s run the whoami command again:



Yay!  It works!  Great, huh?

Now, let’s try that diskpart stuff again:

You’ll recall those commands created the diskpart script file.  Let’s then run the script file:



Oh dear.

Those of you in the know might be thinking we could do “select disk 0” in our diskpart script and run it to trash the system volume.  Fortunately, diskpart protects us from ourselves:



If there’s a saving grace, it’s that the system volume can’t be trashed using this simple method.  But hey, we can still wreak havoc.  Let’s say we have a database called SuckedIn, whose files live in F: drive:



You know where I’m going with this, right?  If we don’t know the disk number, we can still use diskpart to identify the volume number, by changing the script to “list volume” to identify where F: drive lives:



Then we just change the diskpart script to:

And the result is data destruction:



Thankfully, we still can’t trash the system volume, but if someone trashed your database volumes, you’d be just as screwed.  Don’t let this happen to you!

In case you passed out from boredom while reading my post, here’s the important bits that led to this disaster:

  1. The SQL service account is set as Local System
  2. The SQL proxy account is a privileged account, i.e. one that has Administrator rights over the local machine
  3. xp_cmdshell is enabled under these conditions.

That is all it takes to allow an otherwise regular, unprivileged user to HOSE YOUR SQL BOX!  All of this could have been prevented by using best practice, which in case you fell asleep again, is very simple:

  1. Configure your SQL instance to use an unprivileged account for its service account
  2. For the love of all that’s holy, use an unprivileged account for the SQL proxy account
  3. Don’t enable xp_cmdshell unless you absolutely, positively have to.

To change the SQL Server service account, use the SQL Server Configuration Manager tool.  You’ll need to restart the SQL Server service, so schedule an outage window to do this.

Once you’ve done this, use this script to configure the SQL Proxy account.  Make sure this is an unprivileged account:

This change takes effect straight away.  No outage required.

Now when you run the whoami account as an administrator, you’ll get this result:



If you run it as an unprivileged user, you’ll get this result:



Now try running the diskpart script as the unprivileged user.  It won’t work.  YAY for system security!