?
Solved

SQL Server 2008 job fails when deleting records in Linked Server

Posted on 2014-03-10
3
Medium Priority
?
773 Views
Last Modified: 2016-02-10
Hi all.

I have a SQL Server 2008 SSIS package that deletes records from a table in a Linked Server. The linked server (myServerB) is SQL Server 2000.

When I run the package from Integration Services it completes with no errors and deletes the records from the table on the linked server. But when I create a new job from SQL Server Agent and then click on "Start Job at Step..." it fails with the following error:

Executed as user: DOMAIN\ServerName$. Code: 0xC002F210

Delete permission denied on object 'table', database 'mydb', owner 'dbo'. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).

I know the sql task works because when I run the package from Integration Services it completes successfully and deletes the records from the table, but when I try to execute the package from jobs in SQL Server Agent it fails. I have the owner as DOMAIN\administrator, why is it executing as DOMAIN\ServerName$?

How can I get the job to run the package successfully?

Thank you in advance.
0
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917884
>I know the sql task works
this is due to the difference that when you RUN the package from SSIS interactively, the run takes YOUR credentials;
while when the package is run in a JOB, sql server takes the credentials of the sql server agent, by default.

you can specify the "Run AS" in the sql server job's step to solve this for just this task, or to change the sql server agent's default startup login to a login that will have the permissions as needed.
0
 

Author Comment

by:printmedia
ID: 39917914
Thank you for your reply.

I'm in the Job Step but it does not let me choose a "Run As" user it defaults to: "SQL Server Agent Service Account".
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39917918
then you need to create some "sql agent proxy" first:
http://technet.microsoft.com/en-us/library/ms189064%28v=sql.105%29.aspx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

741 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