I Have always had a problem with IIS Manager using SQL Aliases when using the built in .Net membership providers (mainly cause its handy to test and setup the initial FBA user) but never managed to get down to the cause of the issue, just took it as one of those things.
Luckily it was never really that important to resolve so it got left as specifying the alias in the web.config file did appear to work fine. However, a colleague had a similar problem with Visual Studio when trying to regenerate a database layer, which was a bit more of an issue due to the storing of the string.
After a bit of googling we managed to track down the issue:
If like me your happy using the “cliconfg” command to specify aliases (mainly because its included in Windows Server 2008 R2 and there’s no need to install the SQL Native Client manually), its important to understand that the architecture is important, this is where “Native Client” should have given the hint. The problem is that the 32bit client and the 64bit client store their registry keys in different locations, therefore adding an alias in one doesn’t add it to the other. IIS Manager and Visual Studio are both 32bit applications and therefore use the 32bit aliases, however running the “cliconfg” command from run or a command prompt on a 64bit machine unsurprisingly loads the 64bit version of the client configuration tool. Therefore, if you run into this issue, you should run “c:WindowsSysWOW64cliconfg.exe” for the 32bit version.
Note: I have no idea why 32bit applications are stored in SysWOW64 and the 64bit ones in System32… appears like it should be the other way around to me?!