renaming a server

Question

renaming a server

Ian_McCann

SSCertifiable

Points: 5163
More actions
March 4, 2009 at 5:47 am

#126843

SELECT @@SERVERNAME AS 'Server Name' returns 'OldName'
I run
sp_dropserver [OldName]
GO
sp_addserver [NewName], local
GO
returns error
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'OldName'.
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'NewName' already exists.
I run
sp_dropremotelogin [OldName]
GO
returns error
Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 68
There is no remote user '(null)' mapped to local user '(null)' from the remote server 'OldName'.
I think the server has been renamed by our network guys while replication is set up.
Can anyone advise me how to drop the remote logins or linked logins and rename the server
Thanks in advance

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

JamieX Hall of Fame Points: 3763 More actions · Answer 1

I had an issue like this before so I did a quick search to find what helped me and I realized it was not the same. But I ended up here.

Check page 77

http://www.quest.com/quest_site_assets/whitepapers/sql_server_ebook_a_practical_guide_to_backup,_recovery,_and_troubleshooting_(part_2).pdf

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

Thanks Jamie.

Trouble is the physical machine has been renamed.

when I try to disable replication I get the following error

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'OldName'. (Replication.Utilities)

JamieX Hall of Fame Points: 3763 More actions · Answer 3

I havn't run into this issue before so I'm not sure if I can provide any more help.. but the next things I would try is create an entry in the hosts file on the server with the old hostname and point it to localhost or the server IP. Then try and remove it in SSMS on the server.

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

I was unable to resolve this.

Ended up reinstalling SQLSever while nobody was looking, and restored the databases.

I was lucky this time, we had a maintenance window, but if anyone knows how it should be done I would be gratefull if you could share it with me.

Thanks

Ian

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Was sql server restarted after the appserver command execution was done?

The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in each server.

http://msdn.microsoft.com/en-us/library/ms174411.aspx

MJ

chris.powell-959917 SSC-Addicted Points: 454 More actions · Answer 6

MJ hit on it, after you rename SQL Server, you MUST restart the server.

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 7

The server was renamed months ago, the server has been shut down and restarted twice in Feb, so i would have to go with in this instance that is not the answer.

Thanks all the same.

Ian

LuisLopez SSC Enthusiast Points: 184 More actions · Answer 8

Hi, this is my first post. I have 15 days as a DBA.:-D

I had the same issue and just solve it. I add the 'old server' alias in configuration manager, then disabled publication, and after that exec sp_dropserver and SP_ADDSERVER commands, drop 'old server' alias in configuration manager, and it worked. Sorry for my english. I hope this helps.

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 9

Welcome to the group.

Thanks for the tip, I will keep it in mind if this reoccurs

cruci8 SSC Veteran Points: 283 More actions · Answer 10

Try dropping the distribution database via the cleanup sp, this effectively runs the menu command 'Remove Publishing and Distribution' but bypasses the checking. Run

USE master

GO

EXEC sp_dropdistributor @no_checks = 1

GO

You should then be able to rename your SQL server with

EXEC sp_dropserver ''

GO

EXEC sp_addserver '', 'local'

GO

but don't forget to restart the services or it won't take effect.

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 11

The Quest e-book , page #77 was an excellent tip. I have a crappy network that seems to decide on it's own whether DNS works or not. I had to rename the server to the FQDN, and couldnt because of replication which had linked server logins.

Thanks Jamie !!

@STLSQLGuy Default port Points: 1476 More actions · Answer 12

If you are getting this error because of sticky points due to replication run this:

sp_dropdistpublisher 'bad server name'

that should clear up the name mismatch on replication and allow you to change your server name as you noted in the origional post.

thanks, ERH

duvvit SSC Enthusiast Points: 150 More actions · Answer 13

I tried the disable of the publishing, but it still would not allow the connection to the alias.

I ended up changing the server name back on the network, disabling replication, then dropping server and adding server as new name.

duvvit SSC Enthusiast Points: 150 More actions · Answer 14

THIS, worked for me:

select @@servername

sp_helpserver

sp_helpremotelogin 'xxxx'

--sp_dropremotelogin 'xxxx','DOMAIN\account'

--sp_dropremotelogin 'xxxx'

--sp_dropserver 'xxxx'

sp_dropserver 'xxxx', 'droplogins'

sp_addserver 'xxxx-new', local