One of the lessons I have learned the hard-way over the years is to “Never rename a machine/server running SQL Server/IIS or any of the server software”. If you must rename then think twice, tell for yourself the down-time is going to be nothing less than 2 days and then go about formatting the machine, reinstall OS, give the new name and reinstall everything again. If you are brave, then at least uninstall Server Software like SQL Server, IIS, etc. then rename, reinstall everything back. Doing anything else is risky.
Anyways, yesterday I was playing around with one of my VPC Images having Windows 2003 Server, SQL Server 2000, SQL Server 2005 & VS 2005. I wanted to install 2007 Office SharePoint Server Beta 2 on this. But I didn’t like the existing Machine Name (Host Name), since it was only a VPC I just went ahead and changed the name. After rebooting, tried working with VS 2005, SQL Server 2000, SQL Server 2005 and everything (Surprisingly!) seemed to work fine. With this false sense of confidence, went ahead and did Microsoft Update, that downloaded and ran the install for SQL Server 2005 SP1. Trouble Started. SP1 install refused to recognize my Authentication for SQL Services (How dare can it do this to me:-) ). After several Internet Searches, I realized I have no other option other than to play dirty.
<PLEASE DON’T TRY IT AT HOME>
Went to RegEdit, I found and replaced all instances of OLDHOSTNAME with NEWHOSTNAME. Did the same with all Groupnames found in Computer management for Groups created by SQL Server 2005. Went and reregistered services using the NEWHOSTNAME in the new “Reporting Services Configuration” tool. Restarted SQL Services. SP1 Install was now happy and went ahead smoothly, but very slowly
</PLEASE DON’T TRY IT AT HOME>
By this time though the problem seemed to be fixed, I realized this is not the best way to do this. Renaming a machine / host is a common problem for which there should be a recommended solution in SQL Server. Thanks to my fellow Microsoft Regional Directors: Kimberly L. Tripp & Scott J Golightly, I got the answer to do this correctly. I tried the following solution given by them in both SQL Server 2000 & SQL Server 2005, everything is now working like a charm now. Thanks KT & Scott.
sp_addserver newname, local
set originating_server = newname
where originating_server = oldname
RESTART SQL Server
If you want to check everything is fine and check the machine name registered in SQL Server, use the following code:
All the above works the same for both SQL Server 2000 and SQL Server 2005. A related problem has a fix at Microsoft KB Article #317241.