Breaking Analysis Services 2008 with Illegal Characters

Reposted from Andy Hogg's blog with the author's permission.

The other day, I discovered an interesting way to break Analysis Services 2008.

To recreate this effect you’ll need an Analysis Services database – one of the Adventureworks samples will do just fine.

A word of warning, we’re going to be breaking something here. Although we’ll fix it again afterwards, you shouldn’t be following this example on an instance that anyone else is using – unless you want to make yourself really unpopular.

Start by backing up any SSAS database, you may use the GUI or an XMLA query to do this:-

Now restore the backup that you just took, but to a different database name. Let’s pretend that in 3 days from now we’re asked to restore this backup to allow analysts to run queries on a cube as of 3 days ago.

Not unreasonably we’re asked to restore the database without overwriting the existing one, so we elect to include the date within the name of our restored database and call it Adventureworks_28.10.2012

We execute the following XMLA query to accomplish this:-

Analysis Services duly processes the restore for us, and a quick refresh of Object Explorer confirms that all is as expected.

None of this seems too unreasonable does it?……Well, we’ve just horrifically broken Analysis Services.

What am I talking about? Well, try processing the Adventureworks cube within the newly restored database. You’ll see the following error message:-

In fact try opening up another (known good) SSAS database on that same instance and processing one of its cubes. You’ll see exactly the same error message.

Try browsing a cube – any cube on the instance. Same error message.

Try viewing the properties of the Analysis Services instance itself. Same error message.

The reason of course is that we included periods (dots) in the SSAS database name that we restored and Analysis Services clearly doesn’t like it one little bit. The strange thing is though, if you try to restore a database with a name comprised of some of these other illegal characters, Analysis Services will actually refuse point-blank to do this for you in the first place.

You won’t actually be able to check this for the moment though, because trying to restore a database on this instance will result in….. you’ve guessed it, exactly the same error message again.

It’s not possible to delete the database, and we can’t even use Object Explorer to rename the database to something legal – attempting either of these things will result in ……well, ever seen that movie “Groundhog Day”?

It’s fair to say that Analysis Services is not a happy camper, so let’s see what we can do to remedy the situation.

First off, we need to stop the Analysis Services service for the instance in question. I suggest you don’t stop the service via Management Studio because although this will work, you won’t be able to start it again via the same route. Instead I suggest using the Services applet on the server itself, or the NET STOP command from the command line.

Once you have stopped the service, navigate to the directory where your Analysis Services data files are stored. You’ll find a series of files with the extension .db.xml and there will be one for each SSAS database on the instance. Each file will have a name corresponding to a database on the SSAS instance.

Locate the file corresponding to the badly named SSAS database (Adventureworks_28.10.2012.92.db.xml in our case) and delete it:-

You may also wish to delete the subdirectory of the same name (Adventureworks_28.10.2012.92.db) – although this is not essential to fixing the problem, the files in this directory are now effectively orphaned.

Having done this, restart the Analysis Services service using your method of choice. You’ll find the badly named database gone, and Analysis Services once again fully working.

Out of interest you might like to try restoring a database using some different illegal characters, for example Adventureworks_!ӣ$%^&*

What you’ll see is exactly what we might have hoped for Analysis Services to do previously – i.e. stop us doing something stupid:-

Analysis Services 2012 is smarter though.

If we try to restore a database named Adventureworks_28.10.2012 on an Analysis Services 2012 instance we see that it stands for none of our silly nonsense:-

 

---

Andy Hogg is an independent consultant specialising in Microsoft SQL Server and other associated technologies.He holds a MSc. in Business Intelligence which he gained with distinction from the University of Dundee. His blog can be found at andyhogg.wordpress.com/ .

---