• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Job Agent does not run, error ID 208

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?
  • 4
  • 2
1 Solution
uripapukAuthor Commented:
These are screenshots from existing configurations.
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
uripapukAuthor Commented:
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.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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?
uripapukAuthor Commented:
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?
uripapukAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now