Installing 64 bit MOSS with a 32 bit SQL Server (Using SQL Alias)

I have come across a issue which took me a while to figure out the reason.

Problem: I was installing 64 Bit Version of MOSS and configuring the databases on a 32 Bit SQL server. Now on the MOSS server I had 32 bit SQL client tools to setup the SQL server alias. So my connection string when configuring MOSS was "SharePointSQLServer" for the SQL server 10.x.x.x on instance ABC01 port 1435 (10.x.x.x\ABC01, 1435). Now I was setting up the alias on MOSS server using SQL client tools. For some reason confuguration wizard was not detecting the correct SQL alias and was throwing me error of cannot connect to DB server.

Solution and Attempts:

• I tried different sets of aliases but no help. For example tried using machine name instead of IP, tried using FQDN of machine name, tried without instance name. No help!
• Then I thought it might be 32 bit client tools. So I tried removing 32 bit version and installed 64 bit version of SQL client tools. Still No help!
• Worked Solution: Then I went through the registry entries at:

(32 Bit Location)
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

The SQL client tools add the regisrty entry to this location for 32 bit applications running on 64 bit operating System. But MOSS 64 Bit configuration wizard was not reading from this location and instead trying to use the Alias name directly from some other location.

So I went and checked in registry entries at this location:
(64 Bit Location)HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client

And there was no ConnectTo key. So even after installing 64 Bit version of SQL client tools the conenction string was not getting set at above location and was going into Wow6432Node key which is used for 32 bit applications. So I created a ConnectTo key by exporting from 32 Bit Location (Wow6432Node) and importing into 64 Bit Location so that my registry key on MOSS server looks like this:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

And checked that the value of connection string is: "DBMSSOCN,SharePointSQLServer,PortNumber"

And guess what? Yes it worked!

So on 64 Bit Windows 2003 machines if you get any issues related to entries in regisrty keys then you know where and what to look for :-)

Let me know if you have any questions on this.

Cheers,
Kunal