November 7, 2011 at 7:32 am
... but if I run it in the dev environment, it works just fine.
I've got a package, running on DB04 that references DB03 using OLE_db connector (same error with SMO connectors). When I run it in BIDS, it works just fine. When I call the package from a SQL Agent job, whether it's referencing the SSIS Store, or a .dtsx file, I get the attached error.
Both servers are set up to use Mixed Mode authentication. I've 3 different "protection level" settings, and they had no effect on the error received.
The connection objects within the package are set to use Integrated Security. The SQL Agent Service is running under the LOCAL SYSTEM user. The job owner is MYDOMAIN\me (I'm admin), but I've also tried NTAUTHORITY/SYSTEM and "sa". Why does the error output mention the user: "DENVER\PRODDB04$" ???
It's blowing my mind that I can't simply reference another server from an Agent launched job. Is their network on lock down or something? What am I missing? Someone told me that I have to make all the other servers LINKED servers before this'll work... can that be right?
SSC, please help!
Here's the error text for copy/paste-ability...
Message
Executed as user: PRODDB04\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 6:58:00 PM Error: 2011-11-04 18:58:00.74 Code: 0xC0202009 Source: PVM_launch Connection manager "DB03_ole" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DENVER\PRODDB04$'.". End Error Error: 2011-11-04 18:58:00.74 Code: 0xC020801C Source: Tables and data from db03 to db04 account [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB03_ole" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-11-04 18:58:00.74 Code: 0xC0047017 Source: Tables and data from db03 to db04 SSIS.Pipeline Description: component "account" (1) failed validation and returned error code 0xC020801C. End Error Error: 2011-11-04 18:58:00.74 Code: 0xC004700C Source: Tables and data from db03 to db04 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-11-04 18:58:00.74 Code: 0xC0024107 Source: Tables and data from db03 to db04 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:58:00 PM Finished: 6:58:00 PM Elapsed: 0.39 seconds. The package execution failed. The step failed.
November 7, 2011 at 8:49 am
My guess would be that the security instance the SQL Agent is running under on DB04 does not have permission to access DB03. I would further guess that DENVER\PRODDB04$ is the service account the agent is using. The solution would be to grant the appropriate access to the service account on DB04 to the database on DB03
November 7, 2011 at 1:37 pm
Daniel, thanks for the reply. I would have expected a similar configuration for the SQL Server Agent service account, but in fact, it's set to use the Local System account (see new attachment).
1) Does it still make sense that the user would show up as a domain user "DENVER\PRODDB04$"?
2) How would I explicitly give the Local System user on DB04 permissions on DB03?
November 8, 2011 at 7:00 am
FIXED --> The SQL Agent Service was running as Local System, hence why it didn't have any permissions to the other network servers. I devoted a domain user to the service, and gave that user the minimal permissions needed to access the other SQL Servers on the network, and that did the trick.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply