February 11, 2016 at 1:31 pm
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 los 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.
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.
This is the connection string from my package:
Data Source=MyServer;Initial Catalog=MyDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
Thanks,
Scott
February 11, 2016 at 3:43 pm
Could be lots of things...this is a such a common class of problem Microsoft posted a KB article:
SSIS package does not run when called from a SQL Server Agent job step
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 11, 2016 at 5:16 pm
thanks but that is a link to this page.
February 11, 2016 at 6:03 pm
I have had that happen a couple times. i think it is a bug in this site.
https://support.microsoft.com/en-us/kb/918760%5B/url%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 12, 2016 at 7:01 am
Thanks for the link.
My inexperience may show here with my questions/responses but in response to the options presented in that kb article:
I am not really sure how the proxy would work in my situation. I have a package, that while I created it on another machine, currently has the same CreatorComputerName, and CreatorName as the computer I am running it on and the NT_AUTHORITY\SYSTEM account which is the same account the SQL Server Agent service is logging in with. Isn't that the whole point of the proxy account is to run the job with the appropriate permissions? It would seem as if I have the permissions with the system account.
Same with methods suggesting to change the password in the command line or creating a configuration file. Isn't the whole point of these options to override the settings in the package with proper credentials? I am not sure what I can override the system account with that will work if the package has that account as the creator.
February 12, 2016 at 8:10 am
scotdg (2/12/2016)
Thanks for the link.My inexperience may show here with my questions/responses but in response to the options presented in that kb article:
I am not really sure how the proxy would work in my situation. I have a package, that while I created it on another machine, currently has the same CreatorComputerName, and CreatorName as the computer I am running it on and the NT_AUTHORITY\SYSTEM account which is the same account the SQL Server Agent service is logging in with. Isn't that the whole point of the proxy account is to run the job with the appropriate permissions? It would seem as if I have the permissions with the system account.
Same with methods suggesting to change the password in the command line or creating a configuration file. Isn't the whole point of these options to override the settings in the package with proper credentials? I am not sure what I can override the system account with that will work if the package has that account as the creator.
As far as I know (despite what you mentioned in an earlier post), those Creator properties are purely informational.
If you want to confirm suspicions regarding security (and this is a development machine), try changing the SQL Agent service such that its logon credentials match yours (ie, they use your domain user name and password). Then restart the SQL Agent service and then try re-running the SQL Agent job which executes the package.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2016 at 8:32 am
Thanks, I did that and the job completed successfully.
I will admit I am not sure how that helps me.
February 12, 2016 at 8:39 am
scotdg (2/12/2016)
Thanks, I did that and the job completed successfully.I will admit I am not sure how that helps me.
It's helpful in that it proves that this is purely a permissions problem. So focus on your service accounts / proxy accounts and their rights.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2016 at 9:18 am
I guess I didn't see how it helped because I had already made sure that the system account login in SQL server has access to the database. I also created a credential from my user account and a proxy but it still does not work.
February 12, 2016 at 8:45 pm
Phil Parkin (2/12/2016)
As far as I know (despite what you mentioned in an earlier post), those Creator properties are purely informational.
That is my understanding as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 12, 2016 at 8:51 pm
scotdg (2/12/2016)
I guess I didn't see how it helped because I had already made sure that the system account login in SQL server has access to the database.
Just to clarify, it's the SQL Server Agent service account you need to be worried about, not the SQL Server service account. Just checking...
I also created a credential from my user account and a proxy but it still does not work.
That only helps if you linked the proxy account to a job step. Can you script out and post the Credential (minus passwords of course), the Proxy Account and the Agent Job that uses it so we can have a look?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 16, 2016 at 6:19 am
Yes, thanks Orlando.
Originally I was trying to run the package through a stored procedure using xp_cmdshell. I believe the stored procedure uses the SQL Server Service. I then tried executing the package from a job which uses the agent service. Both services are set up to log in as the local system account.
February 16, 2016 at 6:57 am
Thanks for clarifying. My understanding is that processes running as "local system" may have trouble accessing network resources.
You've proven it works when the services are running as your account. Not to mention that it is a best practice, it also alleviates lots of doubt around issues like this, so I recommend you run your services as a low privileged dedicated service account amd grant that service account permissions where required.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 16, 2016 at 7:46 am
I thought about that and yes it did work when I used my credentials on the dev machine but it also worked as the local system account. I thought it would be taking a leap to assume that it would work with my credentials but I guess it is the next logical step.
February 22, 2016 at 10:04 am
So I wasn't able to change the account until this morning. I changed the account to use my credentials as I did on the QA machine while testing. As expected I got the same result.
So I have tried the following (the package runs in every circumstance on the QA machine):
I created proxy using my account
I changed the SQL Server Agent Service account login (for trying to run as a job)
I changed the SQL Server Service (for trying to run as a SP)
I checked all of the SQL login permissions for my account and the local system account
I ran a trace on the jobs and did not see any code being executed related to the package being run
I checked the XML of the package and there is no reference to any other server
I tried using a configuration file
I tried changing the connections string provider from SQLNCLI.1 to SQLOLEDB.1 and back again
The package is on the same machine as the database
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply