Error for a job that uses a MSDASQL linked server

I have two servers with SQL Server 2005 Enterprise Edition 64-bit RTM
version, SQL1 (db server) e SQL2 (app server). On SQL2 I have created a SQL
Server linked server
to SQL1.
Moreover, on SQL 2 I have created a SQL Server Agent job with a step
that calls a remote stored procedure saved on SQL1. The owner of the job
is a SQL account, fe mySQLaccount; this login is used to access my db on
SQL1, too.
SQL Server Agent on SQL2 runs as NT AUTHORITY\SYSTEM.
MySQLAccount is the account for the SQL Server linked server to call the
remote stored procedures saved on SQL1.
The remote stored procedure (on SQL1) use a OPENQUERY that uses a MSDASQL
linked server, created on SQL1.
When I execute the job I have had this error for the step that calls the
remote stored procedure with the OPENQUERY:
executed as user NT AUTHORITY\SYSTEM. Cannot execute the query ... against
OLE DB Provider ... "MSDASQL" for linked server ... [SQL State 42000](Error
7320). The Ole DB Provider "MSDASQL" for linked server reported an error. The
provider did not give any information about the error. [SQL State
42000](Error 7399). The step failed.
This problem doesn't occur when for mySQLAccount assign the sysadmin as
a server role.
I have tried to reproduce the error onto a test environment (with two SQL
Server 2005 Enterprise Edition but with SP2) and the step that calls the
remote stored procedure functions normally also with Public and dbcreator as
server roles without
selecting sysadmin for mySQLAccount.
Any helps to solve this issue? Why this different behaviour?
Many thanks
.

Relevant Pages

  • Re: T-SQL is not quite there yet
    ... StoredProc1 does the same thing to insert data from StoredProc2. ... I assume a lot of this has to do with that the linked server may not ... SQL Server instance, one could surely wish that SQL Server had special ...
    (microsoft.public.sqlserver.programming)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)
  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C
    ... I also checked the individual patch levels for the .NET drivers, SQL Server ... The SQL Server is fully patched, however Windows Update reported that the OS ... Lock pages in memory -- I guess you might have taken care of it as well. ...
    (microsoft.public.sqlserver.clustering)
  • RE: migrating from wmsde to sql server
    ... Click Start, point to All Programs\Microsoft SQL Server, and then click ... then click New SQL Server Registration. ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • Re: Linked Server
    ... SQL instance to be able to delegate on your behalf to the 2nd SQL Server ... [Create Linked Server Object on Middle Server] ... set up its login to use delegation. ...
    (microsoft.public.sqlserver.server)
Loading