SQL Server – Error : Fix : SharePoint Stop Working After Changing Server (Computer) Name

Why does SharePoint stop working after a rename? You renamed the machine, Windows was happy, and SharePoint stopped. The cause is almost always the same. SQL Server keeps its own copy of the server name and renaming Windows does not update it. Two commands fix the database side, and then you have to go and find everything still holding the old name.

Prove It Before You Fix It

One query settles whether this is your problem:

SELECT @@SERVERNAME                          AS sql_thinks_it_is,
       SERVERPROPERTY('MachineName')         AS windows_says,
       SERVERPROPERTY('ServerName')          AS computed_name,
       SERVERPROPERTY('InstanceName')        AS instance_name;

If the first two disagree, you have found it. @@SERVERNAME reads a value stored at install time. MachineName reads Windows. Renaming the computer changes one and not the other.

Why SharePoint Cares So Much

SharePoint stores the database server name in its configuration database. It also stores it in the registry on every server in the farm. It uses that name to connect, and to check it is talking to the right farm.

So it holds the old name in several places, and SQL Server now reports a different one. Many of SharePoint’s own health checks compare those values. That is why the failures look so varied.

Fixing the SQL Server Side

-- a default instance
EXEC sp_dropserver 'OLDNAME';
GO
EXEC sp_addserver 'NEWNAME', 'local';
GO

For a named instance you pass both parts:

EXEC sp_dropserver 'OLDNAME\SQLPROD';
GO
EXEC sp_addserver 'NEWNAME\SQLPROD', 'local';
GO

Then restart the SQL Server service. The new value is not picked up until it starts, so skipping the restart makes people think the commands failed.

SELECT @@SERVERNAME AS sql_thinks_it_is;

Run that again after the restart. It should now agree with Windows.

When sp_dropserver Refuses

You will get an error if the old name is still referenced as a remote or linked server. Look first:

SELECT name, product, provider, data_source, is_linked
FROM sys.servers
ORDER BY server_id;

Server id 0 is the local server. That is the row you are changing. Anything else pointing at the old name needs handling separately, including replication, which does not let go quietly.

If the instance is a replication publisher or distributor, stop. Replication stores the server name in many places. A rename means removing and rebuilding it, not patching it.

What Else Still Holds the Old Name

This is the part people miss, and it is why the fix sometimes seems not to work.

Database mail profiles and their accounts. Agent jobs with the old name written into a step. Maintenance plans, which store a connection with the name inside it. Those are the most common leftover. And linked servers pointing back at the machine itself.

SELECT j.name AS job_name, s.step_id, s.step_name, s.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE s.command LIKE '%OLDNAME%' OR s.server LIKE '%OLDNAME%';

Outside SQL Server there is more. Application connection strings, ODBC data sources, monitoring agents and firewall rules. Certificates issued to the old name. Any SPN registered in the domain. Kerberos fails in a confusing way when the SPN still names the old machine.

The SharePoint Side

SharePoint has a supported way to point a farm at a renamed server. It uses an alias, so the farm never learns the new name at all. That is usually the smaller job.

A client alias on each SharePoint server maps the old name to the new one. The farm keeps asking for OLDNAME. The alias sends it to NEWNAME. Nothing in the configuration database changes.

That is a legitimate permanent answer, not only a workaround, and for a large farm it is often the right call.

What I Would Do Differently Next Time

Decide the name before installing SQL Server, because renaming afterwards is never free.

Better still, have applications connect through an alias or a DNS name from the start. Then the day the hardware changes, nothing else has to.

A rename is not one change, it is the name of a thing that several other things wrote down.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.