Analysis Services 2008 R2 breaking change when deploying from the command line

As collegues of mine will attest, I will script anything that has to be deployed. Some things are easier than others.

In the case of Analysis Services, the .asdatabase file that comes out of the build needs to be futher transformed to create the XMLA that you need to run on the server to deploy your (updated) cube definition. Rather than attempt to replicate this transformation, I have previously chosen to get the Analysis Services deployment utility to do this for me, since this can supplied with command line arguments:

write-host "Generating XMLA"
$asDeploy = "$programfiles32\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe"
& $asDeploy "$pwd\..\bin\MyCube\MyCube.asdatabase" /d /o:"$pwd\MyCube.xmla"

Which works just nicely. Except when we migrated that project to SQL 2008 R2, when it stopped working.

Well, actually that's not true. We'd been deploying to a 2008 R2 server for ages, it was when we changed the deployment script to use the 2008 version of the deployment tool that it all broke.

Basically the next line in the script kept complaining that 'MyCube.xmla' didn't exist, but I'd look in the folder after the script had run and the file was there. So it seemed like maybe there was a race condition.

Which there was.

If you examine the PE headers for the Sql 2005 version of the deployment util (using a tool like dumpbin) you'll see it's marked as a command line application:

C:\>dumpbin /headers "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" | find /i "subsystem"
            4.00 subsystem version
               3 subsystem (Windows CUI)

...but the 2008 R2 version is marked as a gui application:

C:\>dumpbin /headers "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" | find /i "subsystem"
            4.00 subsystem version
               2 subsystem (Windows GUI)

What? Can't see the difference? One is marked CUI with a 'C', the other is GUI with a 'G'. An unfortunately high degree of visual similarity given what a fundamental difference it makes: launch the first from the command line and you wait for it, launch the second and you don't. When scripting it's pretty important to know which one you've got, or you're going to get race conditiions.

In this case the answer was to control the process launching, so we can explicitly decide to wait:

     start-process -FilePath:$asDeploy -ArgumentList:$asdatabase,"/d","/o:$xmla" -Wait;

Maybe I should just do that all the time to be safe, but just being able to use other command line tools within a script without a whole lot of ceremony is one of the really nice bits about powershell, so I tend not to. In this case the launch semantics of an existing utility changing between versions seems like a really nasty thing to be caught out by.
Good reference sources:
Stack Overflow: Can one executable be both a console and GUI app?
MSDN: A Tour of the Win32 Portable Executable File Format

Sql 2008, virtual accounts and a breaking security change from 2005

Interesting gotcha today regarding the different ways Sql 2005 and Sql 2008 grant permissions to the service account they are running under. Interesting, because the differences broke my app, and exposed my complete lack of understanding of a key Windows 2008 R2 security concept - virtual accounts.
In Sql 2005, to simplfy management of the service account's permissions against SQL itself (specifically with regard to changing which account SQL is running under) the product team started creating local Windows security groups, of the form:

computername\SQLServer2005MSSQLUser$computername$MSSQLSERVER

This group is configured by the installer to contain the service account (eg Network Service), and a corresponding SQL login is created (for the windows group) granting sysadmin rights:

I'm a big fan of running services as Network Service. Not having to create explicit service accounts means less admin overhead (both creation, and password expiry maintanance) and a lower overall attack footprint for your enterprise. But there is a downside - a lack of permissions isolation between services also running as Network Service on the same box. In this case, because of the above, anything else on that box that runs as Network Service is automatically sysadmin on your SQL instance.

In Sql 2008 on Windows 2008 R2 the situation is a bit different, because Windows 2008 R2 introduces so-called virtual accounts. I'm still a bit hazy on these, but one of the things this enables you to do is grant permissions to a service without knowing which account it's running under. The actual permissions the service has at runtime are then the union of permissions explicitly granted to the service account as well as the permissions granted to the service itself.
Which is cool. If a bit freaky at first.
So whilst Sql 2008 still has one of those local Windows groups created for it's service accounts, the contents of this are now, somewhat tautologically:

NT SERVICE\MSSQL$SQL2008

...and at the database level, the group is actually ignored, and the login (and SA grant) is given directly to itself, not the group:

