June 8, 2016 at 8:16 am
The vendor rep and I have been banging our head against the wall on this one since May 31st. I'm hoping someone here has a suggestion.
A few months ago, we upgraded our vended app from 2008 to 2012 servers and have been testing. The month end job worked near the beginning of May except for an error on the final step due to a missing variable in the calling code. Fixed that and the last step ran wonderfully. This job has multiple CmdExec steps in it which always ran fine in SQL 2008 and ran fine initially in 2012. There are two problems that cropped up recently, though, that I believe are related.
The first issue is that the Test Team can no longer kick the job off in the client. The client says "SQL Job doesn't exist" in little red letters near the bottom of the screen. I've pushed that back to the vendor to explain why it existed three weeks earlier (we haven't restored the database or made any code changes that I'm aware of) but doesn't now. The second issue is our attempts to run the SQL Job via SSMS.
We've tried various ways of running this. I'm sysadmin in SQL and I have a server admin account (windows and sysadmin in SQL) that I used to RDP into the server to run it local to the machine. Both the tester and I are getting:
non sysadmins have been denied permission to run CmdExe job steps without a proxy account
We don't have a proxy account and would prefer not to set one up as it would cause discrepancies between our setup and the vendor's setup in their home system. They pretty much disavow support if our systems are too different. We've been able to kick this job off via SQL 2008 SSMS in the past without issue and without a proxy.
What things do I need to look at in our new environment to find or resolve this issue?
June 8, 2016 at 8:40 am
In MSDB, the following database level roles grant members various levels of permission to do things like monitor or start jobs.
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
https://msdn.microsoft.com/en-us/library/ms188283(v=sql.110).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 8, 2016 at 8:49 am
Eric M Russell (6/8/2016)
In MSDB, the following database level roles grant members various levels of permission to do things like monitor or start jobs.SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
https://msdn.microsoft.com/en-us/library/ms188283(v=sql.110).aspx
The testers already had those roles and the job failed the first time. Just for kicks and giggles, I granted those roles (just now) to the DBA sysadmin group and reran the job. No luck. Failed with the same error. I don't think this is a database permissions issue.
June 8, 2016 at 12:28 pm
Ahha! A clue.
The SQL Server Agent Service is running under an application domain account instead of our usual SQL Server Agent Service account for some reason. So I am now contacting the vendor rep to discuss and find out why we made that change (if it was just a troubleshooting attempt we forgot to reverse or if we were fixing another issue by changing the account).
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply