Re: Error for a job that uses a MSDASQL linked server
- From: Pasquale <Pasquale@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 19 May 2009 07:48:02 -0700
The job has other steps (that use SSIS pkg).
I think it isn't possible to render this credential the owner of the job.
Thanks
"Tibor Karaszi" wrote:
The credential and agent proxy should be defined on the server where.
the job is, i.e., on the server where you define the linked server.
Then you can set this credential as owner for the job.
--
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:87C6054B-DA29-453D-AB5F-3D8B16AA1028@xxxxxxxxxxxxxxxx
mmmh ok.
You think that I must create a proxy with a Windows credential in
order to
execute
the job step that calls the remote stored procedure saved on SQL1.
The related login has to be present in SQL1 and SQL2. Which server
role
to assign to this new Windows login? Other changes?
I remember that the remote procedure has an OPENQUERY that uses a
MSDASQL
linked server (vs AS/400) on db server SQL1. Thanks.
(why in the test environment all function with simply Public and db
creator
as server roles for mySQLAccount?)
"Tibor Karaszi" wrote:
No, I'm not sure. I was only pointing to a direction which *might*
hold the answer.
--
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:E5C91AF2-002D-44A5-AA7E-3E66CAA17B75@xxxxxxxxxxxxxxxx
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
- References:
- Error for a job that uses a MSDASQL linked server
- From: Pasquale
- Re: Error for a job that uses a MSDASQL linked server
- From: Tibor Karaszi
- Re: Error for a job that uses a MSDASQL linked server
- From: Pasquale
- Re: Error for a job that uses a MSDASQL linked server
- From: Tibor Karaszi
- Re: Error for a job that uses a MSDASQL linked server
- From: Pasquale
- Re: Error for a job that uses a MSDASQL linked server
- From: Tibor Karaszi
- Error for a job that uses a MSDASQL linked server
- Prev by Date: Re: Named Instance Connectivity SQL 2008/2000 Same box
- Next by Date: Re: Raising up numbers of Locks Request
- Previous by thread: Re: Error for a job that uses a MSDASQL linked server
- Next by thread: Re: Error for a job that uses a MSDASQL linked server
- Index(es):
Relevant Pages
|
