SQL Server 2008 Express Edition: Installing on a Domain Controller

If you try to install SQL Server 2008 Express Edition on a machine which acts as a Domain Controller using the packaged Web Platform installer, it will fail and you will see an error in the installation log.

This is because it will attempt to install using Network Service as the default account, which is not allowed on a domain controller. If you look in the logs, you will see an error such as:

Network Service or Local Service account is not permitted for the SQL Server service on a domain controller

If you experience this issue, you need to do a customised installation. This blog post highlights the issues involved and has links to the appropriate installlers to get you up and running.

NOTE: Microsoft do not recommend installing SQL Server on a Domain Controller, so you need to think about your performance requirements and the potential issues such an installation may cause.

SQL Server Agent: Error When Running a DTSX Package to load Excel

When deploying a project onto a 64 bit Windows Server machine, I noticed an issue with scheduling SQL Server Agent to run a DTSX package to load an Excel file. The error is a bit like this:

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered"

I could run the package directly using SQL Server Management Studio without any problems, so it's obvious that the issue is being introduced by SQL Server Agent. After rooting around on the net looking for the answer, it seems that the agent is trying to run the package in 64 bit mode.

There are no 64 bit Excel drivers, so the package fails. For information on the impact that 64 bit architecture has on SSIS, refer to this blog post by Deniz Arkan, the SQL Server Integration Services Program Manager at Microsoft, and this Microsoft article on the subject.

Many forum posts suggest that the package should be run via the Agent using the 32 bit version of DTEXEC rather than the 64 bit version.

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\90\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\90\DTS\Binn.)

So if you have both 32 bit and 64 bit versions installed, you have to explicitly point to the 32 bit version of DTEXEC on the command line.

Alternatively, to run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box. This depends on your having the 32 bit runtime tools installed.

For a step by step description of how to run a package from an Agent job in 32 bit, refer to this Microsoft KB article.

But after all this reading, I bottled it. I can't be bothered having to get the 32 bit version of the runtime installed on the Dev, UAT and Production boxes so I decided to get the files I needed converted into a flat file format. This won't suit everyone as not everybody can get their input files changed to a different format, but for me it's the best route.

Anyway, clearly I am by no means an expert on this subject, but I reckon that Microsoft are trying to discourage people from using 32 bit architecture on 64 bit machines by making this kind of thing difficult to implement, and by not providing 64 bit drivers for their Office products.

SQL Server Job Error: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

If you deploy some DTSX packages to SQL Server, and create an Agent job to run them, you might see this error:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

The workaround is probably simple. When deploying to your SQL Server and you're on the Specify Target SQL Server page of the wizard, select the 'Rely on server storage for encryption':



That should probably do it. If not, check out this thread on MSDN.

Implementing Error Handling in SSIS

I've been trying to get to grips with SQL Server Integration Services, as opposed to the old DTS technology in SQL Server 2000. It's a task.

One of the things I've found difficult is to implement error handling, and after poring over much rubbish on the net, I found two useful blogs on the subject. Both of them have reusable code which is easily added to existing DTSX packages.

Step one is to read this blog by Jamie Thomson at SSIS Junkie.

Then, this additional blog adds a lot more detail and allows you to store errors in a database table which is very useful if your application has a lot of flat file data inputs (such as a reconciliation).

Now I just need to add error handling which will detect if the file is not there at all, and log that too!

SQL Server Reporting Services and IIS: Failed to access IIS metabase - Guaranteed Fix

I spent ages trying to get SQL Server 2005 Reporting Services working, and kept getting the error "Failed to access IIS metabase" from IIS.

After spending ages trawling through documentation, Microsoft KB articles, messing about with security settings and adding Windows and domain users to the IIS metabase, I found the answer in this post which worked perfectly and took less than a minute. If you're having a similar problem, this will solve it:

My MCP: Failed to access IIS metabase.

To summarise his post, you run this command:

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

What's the lesson? Ignore Microsoft and listen to the blogosphere.

Also: if you're just getting started with Reporting Services, this installation tutorial is the business, and this one is superb for showing you how to build and deploy reports.