We help IT Professionals succeed at work.

Trouble scheduling DTS to run.

Belificus
Belificus asked
on
496 Views
Last Modified: 2013-11-30
I am confused about scheduling a DTS to run using Server Agent.  Server agent service runs under local system account.  
I have a DTS that when I open and choose Execute runs fine.
I logged on as sql admin and created a new DTS using my AD account.  When is it scheduled to run I get back an error
The job failed.  Unable to determine if the owner (AD\User) of job SW_to_DB has server access (reason: Could not obtain information about Windows NT group/user 'AD\User'. [SQLSTATE 42000] (Error 8198)).
 so it sounds like the service cannot access the AD.  Not sure what to do about that.
So I tried switching the job owner to a local account that I know has access to that DB.  When I do that it does not run at all.  I just get:
The job failed.  The Job was invoked by Schedule 33 (SW_to_DB ).  The last step to run was step 1 (SW_to_DB ).

I just want to schedule the DTS to run - but I am not sure where to chase this error.

Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

What account does SQL run under?

Regards
  David
can you change the sql server agent to run as a domain admin account and also the job to run as the same account and see if that works. also make sure if the job is trying to access any files/folders this doman account needs permission to those too.

Author

Commented:
dtodd - local service account.

uday - I do not want a domain servcie account with domain admin privledges.

There is a DTS present that was written by a thrid party vendor and THAT dts will run when scheduled.

Just not sure why.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi Belificus,

Why not a domain account? Surely you can create one with minimal priviledges? It doesn't have to be a domain admin account.

What we are suggesting is that it is easier to maniplulate a domain account and give it rights to different parts of the server, whereas I'd be a little more hesitant to do so with the local system account.

Regards
  David

Author

Commented:
Ok - creating a domain account now and setting privlidges.

Be back soon.

Thank you folks for helping me through this!

Author

Commented:
Interestingly enough, you guys are helping me find the problem in a round about way.
Turns out there is a DNS issue with this machine.
More to follow.
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I am planning on moving to SQL 2005 anyway, so the machine will get whacked soon enough.

Well, here's what I have now.  The revrse lookup for the subnet was added, but that did not help.
I had been scheduling jobs by right clicking on a DTS and choosing schedule.  This left me as the owner.
So, I opened up that job and copied the DTS ID.
Then I created a new job, set an SQL account as the owner, pasted the DTS ID and typed in the rest.
Runs like a champ.
I realize that I am NOT addressing the real issue here, but it works and I agree with dtodd that adding the machine back may work.  flushdns & registerdns did not help.
Anyway - THANK YOU for helping me dtodd and sticking with this issue.  I'd like to give you the points.
Cheers,
Douglas

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.