printmedia
asked on
SQL Server 2008 job fails when deleting records in Linked Server
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.
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.
ASKER
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".
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".
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.