Avoid XmlConvert.ToString(DateTime)

XmlConvert is the utility class that controls fundamental .Net data types being converted to/from their XML representations. For dates, the DateTimeOffset overloads are less ambigious than the DateTime ones, and as a result the latter have been depricated. But you'd still expect it to basically work for the fundamental scenarios.

Nope. For DateTimeKind=UTC dates, it's totally broken.

Ttry this (in Linqpad):

	var localTime = DateTime.Now;
	var utcTime = localTime.ToUniversalTime();
	
	localTime.Dump("Local Time");
	utcTime.Dump("UTC time");
	utcTime.Kind.Dump("UTC Kind");
	utcTime.ToString("yyyy-MM-ddTHH:mm:ss K").Dump("Expected XML");
	XmlConvert.ToString(utcTime).Dump("Actual XML from XML Convert");

Gives:

Local Time	23/10/2012 11:11:11 AM
UTC time	23/10/2012 3:11:11 AM
UTC Kind	UTC
Expected XML	2012-10-23T03:11:11 Z
Actual XML from XML Convert	2012-10-23T03:11:11.0773940+08:00

You'll notice immediately (because of the highlighting), that it's done something really really dumb. It's made out that the UTC time is actually a local time in my local timezone offset (+8). It's screwed up the XML representation, and as a result changed the actual time value being passed, even though it knows (based on DateTime.Kind==DateTimeKind.Utc, that the ToUniversalTime() method always gives you) that it's a UTC date.

This is because internally that method does this:

[Obsolete("Use XmlConvert.ToString() that takes in XmlDateTimeSerializationMode")]
public static string ToString(DateTime value)
{
    return XmlConvert.ToString(value, "yyyy-MM-ddTHH:mm:ss.fffffffzzzzzz");
}

...and those z's add the local timezone offset from your PC. This is wrong for a DateTime with Kind=UTC. A better implementation would be as per my example above, ie:

    return XmlConvert.ToString(value, "yyyy-MM-ddTHH:mm:ss.fffffffK");

The K specifier is not so dumb, and adds the time zone to local time datetime instances, and adds the Z to UTC instances. Or alternatively an if{}else{} based on value.Kind==DateTimeKind.UTC.

Anything but what it does right now.

Why do I care? Well WCF generates webservices proxies using DateTime, not DateTimeOffset, so I want to make damn sure it's not making the same mistake.

Nuget Package Restore with internet-restricted build servers

The first time I enabled nuget package restore I had a nasty shock: the TeamCity build server didn't have internet access (and wasn't getting any).

I initially went back to storing-packages-in-source-control, but when I created a spike ASP.Net MVC 4 project, which wanted to add some 30 packages, I decided I needed to address the root problem.

A few people suggested I should setup a local nuget repository, the simplest version of which is just to dump all the packages you need into a file share. But how to point the build server at the share, rather than the standard nuget feed URL? Short of logging in as the build server user and changing the settings through Visual Studio (lame), there appear to be a couple of ways:

Editing %appdata%\nuget\nuget.config (for the build server user)

This achieves the same as the above, but you don't have to log in as that user. It's reliable, but because it doesn't 'carry' with the solution in source control it's brittle (needs doing again if the build server user changes etc...) and you can't vary it on a project by project basis, which is a bit limiting.

Editing the generated nuget.targets file that package restore adds to your solution

This looks like the go, and even has comments in there to show you what to change. But I didn't find this reliable. On my local machine (under a no-internet user) this worked just fine, but on my build server I kept having package resolution errors (from a Silverlight 4 project).

Setting the PackageSources build parameter

What the above did show is what MSBuild properties are involved, so I was able to specify that property on the MSBuild command line and that seems to work consistently:

MSBuild mysolution /p:PackageSources=\\server\fileshare

...now finally I have a green light on TeamCity and can go and do something actually productive instead.

Creating SQL Credentials for Network Service account

It’s fairly normal in production environments to find the SQL Server configured to disallow use of the SQL Agent account for the execution of certain types of job steps: SSIS packages and CmdExec for example. Instead you have to configure an explicit SQL Agent proxy, which requires first storing credentials within SQL’s credential store.

For domain accounts this is fairly straightforward, but if you attempt to add credentials from one of the ‘virtual accounts’ (such as Network Service), you’ll get the following error: “The secret stored in the password field is blank”

The solution is (eventually) obvious: add the credential using TSQL (or SMO), and avoid the UI validation:

USE [master]
GO

CREATE CREDENTIAL [Network Service] WITH IDENTITY = N'NT AUTHORITY\NETWORK SERVICE'
GO

et, voila:

vsdbcmd 'built by a runtime newer than the currently loaded runtime and cannot be loaded'

When deploying the latest version of our application to the Production server we got this error:

Unhandled Exception: System.BadImageFormatException: Could not load file or assembly 'vsdbcmd.exe' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. File name: 'vsdbcmd.exe'

Oh god, I thought. Yet more VSDBCMD wierdness. But this box had SQL Server installed, so the normal 'needs SMO / Batch Parser' caveats didn't apply. Eventually I ILSpy'd the assemblies to check the bittyness, and guess what! The error message was completely accurate. I'd accidentally picked up the VSDBCMD not from the VS 2008 folder (9.0) but instead from the VS 2010 folder (10.0). Which is .Net 4. Which really is a more recent version of the runtime than was installed on the Windows 2008 R2 server. Embarrasing to be caught out by a completely accurate error message (though if it listed the versions involved I might have paid attention)

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