Server and SQL Server Rename big problem

Question

Server and SQL Server Rename big problem

HighlanderAT

SSC Rookie

Points: 41
More actions
January 28, 2005 at 9:55 am

#162464

Hello to everybody!

I have a problem after I renamed my Server (Windows 2003) and then SQL Server.
Since then I am no more able to schedule Backup jops for databases.
I always get the message The job failed. Unable to determine if the owner (<Domain\Account Name> of job <job name> has server access (reason: Could not obtain information about Windows NT group/user '<Domain\Account Name>'. [SQLSTATE 42000] (Error 8198)).
I also can't just change the user in the SQL Server Agent for that job. Here I get the error 14274 cant edit a job that comes from an MSX server.
When I directly start the backup on the databes without scheduling the job it works.

Has anybody a solution for my problem?
PS: The Server is a standalone Server with no Domain.
Thanks Manfred

Viewing 7 posts - 1 through 6 (of 6 total)

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

I also can't just change the user in the SQL Server Agent for that job. Here I get the error 14274 cant edit a job that comes from an MSX server.

The reason you are getting this error is that under sysjobs in msdb, the name of your server is still the old servername. You will have to update the entry in Sysjobs table in msdb. You can run the following

Update sysjobs set originating_server = 'Server_Name'

I think there was a KB article for this also but i couldn't find it.

FYI-Updating System tables is not the best way but i'm not aware about any other workarounds.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

http://www.sqlservercentral.com/columnists/sjones/killthattarget.asp

HighlanderAT SSC Rookie Points: 41 More actions · Answer 3

Thanks for your information!

Itried it and now i can change the user from the old server which createt the job to sa

but it still does not execute the backup from the scheduler!

And why is still the old servername and administrator the owner even when i creat a new backup for schedule?

thx Manfred

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

When you renamed your server, did you rename SQL Server. i.e you need to run sp_dropserver 'Server_name' and than run sp_addserver 'new_servername'

HighlanderAT SSC Rookie Points: 41 More actions · Answer 5

of course i did that.

It still use oldservername\administrator to schedule jobs (thats kind of cracy)

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 6

I actually ran into this recently. Our DR server ($#$^#$ POS ) died the Friday before DR testing, so I grabbed our test server, threw on SQL and then fired it up as 'ProdDRLocSQL1'.

Get the DR server back up and running after the test, so I bring the server back and rename it to 'TestSQL'. Also go through the regstry for an installed program and change the name there. Won't fire the program, but the databases are working. (Heavily dependent on NT authentication.)

I found the solution from hunting on the internet, goes something like this:

From master: select * from sysservers

If you have two lines you need to run something like this. Also you can't have anyone in at the time because at minimum, you'll need to restart the SQL Server Service let alone the box.

sp_configure @configname = 'allow updates', @configvalue =  1
go
reconfigure with override
go

delete from sysservers
where srvid =0
go
update sysservers
set srvid =0
go
update sysservers
set srvname = 'TestSQL',
datasource = 'TestSQL'

Then shutdown and restart.

I think I found part of the solution on microsoft. All I know is that I have the location of the script memorized because it took me 3 !@%@#@$% more hours (on a Saturday ) to get my test box back to working condition.

Good luck.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.