October 25, 2006 at 3:53 am
This is driving me nuts, and is getting urgent!!
I have a linked server
I have a DTS package that pushes data to the linked server
I have a SQLServer job that calls the DTS package.
I can run queries directly across the linked server using QA
I can run the DTS package directly (using my account)
The job FAILS whenever it tries to run the package with the following error:
Executed as user: CFVD\SQLServerService. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147217843 (80040E4D) Error string: Login failed for user 'CFVD\SQLServerService'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'CFVD\SQLServerService'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I've set up the linked server logins as follows:
LOCAL SERVER = AUSDBMAN01
REMOTE SERVER = PDDEV01.CORP.HOLDINGS.COM
DECLARE @rc int
DECLARE @rmtsrvname nvarchar(128)
DECLARE @useself varchar(8)
DECLARE @locallogin nvarchar(128)
DECLARE @rmtuser nvarchar(128)
DECLARE @rmtpassword nvarchar(128)
-- Set parameter values
EXEC @rc = sp_addlinkedsrvlogin@rmtsrvname= [PDDEV01.CORP.HOLDINGS.COM],
@useself= 'false',
@locallogin='CFVD\SQLServerService',
@rmtuser = 'pddev',
@rmtpassword='pdpw'
exec dbo.sp_helplinkedsrvlogin
'Linked Server''Local Login''Is Self Mapping''Remote Login'
AUSDBMAN01NULL1NULL
PDDEV01.CORP.HOLDINGS.COMNULL0pddev
PDDEV01.CORP.HOLDINGS.COMCFVD\sqlserverservice0pddev
The job is owned by CFVD\SQLServerService
CFVD\SQLServerService is the domain account that SQLServer and SQLServerAgent run under.
The remote login pddev exists at the remote server.
Can anyone think why the job is unable to connect to the remote server?
All help gratefully appreciated!!
Thanks
October 25, 2006 at 5:39 am
Right - I've fixed it - although I'm still puzzled...
As above, the CFVD\SQLServerService account (the local account) is mapped to pddev (remote account).
Local SQLServer Agent is running as CFVD\SQLServerService
I was under the impression that the LinkedSrvLogin mapping would allow the domain account to connect as the remote login, but it appears not.
The two servers are in different domains.
LOCAL Sever = AUSDBMAN01.CFVD.HOLDINGS.COM
REMOTE server = PDDEV01.CORP.HOLDINGS.COM
The only difference seems to occur when I try to run the DTS package via the Agent, rather than directly from my own account.
I fixed it by adding CFVD\SQLServerService as a login on the REMOTE SQLServer.
What puzzles me is that all the DTS package on AUSDBMAN01 needs to do is push data onto PDDEV01 - and the linkedsrvlogins should tell it do so using the pddev account on that server. So why did I need to add CFVD\SQLServerService as a separate login on the remote server?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply