SQL Aliases in the real world

Two years ago I wrote a simple article on how to create SQL aliases on your server. For some reason, that post is almost as popular as it is simple.

Recently I had a situation at a customer where our carefully crafted alias wouldn’t work when installing SQL Reporting Services. On the same machine we already installed SharePoint so we knew our alias was ok.

64 bit versus 32 bit

Then we realized that the SharePoint configuration wizard is a 64 bit process, whereas the reporting services configuration manager is 32 bit. It turns out that just typing cliconfg.exe on a 64 bit server only applies to 64 bit applications, 32 bit applications cannot use it.

You can determine the architecture of your application by running Task Manager. 32 bit processes have “*32” appended to their process name.

SQL Server Reporting Services Configuration Manager is 32 bit, whereas the SharePoint Configuration Wizard is 64 bit:

32 Bit alias needed

64 Bit alias needed

Therefore, on a 64 bit system you have to create your alias with two different tools:

  • 64 bit: C:WindowsSystem32cliconfg.exe (also in your PATH)
  • 32 bit: C:WindowsSysWOW64cliconfg.exe

When you happen to have the SQL Server Configuration Manager installed on your server, you can do it from there too: expand SQL Native Client Configuration, right-click Aliases, and then click New Alias. Don’t forget: On a 64 bit system, there are two nodes - one for 32 bit aliases and one for 64 bit aliases.

Configure a 64 Bit alias through SQL Configuration Manager

The screenshot above displays the 64 bit alias I created with cliconfg.exe. Below I create a 32 bit alias with the Configuration Manager as an illustration:

Configure a 32 Bit alias through SQL Configuration Manager

Testing your alias

A little trick I always use to test an alias, is creating an empty text file with a .udl extension. As Windows does not want you to see common file extensions, you’ll have to enable that first before you can rename your .txt file to .udl

Create a dummy text file Convert to an UDL file

After you created your .udl file, double-click it and the well-known data link screen will appear. Fill in your newly created alias as server name, choose the desired authentication method and test if you can connect to a database. Unfortunately, this only tests the 64 bit alias on a 64 bit system.

Testing with an UDL file

BTW, developer types may find this utility useful to create connection strings. Tip: opening the .udl file with notepad may reveal useful info to you. Also take a look at the Provider tab of the tool.