Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

32-bit/64-bit aliases

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.

Print | posted on Tuesday, April 10, 2007 10:23 AM | Filed Under [ SQL Server - General ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET