Solved

Job Agent does not run, error ID 208

Posted on 2013-01-08
6
314 Views
Last Modified: 2013-01-18
OS: MS Server 2003 SO2
SQL: MS SQL 2005 SP2
SQL Server Management Studio: 9.00.3042.00

Summary: The stored procedure executes fine. When we set the stored procedure to execute periodically in a job agent, the agent fails. We selected the Agent to report on error (OS Event Viewer reports the standard error ID 208).

Detailed Description: In MS SQL Management Studio-SQL Server Agent-Jobs-'MyJobName'-RightClick-Properties I am configuring my SLQ job.
In the job properties under Steps-Step1-Edit-General I have following
Step name: Step 1
Type: Transact-SLQ scripts (T-SQL)
Run as: blank (!!!)
Database: Runtime (this is my custom database name)
Command: EXEC dbo._SubmersiblePumpRuntime (this is my stored procedure name)

When I click on PARSE I get a popup that tells me that parsing was successful.

In the job properties under Steps-Step1-Edit-Advanced under Run as user: I selected wide variety of users. I did try 'sa', local OS user who is also a member of the user group for the local OS user group 'SQL Server Agent'. Nothing works. When I select right click-Run from step ... and run from step 1, the job produces same error. It just fails with ID 208.
Again, the stored procedure works just fine. When we put the stored procedure to run in an agent, the agent cannot execute the procedure.

What is wrong with my setup?
0
Comment
Question by:uripapuk
  • 4
  • 2
6 Comments
 

Author Comment

by:uripapuk
ID: 38756782
These are screenshots from existing configurations.
20130108-112752.jpg
20130108-112727.jpg
20130108-134703.jpg
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38757677
As you already mentioned that you have tried with all the logins using yours as job_owner in the wizard, the next step will be to include the schema name in the procedure execution. So, if schema is dbo, try running

exec dbo.abc_prc
0
 

Accepted Solution

by:
uripapuk earned 0 total points
ID: 38772558
The existing configuration does have already command line EXEC dbo.StoredProcedureName.

This is not the solution.
The solution is as follow.

1. The custom database into which the stored procedure is written needs to be linked with users in the MS SQL security. Go to SQL Dev Studio and under Server Objects - Linked Servers - INSQL (this is the name of the custom software that created and uses the database) right click and select Properties. Under properties select Security. Under Security select Be Made using this security content and in the user name and password input fields add a user that has permission to execute stored procedures with the database targeted by the stored procedure. During execution the stored procedure will require rights to access the target database.

2. In the scheduled job go to step 1 where you scheduled your desired stored procedure to run. In the bottom of the window select Edit. In the new popup window select General. On the right make sure that the input field Run as is blank. In the same popup window select Advanced. In the right on the bottom right corner make sure that Run As User is blank. This was the critical point we had to overcome. Any combination of authorized users did not allow us to run the stored procedure via scheduled job. The user fields shall be blank.

In normal case you should have authorized user to be able to run the procedure within the job. Apparently the linked servers (although both servers are local) is sufficient to authenticate.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Expert Comment

by:TempDBA
ID: 38772592
Thanks for sharing your solution. But you already mentioned that you have used all the logins including the one using which you are executing the stored procedure, didn't you?
0
 

Author Comment

by:uripapuk
ID: 38784522
TempDBA have noticed correctly that we have tryed every possible user. But we did not try NO user at all in the tab Advanced for properties of Step 1. We have been thinking that there must be a credible user to run the task inside the SQL Job. Apparently, once you establish trusted link between both servers, you do not need to run the job under specific user.

It is correct that due to lack of knowledge I did not reveal all details on the case in the first place. In my explanation I did try to reveal all I know so that the solution is clear and reusable.

Do you believe we have to award points to TempDBA?
0
 

Author Closing Comment

by:uripapuk
ID: 38792314
We believe that there might be other curcumstances when different security configurations will be required in order to run this Job Agent successfully. What we found is one particular case. The very presence of authenticated user input field (Run as) means that Microsoft expects some user to be entered there. We found that only with the NO user (blank) this Job Agent works.
The author of the questions is the actual person providing the solution. No expect was able to answer this question.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now