Re: Stored Procedure Fails Only When Called By Agent

From: Erik Nelson (cybermud_at_excite.com)
Date: 05/07/04 

Date: Fri, 7 May 2004 15:55:19 -0500

Paul:

Thanks for the continued help. I am already mapping logins...let me provide
you with more detailed information as to the operations I am performing

User Information:
    Local Machine:
        LOCALMACHINE\Administrator/pass1
        LOCALMACHINE\myaccount/pass3

    Each Remote Machine:
        REMOTEMACHINE\Administrator/pass2
        REMOTEMACHINE\\myaccount/pass3

Job Information:
    Runs as LOCALMACHINE\myaccount
    executes StoredProc1, StoredProc2, StoredProc3
    Fails on StoredProc2 with error:
    Msg 17, Sev 16: SQL Server does not exist or access denied. [SQLSTATE
42000]

Stored Procedure 1:
    CREATE PROCEDURE StoredProc1 AS
    set nocount on
    --Create server connections
    EXEC sp_addlinkedserver '10.1.2.10', 'SQL Server'
    --Connections for all servers are added.
    EXEC sp_addlinkedserver '10.1.2.14', 'SQL Server'

    --Add logins for connections
    EXEC sp_addlinkedsrvlogin '10.1.2.10', 'false', NULL,
'REMOTEMACHINE\administrator' , 'pass2'
    --logins for all servers are added
    EXEC sp_addlinkedsrvlogin '10.1.14.10', 'false', NULL,
'REMOTEMACHINE\administrator' , 'pass2'

Stored Procedure 2:
    CREATE PROCEDURE StoredProc2 AS
    set nocount on
    --Drop & Re-Get data from table 1
    DECLARE @okay INT

    EXEC spPingServer '10.1.2.10', @okay OUTPUT
    IF (SELECT @okay) = 0
    BEGIN
    DELETE FROM table1 WHERE source = 'this_remote_machine'
    INSERT INTO table1 (data,that,i,copy)
     SELECT data,that,i,need from [10.1.2.10].DB_NAME.dbo.Table1_remote
    END
    --this is done for each server
    EXEC spPingServer '10.1.14.10', @okay OUTPUT
    IF (SELECT @okay) = 0
    BEGIN
    DELETE FROM table1 WHERE source = 'this_remote_machine'
    INSERT INTO table1 (data,that,i,copy)
     SELECT data,that,i,need from [10.1.14.10].DB_NAME.dbo.Table1_remote
    END

Stored Procedure 3:
    CREATE PROCEDURE StoredProc3 AS
    --Drop servers & logons
    EXEC sp_dropserver '10.1.2.10', 'droplogins'
    --all connections are dropped
    EXEC sp_dropserver '10.1.14.10', 'droplogins'
    GO

I have tried executing the job as the local administrator, and the local sa.
I have not tried changing the administrator password on the local machine to
match the remote machines, but I could do that. I've verified that all this
information is correct by successfully running all the stored procedures in
order from query analyze, logging into the local server using
LOCALMACHINE\myaccount/pass3, the same account that owns the job.

Thank you for all the time you have spent on my problem--hopefully you can
see something here that I can't.

Erik

"Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message
news:OZXF$pGNEHA.1160@TK2MSFTNGP11.phx.gbl...
> Erik,
> my suspicion is that you are using impersonation in your remote server
> login. The sql server agent is the account that runs the job, and this
> account exists on the server with a different password. If this is the
case
> then you can use pass-through security by specifying the same password and
> username. Alternatively,you can map logins in the security section of the
> linked server setup - eg you can map all users on machine A to a single
user
> on remote machine B.
> HTH,
> Paul Ibison
>
>