SQL Server 2005 Express Edition on Windows 2008 x64

While experimenting with the Microsoft App-V 4.5 Release Candidate (more on that soon), I decided to go for a full-blown installation on Windows 2008 x64. Since this is only on my home network, I don't run a dedicated SQL server so I went for the natural choice of installing SQL Server 2005 Express Edition SP2 on my freshly installed Windows 2008 x64 App-V server.

This turned out to be less trivial than I thought. The short answer is: if you want to have a painless install of SQL Server 2005 Express Edition, take the download that includes the “Advanced Services” and simply don’t install them. The “smaller” download package does not include some necessary files for a successful x64 installation.

If you want to go the hard way and patch the setup for easier automated deployment (or just to be ‘1337 and be able to say that you fixed Microsoft’s SQL Server installer for 64-bit systems…), then follow these steps:

• First of all, you should know that SP2 is the first Vista/Windows 2008 certified edition (think UAC, think session zero hardening, think enhanced security). Secondly, SQL Server 2005 Express Edition SP2 is supported to run under WOW64. That is very comforting to know, and I hadn't expected a true 64-bit edition for free. So why does it complain about installing a 32-bit version on a 64-bit machine then?
  
  
  
  "The installation package has a missing file, or you are running a 32-bit only Setup program on a 64-bit computer"
  
  Of course, what you don't see is that SQL is first installing the SQL Native Client in the background (as a prerequisite) and the error message conveniently forgets to mention that this is in fact the installation that is not succeeding. The error message was indeed accurate, but the error was not that I was trying to run a 32-bit installer on a 64-bit machine, but that the 64-bit installer for the SQL Native Client is not included in the package! What’s even worse, some other essential x64 packages are also not included in the smallest SQL Express 2005 SP2 download.
  
  
• So you have to include the missing files manually:
  
  
  1. Download the “SQL Server 2005 Express Edition SP2 with Advanced Services” package.
     
     
  2. Run both the SQL Express installers with the /X switch to extract the setup files (to different directories):
     
     sqlexpr.exe /x
     sqlexpr_adv.exe /x
     
     
  3. Next, locate the 64-bit SQL Native Client (sqlncli_x64.msi) and 64-bit SQL VSS Writer (SqlWriter_x64.msi) from the Advanced Services setup and copy them to the "Setup" directory of the regular SQL Express installation.

Et voila! The installer works now. One day, we will live in a perfect world of unambiguous error messages...

Now off to do some more SoftGri... ehr.. I mean Microsoft Application Vir... ehr... I mean App-V testing!

Installing LSI Logic RAID monitoring tools under the ESX service console

As I discussed in a recent post, I used a Dell Perc 5i SAS controller in my ESX whitebox server. One of the nice features of this controller is that it is a rebranded LSI Logic controller (with a different board layout!), supported by LSI Logic firmwares and the excellent monitoring tools that LSI offers.

Of course, it is important to keep track of your RAID array status, so I decided to install the MegaCLI monitoring software under the ESX Server 3.5 Service Console. Here's how I did it and configured the monitoring on my system:

• The MegaCLI software can be downloaded from the LSI Logic website. I used version 1.01.39 for Linux, which comes in a RPM file.
  
  
• After uploading the RPM file to the service console, it was a matter of installing it using the "rpm" command:
  
  rpm -i -v MegaCli-1.01.39-0.i386.rpm
  
  This installs the "MegaCli" and "MegaCli64" commands in the /opt/MegaRAID/MegaCli/ directory of the service console.

That's it, MegaCLI is ready to be used now. Some useful commands are the following:

• /opt/MegaRAID/MegaCli/MegaCli -AdpAllInfo -aALL
  This lists the adapter information for all LSI Logic adapters found in your system.
  
  
• /opt/MegaRAID/MegaCli/MegaCli -LDInfo -LALL -aALL
  This lists the logical drives for all LSI Logic adapters found in your system. The "State" should be set to "optimal" in order to have a fully operational array.
  
  
