Sorry for starting another thread but I thought enough had changed since my other thread that it was warranted and to be perfectly honest I am at a loss on how to handle this and thought more people may read it.
After moving a package to another server I am unable to run it from a stored procedure using xp_cmdshell or from a job. The package exists in the file system and I added it to the SSIS Package Store. If I run it in BIDS it runs . If I run it from the package store it works. As soon as I try to run t from the job or the stored procedure I get the following errors (regardless of whether or not the job package source is file system or package store).
Error: 2016-02-11 15:10:29.80 Code: 0xC0202009 Source: Package Connection manager "MyServer.MyDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.". End Error Error: 2016-02-11 15:10:29.80 Code: 0xC020801C Source: Data Flow Task OLE DB Destination [39652] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyServer.MyDB" 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: 2016-02-11 15:10:29.80 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Destination" (39652) failed validation and returned error code 0xC020801C. End Error Progress: 2016-02-11 15:10:29.80 Source: Data Flow Task Validating: 100% complete End Progress Error: 2016-02-11 15:10:29.80 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2016-02-11 15:10:29.80 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1).
Both the SQL Server service and the SQL Server Agent service are using the local system account to log on.
I have changed the creator name and creator computer to the production server it has been moved to.
Data Source=MyServer;Initial Catalog=MyDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
I get the same result if I try changing to the provider to SQLNCLI.1.
I tried a proxy account using my username and the system account even though it seemed unnecessary. I set the username in the package connection which was blank to the system account as well.
Forgive my ignorance, but when you ask the "account the job is running under" are you referring to the job owner?
If so the owner is the system account. I have also tried changing it to "SA" and to my network login account which is how it is set up on my development machine.
No not the job owner. Look at the job step(s). What is set for Run As? If nothing, the job will be run by the account running SQL Server Agent. Often that account has no privileges in any database except msdb and master.
It is currently set to the SQL Server Agent account which is the system account. I did give that user in SQL access to the database I am trying to access. I also tried setting up a proxy to use my account to run the job.
I already tried. I set up a proxy using my account. Id did the same on the dev server and it worked properly. I double checked to make all the permissions were the same between the two accounts and they are.
...and the permissions for the proxy account (that is, your account) are identical on the DEV and PROD server? (they're usually not in my shop -- makes auditors nervous)
Check database mapping in the server logins and user effective permissions in the target database.
We haven't found it yet, but there is a difference between DEV and PROD (and IMHO, there should be)
I've actually never seen it be a permissions error. What I've seen it always be is not able to reach the SQL Server, meaning it never even attempted to login as it couldn't reach it.
What I would recommend is running a trace on the production server and the QA server, looking for login events. I suspect you've got a variable or connection somewhere in that package that is causing the error. I suspect your prod server is firewalled off and can't see the server that it is attempting to connect to. It might be a parameterized connection via a variable or even the default value that you are overriding.
I ran a trace on login events on both machines and while I don't have much experience reading these logs I don't see anything that looks out of the ordinary. They both look the same except for the fact that there are more entries on the QA machine.
The package and SQL Server are on the same machine so it couldn't be a firewall issue.
The only parameterize connection is to my source file.
Yes it can be a firewall issue if it's trying to connect to a different server, which is what I suspect is happening.
What I meant by running a trace on both machines is to do it while running the package on the prod server. So you have the package running on prod, but you are watching login events on prod and QA. Do you see login events from the package on either server? Don't run the package on QA, we only care about the prod package here.
But is it the code inside the package that is triggering those login events, or is it the job subsystem? Add more events to your trace to see what commands those spids run, such as RPC:Completed, SQL Batch:Completed.