Wednesday, May 11, 2011

Alias created by cliconfg.exe (32-bit / 64-bit) don't work on 64-bit Windows 7

cliconfg.exe can be found in two locations at a 64-bit system when sql client tools are installed:
- [32-bit] C:\Windows\System32\cliconfg.exe
- [64-bit] C:\Windows\SysWOW64\cliconfg.exe

I wonder if anyone experience this.

Recently, I have moved my development machine to 64-bit windows 7 but I still have one project database running on 32-bit XP; the DB server is SQL 2005.

At my development box, I have SQL 2008 installed. I tried to use both 32-bit and 64-bit cliconfg.exe to create an alias but I cannot connect to that DB server on XP. In the SQL Server Configuration Manager, the alias created by both 32-bit and 64-bit cliconfig.exe are only shown up at "SQL Native Client 10.0 Configuration (32bit)" category.

[Updated on May 15, 2011] As a matter of fact, the entry is correct because the SQL2005 is running on 32-bit XP. Thus, the entry only shows up at 32-bit category. Indeed, this entry can only be used in .NET connection string for connection. Unfortunately, it cannot be used by SQLCMD (by default found at "C:\Program Files\Microsoft SQL Server\100\Tools\Binn") for remote connection. It was what I did last time. It failed.

If I use the non-32-bit client configuration in SQL Server Configuration Manager to create an alias, then the remote connection immediately works.

[Updated on May 15, 2011] Last time, I didn't check with .NET application. I only used SQLCMD -S <alias> for connection. Indeed, this entry only works for SQLCMD but fails for .NET remote connection.

In the registry, the one created by both cliconfg.exe will have an entry at

   HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

The one created by non-32-bit client configuration in SQL Server Configuration Manager will instead have an entry at the following registry:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Currently I don't have time to investigate it. But I just wonder why the alias created by either 32-bit or 64-bit cliconfg.exe utility won't work. I thought each version of cliconfg.exe maintains a separate list of alias in the registry. Why are both currently pointing to the same registry?

[Updated: May 15, 2011] The best reason to explain why both two 32-bit and 64-bit cliconfg.exe utilities create the same alias & registry entries is because the SQL server running on XP is 32-bit. The alias created by the non-32-bit client configuration in SQL Server Configuration Manager won't work for .NET but SQLCMD. Only the entry shown up at "SQL Native Client 10.0 Configuration (32bit)" category works well in .NET ConnectionString. Again, the main reason of my case is that the SQL server is running on 32-bit.

2 comments: