Thursday, 23 April 2009

Error in sharepoint best practices analyzer

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.

Solution
=========
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 .

2 comments:

Fishman said...

Thanks for finding a fix and publishing it! Seems kind of silly seeing that this tool emphasizes best practices for farms that would likely use named instances!

Chee Choong said...

Hi All, just to share information.


The above is work for SQL 2005. If you are using WSS 3.0 single server then you need another step for Windows Internal Database connection (MICROSOFT##SSEE).

I have serching very long in the web cant find the solution. Finally found one link and edit one setting then it works.

Following is another link guide to setup for ODBC for MICROSOFT##SSEE

http://isatechie.blogspot.com/2009/02/windows-internal-database-how-should-i.html

One important thing to take noted the server alias column must use "servername\MICROSOFT##SSEE" then the Best practice analyzer tool will work :)