[2005] Problems after changing domain and server name

[davidchardonnet]

Hello,

I have a SQL 2005 Server running in Windows Server 2003. I set it up, tuned it for performance, put a maintenance plan and it took me a lot of time to do all this. I had to migrate it in another domain, and i had to change its name.

The renaming is OK
But the maintenance plan fails and I get Event logs warnings and SQL Log errors. I don't know how to get rid of these errors. I did not change the 'sa' password, I don't understand these warnings...

I don't want to reinstall this server, but it's the only way out I can see, with my current knowledge.

Can you help me?

Thank you

David

**********************************************************
Event log:
--------------------
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 06/07/2007
Time: 20:00:01
User: N/A
Computer: FRSAC090
Description:
SQL Server Scheduled Job 'Plan REPORTPG5' (0x0D64BE3E04B4CC4287DA78F2246FFCD1) - Status: Failed - Invoked on: 2007-07-06 20:00:00 - Message: The job failed. The Job was invoked by Schedule 1 (Plan REPORTPG5-Planification). The last step to run was step 1 (Plan REPORTPG5).

For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

----------------------
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 09/07/2007
Time: 14:44:10
User: N/A
Computer: FRSAC090
Description:
Login failed for user 'sa'. [CLIENT: 10.0.2.173]

For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 09 00 00 00 46 00 52 00 ....F.R.
0010: 53 00 41 00 43 00 30 00 S.A.C.0.
0018: 39 00 30 00 00 00 07 00 9.0.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...
**********************************************************

SQL Logs:
-------------------
Date 13/07/2007 11:15:48
Log SQL Server (Current - 19/07/2007 00:00:00)

Source Logon

Message
Error: 18456, Severity: 14, State: 8.
-----------------------------

 

[mutley1]

Not sure about 2005 (one of the experts will be able to help out on that!), but after renaming in 2000 you need to check originating_server in msdb..sysjobs. Check the name is the same as the server.

HTH,

M.

 

[mrdenny]

There will be a few things that you need to do after changing the servers name.

exec sp_dropserver 'OldServerName'
go
exec sp_addserver 'NewServerName', 'local'
go
update msdb.dbo.sysjobs
set originating_server = 'NewServerName'
where originating_server = 'OldServerName'
go

After that restart the SQL Service.

You probably have to go into the maintenance plan and resetup the connection to the server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[davidchardonnet]

It seems that the originating_server column in SQL Server 2005 has been replaced by originating_server_id, and it's not possible to update the name...

 

[mutley1]

Hi David,

When you say not possible, are you getting an error message when you run the update command? How are you trying to change it - directly or a script? Ad does the user you are logged in as have SA rights (or is the sa account)?

Cheers,

M.

 

[mrdenny]

You won't need to change the server_id. 0 is always the local server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com