Everyone knows that using SQL connection alias’s are a pretty good idea, as are A records in DNS for when you want to be able swap around your SQL server when the fancy, need or emergency takes your fancy.
I recently was helping a customer trying to do just that but were implementing SQL Connection Alias’s for the first time in order to swap out an old, underpowered and overloaded SQL 2000 server with a more powerful SQL 2005 x64 machine running their MOSS 2007 farm.
As much as they tried, when the connection alias was put in place the servers came back with "Cannot connect to the configuration database". I got involved and even went as far as installing SQL Management Studio on the front end with the alias in place. Funny thing was – it worked. Despite the connection alias working properly the farm could not communicate with SQL.
After a lot of research, looking at logs and trying a few permutations on the cliconfg.exe tool I came across the answer. There were references to the SQL server by FQDN in the registry and the alias was only doing the host name.
This farm wasn’t well documented and even less well configured so finding this kind of inconsistency wasn’t surprising. Mike Hackers blog post mentioned the registry key as part of a post on another topic.
For my case I changed the 14.0 to 12.0 for this MOSS 2007 environment and edited the connection string.
My Computer
HKEY_LOCAL_MACHINE
SOFTWARE
Microsoft
Shared Tools
Web Server Extensions
14.0
Secure
ConfigDB
I changed the DSN value from the FQDN of "server.domain.local" to "server" and Central Administration came up.
Nasty little inconsistency from when the farm was setup.