March 11, 2008 at 10:25 am
hello,
I have an SSIS job scheduled and been running perfectly for last 6 months. But suddenly sincelast week it stopped running and the job is being failed by throwing the following message:
Message
[298] SQLServer Error: 14516, Proxy (3) is not allowed for subsystem "SSIS" and user "domain\uid". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. [SQLSTATE 42000]
I tried sp_grant_proxy_to_subsystem and got the message that it already exists. I then tried sp_grant_login_to_proxy and then the job starts but keeps on running without actually doing anything.
I taled to me admin and was told that the password for the specific uid has not been changed.
Any idea folks???
March 12, 2008 at 4:31 am
I have also got this same issue.
Does anybody have any idea of a resolution?
Thanks
Steve
March 13, 2008 at 3:50 am
If it's been running ok for 6 months then I would guess something else has changed.
Has the account that runs the SQL Server/ the SQL Server Agent changed?
Could permissions have changed on something that the package uses? (Like a network share or something).
You could try re-deploying the package writing some trace statements to a table to help pin point the problem.
Those are my first thoughts on this. Hope they help 🙂
Good luck.
March 13, 2008 at 4:04 am
Thanks for your response.
My issue is the:
"Message
[298] SQLServer Error: 14516, Proxy (3) is not allowed for subsystem "SSIS" and user "domain\uid". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. [SQLSTATE 42000]
I tried sp_grant_proxy_to_subsystem and got the message that it already exists. I then tried sp_grant_login_to_proxy and then the job starts but keeps on running without actually doing anything."
If i run the job from ourtside of SQLAgent (ie: run from file system) it executes fine. When running within SQL Agent (scheduled job) it tries to run but fails - must be permissions but all looks OK. I try to use sp_grant_proxy_to_subsystem but as mentioned message received saying alrteady exists.
Credential is a non admin domain service account, proxy is using this non admin domain service account (different account to the package author).
Any further ideas?
Thanks
Steve
March 13, 2008 at 5:00 am
Have you checked the proxy's properties to make sure that it still has rights to execute an SSIS package? Does the login which owns the job have rights to the proxy? Also, check that the credential is correct and that it's login details haven't changed...
Kindest Regards,
Frank Bazan
March 13, 2008 at 5:21 am
I'd suspect someone has been tampering with the proxyaccount itself or that e.g. a password pollicy locked or revoked the user.
Check AD for the proxy user account that it hasn't been locked or revoked.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2008 at 5:25 am
Thanks Journeyman,
Yes the Proxy has permissions (has the check box selected) to both 'SQL Server Integration Services Package' & 'Operating System CMDExec'.
The proxy account principals are set to SQLAgentUser, SQLAgentReader & SQLAgentOperator as well as ProcessAdmin. The account that is being used by the credential is the same service/domain account as the job owner (althought this is a different account to the job creator/author).
Thanks
Steve
March 13, 2008 at 5:28 am
ALZDBA
I have just tried logging onto the domain using the 'credential' service account and it let me in fine so i know that the user credentials are correct.
I think it must be permissions local to SQL but cant see what. Can you think of anything within MSDB that i may have missed?
Whats strange is that the message regarding using sp_grant_login_to_proxy is being returned yet when i run this SP it says permission already set
Thanks
Steve
March 13, 2008 at 5:58 am
- What version of sql2005 is this ? (+ sp + cu please)
- has the package been modified lately ?
- did you try sp_revoke_login_from_proxy and grant it again ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2008 at 7:37 pm
Have you tried bouncing the SQL AGent service to make sure that the domain account this uses (assuming it's a domain account) is still valid and the password hasn't expired? If it's been six months, that sound like just long enough for a password to expire by default.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 14, 2008 at 3:29 am
Thanks Tim.
Its a production box so will need to schedule this for out of hours.
However i can confirm that the account thet the SQL Agent service is running under is the same account that the credential is using (and i am currently also able to log onto the domain using it - so it cant have expired). A bounce of the service is always worth a try so will do so next.
Steve
March 31, 2008 at 2:41 pm
Hello folks,
Thanks a lot for your info and tips.
Finally, it is working now for me. I tried different things as you have discusses here. But, at the last resort, I deleted the Credential and Proxy. Then recreated them - same name and properties. Set up the job to run as the same proxy account. And, everything started working like before!!!
No idea what caused such problem.... cheers...
-nero
April 30, 2009 at 11:24 pm
Most of the cases it might be permission issue. Even mine hte same and got resolved after that.
September 3, 2009 at 11:08 am
I had the same issue today. Everything was running fine for a long time and all of a sudden the job stopped working today. Just deleted and recreated the credential and proxy and now its working.
September 3, 2009 at 1:50 pm
- What version of sql2005 is this ? (+ sp + cu please)
- has the package been modified lately ?
- did you try sp_revoke_login_from_proxy and grant it again ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply