Category Archives: SQL Settings

Let’s talk about xp_cmdshell

Leave a reply

It’s happened again, xp_cmdshell being enabled is flagged as a security vulnerability. The recommendation is to turn it off. I disagree, and here’s why…

First of all let’s be open about the fact that I have a stake in this game. Our monitoring toolset uses xp_cmdshell for a few things. It shells out from SQL and checks the block sizes on the disks, whether power saving is enabled on the server and so forth. It’s extremely useful functionality which would be a pain to replace. But I do find I have to keep answering this question, and often asked by people who should know better.

Let’s also be clear on a few important points:

  1. XP_CMDSHELL configuration is off by default.
  2. It can be turned on and off by a SQL sysadmin
  3. By default, if turned on it can ONLY be used by a SQL sysadmin.
  4. If you want a non-sysadmin to use it you can enable that right. You then need to set up a proxy windows account for it to execute under.
  5. If no proxy is in place a non-sysadmin can’t use it, and a sysadmin using it will generate a windows process in the context of the SQL Service account.

If you put all that together you will see that only a sysadmin can use xp_cmdshell unless you explicitly configure access for another user, and even more importantly you can’t stop a sysadmin using it.

Therefore whether the setting is enabled or not is irrelevant. The only person who can use it has the ability to turn it on anyway, and you can control the level of access to the operating system by correctly assigning least privilege to the SQL Service account or the proxy account.

Now…the question might rise about whether the functionality itself is a security vulnerability and I’m not going to argue that it’s not a possible attack vector. It provides a powerful integration between the SQL Server and the Operating system. But so does executing an agent job in CmdExec mode and I’ve never seen that flagged in a security audit.

Neither xp_cmdshell or cmdexec agent job functionality is something you can turn off in such a fashion a sysadmin can not turn it back on(although even typing that about 5 things to give a try occur to me) so I think the very best this ‘audit recommendation’ will ever achieve is protection against non-malicious ‘accidents’ by tired DBA’s. Disabling it will be no more than a minor inconvenience for a malicious user who has the rights to use it once enabled.

How To Get a Free Azure Database

2 Replies

I’ve been doing some testing on Azure database over the past few weeks. Obviously when you move to databases being used as a service you need to pay to get this service, but I did find a nice little workaround that unlocks the “free tier” for SQL Database.

The process is outlined below, but the TLDR version is – create a “WebApp + SQL” in the free tier and the database becomes free. There are some downsides to this of course – the free tier is limited to 5DTU and 32MB size, and it vanishes after a year. But for playing around those values should be fine – and if you need more than that it gives you a good marker for how much Azure Database will cost you, because don’t forget what you are paying based on in Azure DB.

To get your free Azure Database Click on “Create a Resource” and search for “WebApp + SQL”. Select it then click create and you’ll see something like this:

The elusive “Free” tier.

The free tier can be selected from within the Dev\Test toolset:

Free is good.

Then when you select the information for your database the super secret free Azure Database Tier has been unlocked.

Oh look…a secret thing.

Please remember the limits on that free database:

It expires after 365 days

It has only 5DTU

It cannot be larger than 32MB.

Easily enough to run my new CRM on…

SQLSAT582: Resolving the “WinRM firewall exception will not work since one of the network connection types on this machine is set to Public” Error.

1 Reply

At SQL Saturday Melbourne, an otherwise excellent event, my demo failed on me because the DBAtools module failed to load with the following error:

Set-WSManQuickConfig : <f:WSManFault xmlns:f=”http://schemas.microsoft.com/wbem/wsman/1/wsmanfault”
Code=”2150859113″ Machine=”localhost”><f:Message><f:ProviderFault provider=”Config provider”
path=”%systemroot%\system32\WsmSvc.dll”><f:WSManFault
xmlns:f=”http://schemas.microsoft.com/wbem/wsman/1/wsmanfault” Code=”2150859113″
Machine=”<machinename>”><f:Message>WinRM firewall exception will not work since one of the
network connection types on this machine is set to Public. Change the network connection type to either Domain
or Private and try again. </f:Message></f:WSManFault></f:ProviderFault></f:Message></f:WSManFault>
At line:116 char:17
+ Set-WSManQuickConfig -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Set-WSManQuickConfig], InvalidOperationException
+ FullyQualifiedErrorId : WsManError,Microsoft.WSMan.Management.SetWSManQuickConfigCommand

For a piece of code that had run successfully a dozen times this was of course a VERY annoying error, not least because I set it to run in another window and carried on with my talk, so didn’t really have time to either troubleshoot it or re-run it once I was back.  I had initially believed the problem was due to an earlier deviation I had made from the order I was going to do things and therefore related to execution policy.  But I hadn’t even read the error message properly during the demo because I just wanted to curl up in a corner and cry.

But running it again later I remembered the other change that I had made to my laptop from every other time I had run the code.  I had adjusted my network settings from a static IP address to allow me to connect to the university wireless.  That was a dumb rookie mistake, and I really had no excuse having not re-run my code in the new environment.  My session was right after lunch, so I had all lunchtime to step through my code, and had done so with everything but the particular script that failed.  Lesson learnt the hard way I guess.  Speakers be warned.

Anyway, the error is obviously related to the change in wireless settings.  I was quickly pointed in the correct direction by this post from Matt Wrock who provides the powershell commands needed to set all your network connections to private:

$networkListManager = [Activator]::CreateInstance([Type]::GetTypeFromCLSID([Guid]"{DCB00C01-570F-4A9B-8D69-199FDBA5723B}")) 
$connections = $networkListManager.GetNetworkConnections()
# Set network location to Private for all networks 
$connections | % {$_.GetNetwork().SetCategory(1)}

As the article referenced above explain the implications and manual process better than I could I won’t add additional junk to the internet, except to say that the failure of the import of the DBA tools module appears to be due to a failure of Set-WSManQuickConfig  in the PS-Remoting module.  Also not that making your networks private actually has less security associated with it than having them “Public” which is a bit counter intuitive.  A “Public” network setting means you are on a public network, don’t trust it so security is high, whereas a private network means you trust the network and are prepared to let traffic do a few extra things – like ps-remoting for instance….ahem.

This is the first session which has been so heavily reliant on demo code, normally I do a lot of speaking from a slide deck.  The turnout was really good, but I felt my presentation in general was pretty poor.  It’s much easier to deviate from what you planned when you are talking from a slide deck, because the slide deck brings you back to the intended presentation.  When you are just typing code into a powershell console and someone asks you a question that takes you away on a tangent it’s harder to come back on topic.  In general I think the presentation went a little bit all over the place, which is exactly what people don’t need when they are trying to grasp the fundamentals of using a programming language.  The structure I had in place was good, but my execution on the day was poor.  So apologies to the audience on the day, but hopefully the core message of the usefullness of powershell still got through.  You can run through my demo scripts by downloading them from the SQL Saturday site at:  http://www.sqlsaturday.com/582/Sessions/Schedule.aspx

 

 

Optober – SQL sp_configure options – Allow Updates

Leave a reply

Option Name:  allow updates

Usage:  sp_configure ‘allow updates’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  Once upon a time if you wanted to update system tables in your database you could do so, and this was the magic switch you needed to throw to do it.  In the current enlightened times people realize that updating system tables is generally speaking not a good idea, and if you really need to do it to (for example) fix an allocation error corruption, there is now emergency mode repair which can be used.

When should I use it:  Never after SQL 2000, and even in SQL 2000 only if you are very clear on what you are doing.  Books Online is pretty clear on this.  If you’ve updated system tables then your database is not in a supported state.  This feature currently has no effect and will be removed in a future version of SQL server.

What else should you know:  There are other ways to update system tables if there is corruption within your database.  But you need to be aware that if you change an underlying system table the date of the last change is logged in the header of the database.  It will be there for everyone who cares to look(ie Microsoft Support) to see.  I’m not going to go into details on that accept to point you over to Paul Randal’s blogs on database corruption.  Though if you are looking at this stuff god knows how you would have found my blog before his.

Optober – SQL sp_configure Options: Show Advanced Options

Leave a reply

Option Name:  Show Advanced Options

Usage:  sp_configure ‘show advanced options’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  If you have just installed SQL Server and run sp_configure you are going to get 18 options.  If you enable(Set to 1) the ‘show advanced options’ option you get access to all 70 options.  What’s an advanced option?  To be honest the list looks pretty arbitrary to me.  Things like ‘backup compression default’ and ‘remote admin connection’ are fine as ‘simple’ options, but why would ‘clr enabled’ or ‘nested triggers be considered simple?  You can do some serious damage with just the simple options.

The only official word I can find on it describes the advanced options as ‘Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician’.  There’s a few advanced that I feel could probably be simple, and a few simple that could probably be advanced.  As we progress through the month I’ll talk about each of these and the damage(or not) they could do.

SQL Saturday Christchurch is back

Leave a reply

Last years SQL Saturday was a lot of fun, and this year we are at it again with SQL Saturday returning on June 14th. I guess we’re established now because we were quite easily able to attract some amazing speakers.

I’ve done 3 sessions for the user group since the last SQL Saturday, so the local users will be pleased to hear from someone else and I’ll take a break from presenting this year to allow our guest cast of MVP’s and sponsors the focus. Thanks to Microsoft, Varigence, Kelly IT and Pluralsight for their generous sponsorship.