SQL Agent and Domain Server

Question

Post reply

SQL Agent and Domain Server

R M Buda

SSCrazy

Points: 2225
More actions
August 25, 2006 at 2:14 am

#75045

Hi folks,
Our windows domain controller was down for an hour over the weekend for maint. When I came in on Monday many scheduled jobs had failed and others had run for a long time.
All servers are on SQL2K SP4. The SQL2005 servers had no issues.
This is the sort of message we got:
The job failed. Unable to determine if the owner (DOMXXX\UserYYY) of job JobZZZZ has server access (reason: Could not obtain information about Windows NT group/user DOMXXX\UserYYY'. [SQLSTATE 42000] (Error 8198))
I think I have seen this sort of thing before, and spoke to another DBA who believes that if SQL Server is running any job while the domain controller is down then it goes into a state where it can no longer communicate with the domain controller, even after it comes up again. In this state the SQL service requires restarting.
I have seen similar issues with mail servers - SQL loses touch when the mail server bounces.
I would like to find out if anyone else has seen this or if it is documented?
Thanks,
Renato

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

the problem is that these jobs are owned by a windows domain user !

They get authorised every time the job is launched by sqlagent.

If you can, change them to sa or a sql-user with then needed authorities.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

R M Buda SSCrazy Points: 2225 More actions · Answer 2

Thanks alzdba - I am changing the job owners where possible.

We also have problems with jobs that use linked servers relying on integrated auth.

I would like to hear if people have had similar experiences.

In other words do people use integrated auth in SQL 2000 and if so is there an acknowledged problem around losing communication with the domain controller after it has bounced.

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 3

I guess you better launch a new thread with title "linked server losing connection when domain controller bounced"

Maybe that will encourage some people to jump onto it