Solved

Job Agent does not run, error ID 208

Posted on 2013-01-08
6
320 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

806 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