Wizards are nice as long as nothing goes wrong. Behind the scene the carry out a lot of work and all you have to do is click on a button, wait...and pray to every God of every world religion that all goes well! Because if it doesn't....that's where the hair pulling starts!
Last week I got a call from a collegue of mine to take a look at a service pack installation problem on a MOSS 2007 server. All installations were carried out successfully, but when the configuration wizard kicked in, they received an error about a connection failure using Named instances. Because we disabled named instances & only allowed connections using TCP we were supprised by the error message.
Digging deeper into the system gave some insight into how SQL Server 2005 works and what causes the error:
In the early days SQL Server only allowed one instance per server which listened to port 1433 for incomming connections. As SQL Server 2000 introduced support for multiple instances a new service (SSRP) needed to provide a way of determining which instance needed to be connected to. This service was later replaced by SQL Server Browser service. Essentially this listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.
When the SharePoint Configuration Wizard kicks in, it requests information from the SQL Server Browser service about the instance to connect to. A failure contacting the listener will result in the "connection failure using named instances". Even though you disabled named instances and only allow TCP connections.
In our case the Service was running, but due to a SQL Server 2000 also running on the server it somehow got mashed up. Removing the old SQL Server 2000 instances & restarting the service solved the problem.
Lessons to be learned:
- Make sure the SQL Server Browser service is up and running on the SQL Server
- Make sure that no firewall is blocking the SQL Server communication (open up 1433 for TCP & 1434 for UDP)
If you do not want to open port 1434 or don't want to start the SQL Server Browser Service you can still run the configuration wizard by using the command line. This way you can provide all parameters manually to connect to the instance directly.
Psconfig –cmd configdb –create –server "dbserver\instance,1433" –database SharePoint_Config –user adomain\username –password ***** –admincontentdatabase SharePoint_Admin
More information on SQL Server Browser Service:
http://msdn.microsoft.com/en-us/library/ms181087.aspx
