How to change server name in SQL 2005? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to change server name in SQL 2005?

Author

Topic

maranbe
Starting Member

28 Posts

Posted - 2007-01-10 : 23:52:43

Hi Folks,

How do I change the computer name of a server running SQL Server 2005?
I would appreciate any help. Thanks.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

monty
Posting Yak Master

130 Posts

Posted - 2007-01-11 : 01:20:50

right click on my computer icon>>click properties>>click computer name tab>>click change

to change accordingly

its me monty

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-11 : 06:51:02

"right click on my computer icon>>click properties>>click computer name tab>>click change
to change accordingly"

This is NOT the only action you have to do. There are some internal settings within SQL Server which also need to be changed. Search here for advice from TKizer re same.

maranbe
Starting Member

28 Posts

Posted - 2007-01-14 : 21:15:27

could you please point me to the thread describing my concerns? I have only found steps in sql 2000 and 7 but none for 2005. really need to know if i can just drop the database and add a new database would suffice the change. Looking at the logins, you will find the ff. users:

X226\SQLServer2005MSFTEUser$X226$MSSQLSERVER
X226\SQLServer2005MSSQLUser$X226$MSSQLSERVER
X226\SQLServer2005SQLAgentUser$X226$MSSQLSERVER

note that x226 is the old machine name which i already changed to a new name. Can i just delete these or rename them to reflect the new machine name? please help. thanks.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-15 : 06:44:18

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67379&SearchTerms=rename,server
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72139&SearchTerms=rename,server

are 2 starting points.

maranbe
Starting Member

28 Posts

Posted - 2007-01-15 : 20:47:46

Many thanks Andrew but i still have to know what to do with those default users created when i first install sql.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

amnhdba
Starting Member

3 Posts

Posted - 2010-04-22 : 15:00:52

I ran into the same problem and had to create documentation for future reference. Here is an excerpt in regard to renaming the default logins for Full Text Search, MSSQL and SQL Agent:

4. On the new server, there are 3 logins that control the SQL Server services that need to be dropped and recreated. This is because the current Windows SID (i.e. Security Identifier) refers to the SID of the account on the old server. The logins are actually members of Windows local groups created by SQL Server setup. The local groups also need to be renamed to reflect the new server name. Restart each service after the change is applied to verify the change was successful.

Full Text Search Login Name =
<old_server_name>\SQLServer2005MSFTEUser$<old_server_name>$MSSQLSERVER

4.1 Drop Full Text Search Login

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<old_server_name>\SQLServer2005MSFTEUser$<old_server_name>$MSSQLSERVER')

DROP LOGIN [<old_server_name>\SQLServer2005MSFTEUser$<old_server_name>$MSSQLSERVER]

4.2 Recreate Full Text Search Login

CREATE LOGIN
[<new_server_name>\SQLServer2005MSFTEUser$<new_server_name>$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

4.3 Rename Windows Local Group for Full Text Search

• From Administrator Tools->Computer Management->Local Users and Groups->Groups, select the group name that refers to the old name of the local Full Text Search security group (i.e. SQLServer2005MSFTEUser$<old_server_name>$MSSQLSERVER
• Right-click the group, select Rename and replace the old server name with the new one

4.4 Restart the Full Text Search Service
From SQL Server Configuration Manager->SQL Server 2005 Services, right-click the Full Text Search service and select Restart.

5. SQL Server Service Login Name =
<old_server_name>\SQLServer2005MSSQLUser$<old_server_name>$MSSQLSERVER

5.1 Repeat steps outlined in sections 4.1 – 4.4 for the SQL Server Service login.

6. SQL Agent Service Login Name =
<old_server_name>\\SQLServer2005SQLAgentUser$<old_server_name>\$MSSQLSERVER

6.1 Repeat steps outlined in sections 4.1 – 4.4 for the SQL Server Agent Service login.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources