April 8, 2016 at 6:54 am
I've only found one article on this regarding SQL 2012 and it's not too clear what the solution was. So I'm hoping someone here can help.
We've got our SSIS packages in MSDB this time around and the SQL Agent account (and our accounts) have sysadmin access to the system right now. But every time we try to run the job that executes the SSIS package, we get this error:
Could not load package "\MSDB\Folder\MyPackage" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "MyServer" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.
We tried granting ssisadmin in MSDB to the accounts, but it's still not working. Since these packages are in MSDB, I'm not sure how granting access to File System would help. And I'm not even sure where I would grant that access since the packages aren't on the file system.
Thoughts?
April 8, 2016 at 7:03 am
Does the job run if you reference 'localhost' instead of the server's name?
Also, what happens if you try to run the individual job steps as the user that the job is configured to run under in sql agent?
April 8, 2016 at 7:42 am
Manic Star (4/8/2016)
Does the job run if you reference 'localhost' instead of the server's name?
Nope. Now the error is:
Executed as user: SQLAgentAcct. The process could not be created for step 1 of job 0xDE9D8E9F101424409E0E06A9FE0D8E2B (reason: The system cannot find the file specified). The step failed.
Manic Star (4/8/2016)
Also, what happens if you try to run the individual job steps as the user that the job is configured to run under in sql agent?
Same thing. It's trying to run as the SQL Agent Account already.
We've even gone into DCom on the server to verify the account had Launch & Activate, Access, and Configuration permissions. They're all set for both local and remote.
Even tried switching the OS command line to Integration Services and got a slightly different error, but basically the same thing:
Connecting to Integration Services on the computer MyServer failed with the following error Access is denied
April 8, 2016 at 7:49 am
I'm beginning to wonder if this is a DNS problem. We have the instances aliased, but we don't currently have DNS records specifically referencing the aliased instance names. For instance, we have a server name that is ABC-Database222-X but the instance is aliased as MyServer. We did try replacing the instance alias with the server\instance and got the "Could not create process" error again.
Executed as user: AgentAcct. The process could not be created for step 1 of job 0xDE9D8E9F101424409E0E06A9FE0D8E2B (reason: The system cannot find the file specified). The step failed.
Sooooo.... We're really confused right now... The only file the job is trying to access is the log file. It has Full Control access (Local Administrator) to the SAN storage.
My coworker is logging into the server directly with the account so we can see if the error messages change when we're not working remote.
EDIT: And the answer is no, it doesn't.
April 8, 2016 at 8:37 am
Presumably the Integration Services service is running on the server and you are able to connect to it from an ordinary SSMS session?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 8, 2016 at 8:41 am
Brandie Tarvin (4/8/2016)
I'm beginning to wonder if this is a DNS problem. We have the instances aliased, but we don't currently have DNS records specifically referencing the aliased instance names. For instance, we have a server name that is ABC-Database222-X but the instance is aliased as MyServer. We did try replacing the instance alias with the server\instance and got the "Could not create process" error again.Executed as user: AgentAcct. The process could not be created for step 1 of job 0xDE9D8E9F101424409E0E06A9FE0D8E2B (reason: The system cannot find the file specified). The step failed.
Sooooo.... We're really confused right now... The only file the job is trying to access is the log file. It has Full Control access (Local Administrator) to the SAN storage.
My coworker is logging into the server directly with the account so we can see if the error messages change when we're not working remote.
EDIT: And the answer is no, it doesn't.
if you suspect a dns problem, connect with the IP and port directly
April 8, 2016 at 8:44 am
Phil Parkin (4/8/2016)
Presumably the Integration Services service is running on the server and you are able to connect to it from an ordinary SSMS session?
Yes. And when we try to run the packages manually from SSMS, the process appears to work.
April 8, 2016 at 8:47 am
Can i just add my two cents to this topic. I have never been able to run Agent jobs with any other account aside from a proxy account.
Not sure if that has been tried but you'll need to create a credential using a service account and then a proxy account using the credential's ID. Finally, permissions need to be set against the proxy account allowing to call integration services jobs. (SSIS Package Execution) which can be found under SQL Server Agent - Proxies
April 8, 2016 at 8:57 am
I've never faced this issue before. Please make sure that the integration services and the SQL Agent are using the same service account.
April 8, 2016 at 8:58 am
If you are storing your packages in MSDB, you could try exporting them to the file system somewhere local on the server and switching the sql agent job to call them from the file system.
April 8, 2016 at 9:01 am
jay81 (4/8/2016)
I've never faced this issue before. Please make sure that the integration services and the SQL Agent are using the same service account.
Please don't take this the wrong way, but if you've never faced this issue before, what makes you think this is the problem?
April 8, 2016 at 9:02 am
ttdeveloper (4/8/2016)
Can i just add my two cents to this topic. I have never been able to run Agent jobs with any other account aside from a proxy account.
We've never run our jobs with a proxy account. Never needed to. I'll keep this in mind for a last resort, but since other non-SSIS related jobs are running without issue, I'd rather not start messing with the accounts.
April 8, 2016 at 9:04 am
Manic Star (4/8/2016)
If you are storing your packages in MSDB, you could try exporting them to the file system somewhere local on the server and switching the sql agent job to call them from the file system.
I've been mulling that over. We were doing File System on the old servers, but when I updated the jobs, I made sure to get the command line from a faux IS step and copied over the old dtexec.exe command line.
So, yeah. It's the one thing we haven't tried yet.
April 8, 2016 at 9:12 am
Out of curiosity what OS is your server running on?
I had an issue that may be related to this sometime back and it had to do with our current migration to SQL Server 2012. May or may not be related but have a look.
April 8, 2016 at 9:21 am
Brandie Tarvin (4/8/2016)
ttdeveloper (4/8/2016)
Can i just add my two cents to this topic. I have never been able to run Agent jobs with any other account aside from a proxy account.We've never run our jobs with a proxy account. Never needed to. I'll keep this in mind for a last resort, but since other non-SSIS related jobs are running without issue, I'd rather not start messing with the accounts.
Except we don't have File System set up and configured so SSIS is looking in the wrong place for the packages and even changing the config file for SSIS isn't working. BLEARGH.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply