SQL Server job failure - When node fail over

Question

SQL Server job failure - When node fail over

sivaprasad

SSCertifiable

Points: 6183
More actions
March 19, 2005 at 11:21 am

#87016

I have a setup of SQL Server 2000 Active \ Passive Cluster on Windows 2003 Environment. Maintenance plans were created when the Node-1 was active.
Two weeks back the SQL Server cluster service failed over to Node-2, where in all other services are remain in the Node-1.
But the backup jobs and all the other maintenance jobs are failing on Node -2 with the following error.
"The job failed. The owner () of job DB Backup Job for DB Maintenance Plan 'UserDB Maintplan' does not have server access."
But all these jobs were owned by Windows Domain account which is member of the 'Local Administrator Group'.
On workaround , I changed the job owner to a 'SQL Server Login' and the jobs
are running successfull now.
Is anyone faced the same kind of error?
Appreciate your views and ideas on what could have casued this problem.
Thanks

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

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

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

I remember this to be a post sp3 bug where jobs could not run under windows account but i couldnot find the fix for it. i will post it if i find it.

sa24 SSCrazy Eights Points: 8027 More actions · Answer 2

Below is the closest article i found.

http://support.microsoft.com/kb/825042

I have my test server on 8.00.878 and the issue is fixed on that one. The rest of the servers are on 8.00.818 and they have an issue of job failing when you run it under windows account.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Have you checked active directory ? Specifically the 'Account' tab, 'Log On To' button ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

sivaprasad SSCertifiable Points: 6183 More actions · Answer 4

Rudy,

Have you checked active directory ? Specifically the 'Account' tab, 'Log On To' button ?

Can you elobrate on what do I need to check on 'Active Directory" and the reason?

Thankx

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

In AD you have the ability to 'limit' the servers that a domain user account can log into (i.e only server_a & server_b or all servers except server_c). The default is no restrictions on 'log on to' in AD.The path mentioned above will get you to where you need to be (maybe you need an Domain Admin). So your service accounts and scheduled task JOB owners that are domain accounts need to be allowed to 'log in to' both physical servers in the cluster.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

sivaprasad SSCertifiable Points: 6183 More actions · Answer 6

Hey,

I checked and found the SQL Service accounts have "Log on" previleges on the both nodes.

- Sivaprasad S

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Does the owner of the JOBs created by the Maintenance plan have the same authority to log on to both servers ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."