• /opt/MegaRAID/MegaCli/MegaCli -PDList -aALL
  This lists all the physical drives for the adapters in your system; the "Firmware state" indicates whether the drive is online or not.
  

The next step is to automate the analysis of the drive status and to alert when things go bad. To do this, I added an hourly cron job that lists the physical drives and then analyzes the output of the MegaCLI command.

• I created a file called "analysis.awk" in the /opt/MegaRAID/MegaCLI directory with the following contents:
  
  

  # This is a little AWK program that interprets MegaCLI output
  
  /Device Id/ { counter += 1; device[counter] = $3 }
  /Firmware state/ { state_drive[counter] = $3 }
  /Inquiry/ { name_drive[counter] = $3 " " $4 " " $5 " " $6 }
  END {
  for (i=1; i<=counter; i+=1) printf ( "Device %02d (%s) status is: %s <br/>\n", device[i], name_drive[i], state_drive[i]); }
  

  This awk program processes the output of MegaCli, as you can test by running the following command:
  
  ./MegaCli -PDList -aALL | awk -f analysis.awk
  
  when being in the /opt/MegaRAID/MegaCLI directory.
  
  
• Then I created the cron job by placing a file called raidstatus in /etc/cron.hourly, with the following contents:
  
  

  #!/bin/sh
  
  /opt/MegaRAID/MegaCli/MegaCli -PdList -aALL| awk -f /opt/MegaRAID/MegaCli/analysis.awk >/tmp/megarc.raidstatus
  
  if grep -qEv "*: Online" /tmp/megarc.raidstatus
  then
  /usr/local/bin/smtp_send.pl -t tim@pretnet.local -s "Warning: RAID status no longer optimal" -f esx@pretnet.local -m "`cat /tmp/megarc.raidstatus`" -r exchange.pretnet.local
  fi
  
  rm -f /tmp/megarc.raidstatus
  exit 0

  
  Don't forget to run a chmod a+x /etc/cron.hourly/raidstatus in order to make the file executable by all users.
  

In order to send an e-mail when things go wrong, I used the SMTP_Send Perl script smtp_send.pl that was discussed by Duncan Epping on his blog.

Renaming a VirtualCenter 2.5 server

After running my VirtualCenter server on a standalone host for quite some time, I decided to join it into the domain that I am running on my ESX box (in order to let it participate in the automated WSUS patching mechanism). This also seemed like a perfect opportunity to rename the server's hostname from W2K3-VC.pretnet.local to virtualcenter.pretnet.local. However, after the hostname change, the VMWare VirtualCenter service would no longer start with an Event ID 1000 in the eventlog.

Somehow, this didn't come as a surprise ;). This has been discussed before on the VMWare forums (here and here), but I post it here because I did not immediatelly find a step-by-step walkthrough.

The problem was in fact twofold, the solution rather simple:

• Renaming SQL servers is a bad idea in general (so it appears). For my small, nonproduction environment, I use SQL Server 2005 Express edition that comes with the VirtualCenter installation. If you rename a SQL server, you need to internally update the system tables using a set of stored procedures in order to make everything consist again. This is done by installing the "SQL Server Management Studio Express" and then executing the following TSQL statements:
  
  sp_dropserver 'W2K3-VC\SQLEXP_VIM'
  GO
  sp_addserver 'VIRTUALCENTER\SQLEXP_VIM', local
  GO
  sp_helpserver
  SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')
  
  The first statement removes the old server instance (replace W2K3-VC with your old server name), the second statement adds the new server instance (replace VIRTUALCENTER with your new server name). The sp_helper and SELECT statement query the internal database and variables for the actually recognized SQL server instances. You need to perform a reboot in order to get the proper instances with the last two statements.
  
  
• Secondly, the System ODBC connection that is used by VMWare required an update to point to the new SQL Server instance. This was of course done using the familiar "Data Sources (ODBC)" management console.