(note: i've got SQL 2008 as a non-default instance, hence the specific naming. But you get the idea).

What does all this mean? Put simply (and somewhat recursively):

• Only the SQL 2008 service itself is setup as an adminstrator on the SQL 2008 service. The principal (service account) that runs it is not - by itself - an administrator on that instance.
• It is no longer the case that other applications running under the SQL Server service account are sysadmins on any SQL instances running under those same credentials.

It was the second bullet that broke my app. This is illustrative of poor original design, for sure, but giving Analysis Service carte blanche over the SQL instance on that same box seemed like a fairly safe call originally. But it exposed a really cool security improvement in Windows 2008 R2.

In this case the problem is the solution: I can just go an add a grant for the virtual service account for Analysis Services, give it enough SQL permissions to do what it needs and the problem goes away.

More on virtual accounts from the Sql 2012 doco, and from Technet articles Managed Service Accounts (MSAs) Versus Virtual Accounts in Windows Server 2008 R2 and What's New in Service Accounts

Gotchas with the Kinect SDK for Windows

Playing with the Kinect SDK for Windows, and having a ball, but the doco is (understandably) a bit rubbish in places, or to be more specific – lacks critical details around the form that a parameter takes, where that detail is important.

Anyway, this is my list of gotchas so far:

Depth Data Inverted when Player Index tracking enabled

Bizarrely, whether you initialize and open your depth image stream with ImageType.Depth or ImageType.DepthAndPlayerIndex makes the difference between whether what you get is ‘right way round’ or horizontally inverted.

Inverted is generally more useful, because it matches with the ‘mirror image’ video stream. So why isn’t the stream like that always? Seems like an unnecessary inconsistency to me, and one you might want to spell out in the doco.

Different Depth Data Pixel Values when Player Index Tracking Enabled

When you do turn player index tracking on, the depth stream ‘pixels’ are lshifted 3 positions, leaving the lower 3 bits for the player index. This is documented, and I understand you’ve got to put the player index somewhere, but why not make the format consistent in both cases, and just leave the lower bits zero if tracking not enabled? Better still, why not put the (optional) player index in the high bits?

This is especially irritating because...

GetColorPixelCoordinatesFromDepthPixel() Requires Bit-Shifted Input

The nuiCamera.GetColorPixelCoordinatesFromDepthPixel() mapping method expects the ‘depthValue’ parameter to be in the format it would have been if you had player tracking enabled. If you don’t, you’ll have to lshift 3 places to the left yourself, just to make it work. So depending on how you setup the runtime, the pixels from one part of the API can or can’t be passed to another part of the API. That’s poor form, if you ask me.

Not that you’ll find that in the doco of course, least of all the parameter doco.

No GetDepthPixelFromColorPixelCoordinates Method

Ok, so I understand that the depth to video coordinate space translation is a lossy one, but I still don’t see why this method doesn’t exist.

I picked up the Kinect SDK and the first thing I wanted to do was depth-clipping background removal. And the easy way to do this is to loop through the video pixels, and for each find the corresponding depth pixel and see what its depth was. And you can’t do that.

Instead you have to loop through the depth pixels and call the API method to translate to video pixels, but because there are less of them compared to the video pixels, you have to paint them out as a 2x2 block, and even then there’ll be lots of video pixels you don’t processes, so many you have to run the loop twice: once to set all the video pixels to some kind of default state, and once for those that map to depth pixels to put the depth ‘on’.

Just didn’t feel right.

Geolocation in HTML 5

Ok, so it’s not actually part of HTML 5 (the spec), but conceptually at least it’s definitely part of HTML 5 (the brand).

So what’s actually involved. Hmm. OH MY GOD IS IT THAT EASY !?
function showMap(position) {
// Show a map centered at (position.coords.latitude, position.coords.longitude).
}

// One-shot position request.
navigator.geolocation.getCurrentPosition(showMap);

[from the W3 geolocation spec]

So you just rock up to html5demos.com/geo and ...


Holy crap. I won’t show you the resulting map because it shows where I live. What’s really freaky about that is this netbook doesn’t have a GPS. So either Windows 7 or IE 9 has fallen back to IP-based location inference, and somehow still got me only one house out.

I’m totally freaked out.

Anyway, the point of all this is that IE 9 is the browser for Windows Phone 7.5 (Mango), which – if it actually supports this API (and Wikipedia says yes it does) - means you can write location-aware mobile apps targeting Mango without having to ‘go native’. And for the demo I want to put together, this can only be a good thing...

Windows 8: First Impressions

Q: "Your first impression? Love or hate?"

Well that's a really good question.

I was always horribly dissapointed with my HP TX2 multitouch laptop, and whislt some of that was about the hardware (rubbish battery life, noisy fan), some of it was just how non-touch capable Windows 7 was to actually use. Windows Media Center achieves many of my 'media center kiosk' wants, but doesn't let me Skype or browse the web without dropping back to the desktop and so forth. Then you've got to go and find the mouse and all that crap. I realised that, like Media Center, a different usage type required a very different UI experience.

So in many ways what I was after was absolutely where Windows 8 is going. And (in the 30 mins I've actually played with it) I love it for that.

That being said, they're going to have to be really careful they don't throw the baby out with the bath water. Ok the desktop's still there for 'traditional' apps, but the Start bar is gone, as is apparently ALT-TAB task switching. And without a touch screen, that metro UI really sucks actually.

It'd be more than a shame if embracing a device/cloud future required ditching 10 years worth of desktop productivity, it'd be a Vista-scale corporate-desktop disaster.

Yes, Silverlight is Dead

Not exactly quick off the block predicting this, but I didn't want to rush to judgement. But yes, Silverlight is dead, on the desktop at least.[1]

Why? Reach. Silverlight was always going to be playing catchup to Flash, which took pretty much a decade, remember, to get ubiquity. Silverlight just didn't have time on its side. Today if you build an app in Silverlight you can target contemporary browsers on Windows/Mac. By contrast if you build it in HTML you can target Macs, Linux, iPods, iPads, Android, Windows Phone, Kindles, PS3... the list goes on.

To put Silverlight out to all those individual devices is going to take Microsoft a heap of time and effort. By contrast all of those devices have web browsers already, most of them pretty good ones, and getting better all the time.

It's a numbers game. The browsers finally won.

But can HTML realistically replace Silverlight? Absolutely. Not entirely, not today, but surprisingly close, and getting closer by the day. The foundations for mature, maintainable web-client development are finally being put down. And the tooling. Visual Studio 2010's javascript IntelliSense is pretty damn impressive, and already supports jquery for example. Add support for MVVM development (ala knockout.js) and you've got a decent development workflow to rival what you might be used to in WPF/Silverlight/Winforms land (we'll probably see more about this out of Build this week). And don't forget there's a JS version of RX.

Sure, browser-based javascript is somewhat limited compared to the Silverlight runtime. The touch support isn't quite there yet, for example. But it's more than enough to support UI interaction, and the gap's closing awfully fast.

As a developer who started in web, then moved to the desktop I'm really excited about all of this because I can see a future that finally blends the best of both worlds.


[1] I'll clarify before I get flamed: Silverlight is not dead today. I'll be starting a new project using it real soon actually. But the transition is going to be pretty abrupt. I'll be amazed if you start any new Silverlight projects next year.

#AUTechEd 2011

The condensed version

• Denali Always On for Mission Critical Systems: failing demo rather confused this one. And I don’t understand how it can be shared-nothing mirroring if it’s still based on a Windows cluster
• Abundantly "Crescent": MS’s answer for BI visualisation gets even more fragmented
• Practical Consolidation: when to go hard(ware), when to go virtual
• HTML 5 - The useful bits: some bits you can actually use! Now!
• Kinecting The Dots – Developing with the Kinect SDK: I want a Kinect
• What's new for Windows Phone 7.5 Games Developers XNA and Silverlight: it’s not either/or any more
• What's new in Windows Phone 7.5 for End Users and Enterprises: finally, targeted (‘private’) deployments
• There is no Web: HTML5 Offline, NoSQL and MVC 3: offline support not there yet. Avoid for now. No, really.
• Killer Real-World PowerPivot Examples: Grant is insane
• JavaScript with a view – advancing client side code: viewmodel driven MVVM for JS is a reality. Use it
• Science of Great UI: it is a science. It is important.
• Data Dashboards using Microsoft BI: why use the right tool for the job when you can just keep hacking till it works?

Updated Links now point to Channel 9 site, where the videos will end up