Why you should use a SQL Alias with SharePoint
Recently I was asked to take a look at a SharePoint 2007 system that was having a few problems after an IP readdressing project.
The 2007 Farm was stretched across 2 domain, the main farm was working fine after the readdressing, but one server in the 2nd domain was having problems connecting to SQL.
When trying to connect to a web application hosted in the 2nd domain we were getting the ever helpful “An unexpected error has occurred” message in the browser.
The ULS Logs were full of database connection errors, but we could ping the database server okay, we could TELNET to port 1433 okay. We had all the firewalls checked for dropped packets but could not see an faults.
After many hours of checking and rechecking we decided to re-run the SharePoint Config Wizard, which timed out and failed but left these entries in the PCDiagnostic log file.
03/13/2012 12:11:44 1 INF Found a v3 confidb in the registry. configdb SharePoint_Config, server xx.xx.xx.xx
03/13/2012 12:12:14 1 INF Calling SPFarm.Local and SPServer.Local to get the local farm objects
03/13/2012 12:23:24 1 INF SPFarm.Local returned null. This usually means that the server is not joined. But, you can delete a server from the configdb without unjoining, which would mean that this machine still thinks it is joined.
03/13/2012 12:23:24 1 ERR Discovered a v3 connection string and SPFarm.Local indicates that this machine is not joined. Therefore we failed to determine if we were joined to a server farm.
This finally shed some light onto the problem, the IP highlighted above was the previous address of the SQL server, not its new address, or its name, so we checked the registry and found that the dsn for the SharePoint SQL server has the SQL servers previous IP address, as soon as we updated this to the SQL servers name SharePoint sprang into life.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB]
"dsn"="Data Source=xx.xx.xx.xx;Initial Catalog=SharePoint_Config;Integrated Security=True;Enlist=False;Connect Timeout=300"