32-bit/64-bit aliases

Tue Apr 10, 2007 by Tara Kizer in sql-server-general

We don't use the default port for SQL Server for security reasons, so we usually have to explicitly tell the client how to connect to SQL Server by creating an alias on the client machine.

There are many ways to create an alias on a client machine. My favorite is via the registry as it allows me to save the values to a reg file so that we can quickly deploy them to other client machines.

Here is the location of these aliases:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Yesterday I was configuring transactional replication between two databases on different servers. The servers are on different networks and are separated by at least one firewall. I first tried connecting to the subscriber by running Management Studio on the publisher. As expected, I wasn't able to connect successfully using ServerName\InstanceName. I then tried using this in Management Studio: ServerName.DomainName.com,PortNumber. This worked, however replication requires you to use the actual SQL Server name (ServerName\InstanceName).

I then added the following alias to the previously mentioned key:
String value: ServerName\InstanceName
Data: DBMSSOCN,ServerName.DomainName.com,PortNumber

I was unable to connect using this alias. This should be no different that using ServerName.DomainName.com,PortNumber. After scratching my head for a while, I finally figured out what the problem was.

The publisher server is a 64-bit machine. When you are using 32-bit applications on a 64-bit machine, it uses the Wow6432Node key in the registry to present a separate view of HKEY_LOCAL_MACHINE\SOFTWARE.

Once I added the alias to the following key, I was able to connect successfully to the subscriber using Management Studio:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

Check this out for more details on Wow6432Node.

Note: SQL Server Configuration Manager allows you to create both 32-bit and 64-bit aliases when the client is 64-bit. I hadn't noticed this until after I found Wow6432Node in the registry.