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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
>>
>>



.

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: 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)
  • 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)