Re: Error for a job that uses a MSDASQL linked server

Are you sure?
For each proof on SQL1 and SQL2 production servers it is necessary a
customer authorization.
Thanks

"Tibor Karaszi" wrote:

Perhaps you need to setup a credential (CREATE CREDENTIAL) and an
Agent Proxy for that login?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Pasquale" <Pasquale@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D7531F7-F234-4930-B8FF-2015D548B1BB@xxxxxxxxxxxxxxxx
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