0

I have an extremely simple SSIS package on my SQL Server 2017 database instance, running via an agent job. I have created a credential and proxy account to run the integration service job. If the job owner is 'sa' the job will run fine. however if I change the job owner to the service account I need to have I get the following error:

The job failed. Unable to determine if the owner (THE_FUND\xyz_svc) of job MYPITAJob has server access (reason: Could not obtain information about Windows NT group/user 'THE_FUND\xyz_svc'<c/> error code 0x5. [SQLSTATE 42000] (Error 15404)

Additionally the service account is a local admin on the server, and sysadmin in sql server in an attempt to remove possible permission issues.

I created a credential using the service account as its identity. Then created a proxy account under SSIS Package Execution referencing the credential i created.

Would love to know what I am missing.

SSISPissesMeOff
  • 412
  • 1
  • 5
  • 15
  • The package has two steps, the job has one? step which is "run package?" Do any packages run successfully on this server? – billinkc Aug 04 '20 at 21:30
  • the job has 2 steps the package only has one i will correct that in my initial post. Yes every job runs fine as long as the job is owned by 'sa' if the job is owned by my service account (or any service account) it fails. – SSISPissesMeOff Aug 04 '20 at 21:43
  • Oh, so that's interesting. What about other job types that get run as the service account and not SA, do they fail as well? Because that smells like an active directory type of error https://stackoverflow.com/questions/1234570/could-not-obtain-information-about-windows-nt-group-user – billinkc Aug 04 '20 at 21:47
  • yes everything fails unless its owned by sa. It can be run as whoever. I thought there was issues with my credential maybe or proxy account i was using in the agent job but it does not seem so. – SSISPissesMeOff Aug 04 '20 at 21:57
  • 1
    @billinkc - thanks for pointing me in the right direction. It indeed was an Active Directory issue. I was able to finally find it when i did a lookup and the account as well as the proxy account I was trying had a NULL OU. Once dropped and recreated, this worked just fine. – SSISPissesMeOff Oct 25 '20 at 23:03
  • Glad to hear it. Guess you gotta change your handle to ADPissesMeOff ;) – billinkc Oct 26 '20 at 01:29
  • @billinkc dont tempt me. if AD begins pissing me off as much as SSIS i will change it for sure. it has a long way to go :) – SSISPissesMeOff Oct 27 '20 at 17:57

1 Answers1

1

As it turns out the issue was related to Active Directory. The service account had no registered OU. Once the service account was removed, and recreated the OU was populated and everything with the SQL Server jobs started working. I guess sometimes SQL Server is not to blame. Once SQL Server was able to find the information it needed from AD, problem solved.

SSISPissesMeOff
  • 412
  • 1
  • 5
  • 15