Re: Error for a job that uses a MSDASQL linked server
- From: "Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 20 May 2009 16:22:33 +0200
Yes, that should be enough.
--
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:41340BC4-F63D-44A1-ADB2-76925019B503@xxxxxxxxxxxxxxxx
Is it enough to define a credential and the related proxy maintaning the
actual job owner? 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
- Re: Error for a job that uses a MSDASQL linked server
- From: Pasquale
- Error for a job that uses a MSDASQL linked server
- Prev by Date: Side by Side migration SQL2000 - SQL2008 space requirements
- Next by Date: Insert
- 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
|
Loading
