Update

From: Dane Dickey (dane.dickey@bankofamerica.com)
Date: 01/14/03 

From: "Dane Dickey" <dane.dickey@bankofamerica.com>
Date: Tue, 14 Jan 2003 07:36:28 -0800

I am getting the same error on my jobs that run remote
queries against a linked server (DB2 UDB). On the Linked
Servers security properties, I am using the "connections
will be made using this security context" option with an
ID and password for the linked server. The problem seems
to be that the job owner is a SQL Server login that is not
a sysadmin (just db_owner). If I switch the owner
to "sa", the job runs fine. How do I get my job to run
under the db_owner login?

Thanks

Dane

>-----Original Message-----
>Guys,
>
>Seems to have something to do with security. I now get
>the following error:
>
>Executed as user: QSECOFR. Remote access not allowed for
>Windows NT user activated by SETUSER. [SQLSTATE 42000]
>(Error 7410). The step failed.
>
>I have been attempting variations on users. The attempt
>above was to impersonate the AS400 QSECOFR User. A
>Windows domain user and a SQL user, both mapped to remote
>users and not mapped, with a default security context,
all
>seem to have failed. I gather that the SQL Agent Job
>attempts to log on with the "Owner" account, and this
>account needs to be mapped to an AS400/DB2 account.
>However nothing I've tried seems to work. Am I missing
>something really simple??
>
>Thanks,
>Simon.
>
>
>
>
>
>
>>-----Original Message-----
>>Guys,
>>
>>I have a linked server, a DB2 database on AS400 using
>ODBC
>>with a connection string.
>>
>>I also have several stored procedures that reference the
>>linked server, and these work via Query Analyzer. They
>>upload data from the DB2 database into the SQL database.
>>
>>
>>I *want* to automate the process by creating a Server
>>Agent job that executes the stored procedures each
>night.
>>Problem is that it doesn't work. I suspect it may be
the
>>security login's, or perhaps Server Agent doesn't just
>>like Linked Servers (though I've found nothing to
support
>>this.)
>>
>>Thanks,
>>Simon.
>>
>>
>>P.S. For those who may need this info the error I get
is:
>>
>>SQL Server Scheduled Job 'TestUploads'
>>(0xD282E2D556F5D14177C991D2FA4CCF9E) - Status: Failed -
>>Invoked on: 2002-12-19 08:40:33 - Message: The job
>>failed. The Job was invoked by User SimonV. The last
>>step to run was step 3 (UploadTheStates). The job was
>>requested to start at step 1 (ResetTables).
>>
>>
>>And the error occurs on the following line:
>>
>>DECLARE curStates CURSOR FOR
>>SELECT * FROM OPENQUERY(<Linked Server>, 'SELECT
>>StateCode, StateName FROM States')
>>
>>
>>I have determined that it's not the cursor. I also have
>a
>>view defined with the OPENQUERY statement, and this
>>likewise dosen't work.
>>
>>.
>>
>.
>