I was getting this error when i tried to run sharepoint best practices analyzer in our sharepoint Farm.
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Completed Collecting Data phase.
Starting Postprocessing Rules phase.
Completed Postprocessing Rules phase.
The Webfarm was configured with SQLServer 2005 with a default instance, everything was fine until i decided to run the sbp in LIVE . It was working fine in my Dev machines and in other enviorments except LIVE, there it throws this stupid error.
Google search, tried all my might to make it work but nothing happend and got no much help from net. There were couple of guys saying they created the database alias and got it fixed but nothing was solid.
Spend 2 days on it and finally got the trick to resolve, thought of spending some time which might help someone.
This error happens when you have multiple instance of the database Server ( I think so !!). SPB takes the database settings from the HKEY_LOCAL_MACHINE -> SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB\.
To fix the error you need to create a new ODBC > SYSTEM DSN with a proper alias.
This is what i did to fix this.
once its created click on the Created DSN and edit it
Click on the Client Configuration
Edit the instance with "\INSTANCE NAME"click next
Set the Config Database as Sharepoint_Config and Test it.
Check whether its successfully resolved.
Once this is done run the Sharepoint Best Practise Analyser again and Viola !!! it would start working .