Afterwards, the VMWare Virtual Center Server service started just fine again.

Enabling Subject Alternate Name certificates

When requesting certificates from your freshly installed Certification Authority, it can come in handy to specify multiple DNS names that this certificate should be valid for. This principle is known as specifying a list of "subject alternate names" that the server is also reachable under.

Unfortunately, this mechanism doesn't work out of the box with Windows CA's. On your CA, you first need to enable a setting that allows the usage of SAN attributes. Open a command box and type (on one line):

certutil -setreg policy\EditFlags +EDITF_ATTRIBUTESUBJECTALTNAME2

net stop CertSvc & net start CertSvc

Afterwards, use the SAN:dns=&dns= attribute when requesting certificates to enable multiple DNS names.

Windows 2008 Certificate Authority and Windows 2000/XP/2003 clients

I was experimenting with Windows 2008 Certificate Services the other day in order to create certificates for WSUS 3.0 and for doing SSL tunneling of RDP towards the internet. I noticed that several of my clients were unable to automatically install the WSUS client, with vague errors in the event log (Win32HResult=0x00000000):


I had quickly discovered that the problem was related with the certificate that I had issued for the WSUS IIS server. It turned out that Windows 2008 WSUS clients could connect without any problem to the WSUS webpage, but Windows 2003 and Windows XP clients could not. What made it even more puzzling is that on a Windows XP system, connecting to the IIS homepage didn't succeed using Internet Explorer, but worked perfectly fine using Firefox.

Opening the certificate of my WSUS server gave the following result:


with a "This certificate has an nonvalid digital signature" error in the "Certification Path" details for both the issued certificate and my CA certificate.

Root cause:
The answer is the bleeding obvious: Windows 2008 has several new additions to the cryptography API, called Cryptography Next Generation (CNG), that are used in the V3 certificate templates for CA's and Webservers in Windows 2008. Amongst those new features is support for new certificate signing algorithms (in my case SHA512, a SHA-2 variant) which is not recognized by older clients. Windows XP SP3 adds support for XP, I suppose a future hotfix will add compatibility for Windows 2003.

Solution:
In absense of a worldwide XP SP3 deployment and a working hotfix for W2K3, the only option here is to ensure that the Windows 2008 CA certificate is created with a non-CNG cryptographic provider. If you already created a CA certificate using the new CNG features, the only option is to reinstall your CA and regenerate your CA certificate --- remember how mum always told you to think things over twice before just plainly installing a W2K8 CA... I bet you regret that now (just like I did :D) ? Reinstalling your CA could be messy, and make your PKI infrastructure go berserk, so this time do think twice before going down that road!

Step by Step plan of attack (POA)
So you have decided you want to proceed? First verify that you are indeed using a CNG CSP. To do this, open your registry editor and navigate to the following key:

[HKLM\SYSTEM\CurrentControlSet\Services\CertSvc\
Configuration\{CAname}\CSP]

If you find a CNGHashAlgorithm REG_SZ value, and the HashAlgorithm DWORD is set to 0xFFFFFFFF, then you are using a CNG CSP. If the HashAlgorithm is set to a value such as 0x00008003, then you are already using a "classic" CSP. You can also use the following command on the CA to retrieve the CSP:

certutil -getreg ca\csp\HashAlgorithm
certutil -getreg ca\csp\Provider

which will return the HashAlgorithm and the name of the CSP. For more information, I refer to the Microsoft whitepaper "Active Directory Certificate Server Enhancements in Windows Server Code Name Longhorn", you crypto-boys out there will love it.

Keep in mind that when you are adding the Certificate Services Role to your Windows 2008 server, that you need to specify the proper cryptographic service provider. The image below displays some of the options, what is important to remember here is that all the service providers that contain a hash sign ("#") are CNG providers and thus incompatible with Windows XP SP2/Windows 2003 and earlier clients.


