If you don’t know (and that’s a good thing in this instance), cliconfg.exe is one of those older-than-dirt parts of Windows that is still around because it serves a specific purpose. In this case, it is used to configure SQL Server connection aliases.
Aliases are basically an abstraction between a SQL Server Instance’s actual connection string information (DNS name, Instance name, port, etc) and the name used by a client to connect to it. In this case, “client” can be either code/an application or a user. I know aliases are mostly evil, however some people do use them extensively. I don’t know that they’re all bad, but that’s a different post for a different time.
x86 cliconfg.exe vs x64 cliconfg.exe
Sit down at any remotely-modern Windows machine (it’s been there since 2000) and you can bring up a Run box, type “cliconfg”, press ENTER, and the utility will launch. If you do this on a 64-bit build of Windows, the 64-bit version of the utility will launch, as you might expect. This sounds good at first, but it might not be.
The first problem this presents is there is absolutely zero way to tell from within the utility itself which version of it is running. Therefore, if you’ve just moved from a 32 to 64-bit machine, there isn’t anything that might get your attention and suggest that anything unexpected is going on. This has probably happened a long time ago for some, but if you’re a corporate monkey like I am, you might have only recently been able to make the jump to a 64-bit OS.
The ultimate issue that crops up stems from why there are two versions of cliconfg.exe in the first place. In short, Windows keeps separate lists of aliases, one used by 32-bit applications and the other by 64-bit. These lists are maintained by their respective versions of cliconfg.exe. These lists are completely independent, and are only accessible by applications compiled with the same bit width.
In order to launch the 32-bit version of cliconfg, it has to be run from the folder it’s in. The path to this file is %SystemDrive%\Windows\sysWOW64\cliconfg.exe. As mentioned, it looks (and acts) just like the 64-bit version. I’d think that the folder should be named “sysWOW32” instead, but what do I know?
Which one to use?
Setting up aliases needs to be slightly planned out, although the same aliases can just be set up on both “sides”, and call it done. If one chooses not to do that, it’s a matter of thinking about what apps are going to
be utilizing any given alias, and then setting that alias up on the same bit width as the app. For example, to use an alias from within SQL Server Management Studio, the alias needs to be set up on the 32-bit side, as SSMS is still a 32-bit application.
Plan B (or Plan A?)
Here’s a different idea: Don’t use cliconfg.exe!
It’s old. I have a feeling it will go away some day. It isn’t very clear on what you are doing (32 vs 64-bit). All problems. Instead, consider using the SQL Server Configuration Manager. It’s new(er). It’s part of SQL Server, so it will probably be around for at least a bit. It is also very clear about the difference between 32 and 64-bit aliases.
For the current topic of conversation, that last point is the most important one. On a 64-bit machine, the Config Manager will have two Native Client items in the tree view in the left pane, one for 64-bit and the other for 32. Aliases configured in one do not show up in the other, making it perfectly clear what is going on.
I know that aliases are bad; using cliconfg.exe to manage them is arguably worse. However, if the situation exists that they need to be used, care needs to be taken in 64-bit client environments. The best way to deal with this situation is to use Configuration Manager.
Good article. I’ve been bitten by this before. While using Cliconfg.exe stinks, not all client machines have the SQL Server Config Manager on them. Only machines that have had a SQL component installed on them will. I’m a SharePoint consultant and almost none of my servers will have it, and in some larger companies installing SQL components on SharePoint servers is expressly forbidden. In those cases I’m forced to either use cliconfg.exe to set the aliases, or set them in the Registry.
tk
Thanks!
Yeah, servers are definitely the big place where this comes into play. Funny, I think an x64 SharePoint box was the first time I ran into it, too.
[…] Recently, through twitter, a blog post by @AirborneGeek caught my attention. It’s titled A Note About cliconfg.exe on x64 Machines where Kerry Tyler writes about the different versions of cliconfg.exe; I agree its very […]
Hmmm… please read this article: http://vogtland.ws/MarksBlog/?p=270&cpage=1
He seems to be saying explicitly that you should NOT use SQL Server Configuration Manager on the actual SQL Server machine, but instead you MUST use cliconfg.exe on each server that points to SQL. Please battle it out with the other guy and let us know what’s up… thanks!!
It looks like Mr. Vogt is pointing out that aliases are a function of the client “end” of the client/server arrangement, as opposed to being something that you set on the server.
Bottom line, he’s correct; he’s also not saying anything too much different than I am. Aliases should be set up on each machine that needs to connect to the SQL Server, whether that box be a workstation or a server. In fact, I almost always (and recommend) setting the alias up on the actual server that the alias is for. This ensures that the server itself will answer to its alias name. I find this useful for sanity purposes in case a script has to be tested on the actual machine or something similar.
My reference to using SQL Server Configuration Manager is simply using it as an alternative to cliconfg.exe. The Config Manager applet is installed along with the normal client tools on any machine (well, OK, maybe just the “Advanced” ones and not “Basic”; I don’t think I’ve ever installed just the Basic ones), either workstation or server. So, just because you’re using the “server” tool, doesn’t mean you have to be doing so on a server 😉
Thanks for very useful info. I could not understand why my Alias was not working until figured it out it was added in 64-bit, but not 32-bit.
Probably, you can put the location of the 32-bit.
But, I agree with you. It’s a lot easier to use Configuration Manager (only if you use TCP/IP -> but mostly) as both are displayed (32 and 64 bit).
In order to learn about it.
If you run cliconfg.exe in run you will 64bit version
if you run %windir%\SysWOW64\cliconfg.exe you will run 32 bits version
In Windows register the keys
HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
keep the configuration values entered with Cliconfg.exe or the SQLServerManager##.msc (##=10 for SQL Server 2008).
thanks for this. My colleague was stuck on this and now I AM the boss! (;
Awesome! You’re very welcome.
I like your ‘Plan B’. However, what’s interesting is that I’m an MCT and teach a lot of the Microsoft SharePoint classes. Even in the SharePoint 2013 classes, Microsoft still recommends using cliconfig.exe upon initial install of SharePoint in the case that you wish to move the configuration and content databases at a later date.
My issue with that is that the installing SharePoint using a SQL alias does _nothing_ to make it easier if you need to move the Config (or any other) database later on. Whether you use an alias or not the process of moving to a different SQL instance is exactly the same. Actually, not using a SQL alias at installation is _less_ work that using one, since you only mess with it once, when you actually move the databases. If you configure SharePoint with a SQL alias you need to mess with the alias once at installation and then again when you move the databases.
I teach a lot of SharePoint admin classes. Whenever this question comes up I always stop and take a poll of the class to see how many people know what a SQL alias is. These days maybe half the class raises their hands. When I started talking about this in 2009 it was maybe 20%. I don’t like using SQL aliases unless you need them for the same reason I don’t like using aliases in PowerShell, they make it tougher for the next guy if he doesn’t know about them.
tk
Thanks for the very useful info. I wasnt aware of cliconfg before today. Also the Plan B (or Plan A ?), finally clarified it for me. I couldnt figure out where I was going wrong with the Alias, until I realised the tree shows a 32bit and 64bit config option.
I will be using this in the next few days where we have a legacy app which is being installed on a new server and we will have to create an alias to a new db server as the app still expects the old db server names. I will probably have to use cliconfg on the windows client machines as they will not have SSMS or the connectivity s/w installed on them.
Thanks for clarifying this and giving me a eureka moment !!!