The default cryptographic service provider for Windows 2003 is the "Microsoft Strong Cryptographic Provider", so that is what you want to use. Notice how selecting this provider reduces the number of certificate signing options... SHA-2 algorithms are no longer included! Proceed as usual to end up with a CA that produces certificates that can be handled by legacy clients.

ESX 3.5 on a whitebox

It has been very quiet from my end for the past weeks because I was very busy at a client & at the same time spending all my free time working on my ESX-on-whitebox hardware project. After being inspired by some colleagues, I decided to order the following hardware:

• Asus P5BP-E/4L motherboard
  This motherboard supports an Intel S775 processor, has VGA and audio onboard and most importantly, the LAN controllers on this motherboard are ESX certified (Broadcom 57xx chipset).
  
  
• Intel Q6600 Quad Core processor (2.4 GHz) and 8 GB ECC RAM (4x 2GB)
  Just to be sure I have enough CPU power and memory resource pools :)
  
  
• Dell Perc 5i Integrated SAS Controller
  My colleagues advised me that storage was the biggest bottleneck in their ESX whiteboxes (based around the very nice Asus P5M2/SAS board). I decided to go for a dedicated hardware controller. I picked up the Dell Perc 5i controller, which is more or less a rebranded LSI Logic 8408 SAS controller on EBay with 256MB of RAM and a battery backup unit for about 175 EUR.
  
  The main advantage of SAS controllers is that they also support the (cheaper) SATA consumer drives. A quick test confirmed this; I had absolutely no problems at all with this controller & even flashed the latest LSI Logic firmware to it :).
  
  Maybe of interest for some: the later Dell firmwares and also the later LSI logic firmwares for this controller provide support for Write Back without a BBU present.
  
  
• SATA to SAS cables
  The Dell Perc 5i has SFF-8484 SAS connectors on board, so I purchased two Adaptec SFF-8484 to 4xSATA cables from a nearby store to attach all the drives.
  
  
• 8 Seagate SATA harddisks (4x 1TB and 4x 200GB)
  Space... loads of space.

The hardest thing was getting all these disks in my Silentmaxx ST11 casing; it required some case modding and loads of patience to get everything well fitted. The 500W PSU that is necessary to provide enough juice, was recycled from an Antec Sonata case. I also added a small 3Com 3C905 100Mbps card for my ISP modem connection.

The installation of ESX 3.5 was a piece of a cake & and I can confirm that the above hardware works like a charm. For those interested, I also noticed that ESX 3.5 supports the ICH7 SATA controllers (found on many consumer motherboards as well). I think -- but this has to be confirmed by someone else -- that you need to configure your ICH7 disks in a RAID before the ESX kernel will accept them as a storage pool.

MAV 4.5: How to perform a Dynamic Suite Composition

The blog of Justin Zarb details a step-by-step guide of how the Dynamic Suite Composition (DSC) in Microsoft Application Virtualization 4.5 functions. He describes how to include a Snag-It bubble into an existing Office 2007 bubble. There are not many technical details about how DSC works, but some interesting facts are mentioned there:

• Appearantly, it is possible to compose multiple bubbles but only one level deep. If you attempt to include an OSD file that in itself has another DSC, this third bubble is not included.
  
  
• Sequencing tip: make sure your sequencer workstation has all the software installed that you want your second bubble to hook onto. For example: if you are sequencing an application that integrated with Office 2007, do a fat installation of Office 2007 first, and only then start the monitoring and sequencing of the add-ins.
  
  Personal note: also for applications that depend on Java or Oracle clients, you obviously first need to prepare your sequencer workstation by installing those core components.
  
  
• The user changes that are made in the dynamically composed bubbles are all redirected to the primary bubble's UsrVo_sftfs.pkg files.

I am already planning for a few weeks to delve deeper into the DSC of MAV 4.5 beta and to check in more details what Justin describes, but a project at a customer currently prioritizes my spare time into non-MAV related things. Be sure to check regularly here again for more information on DSC.