I work as ERP Manager in one of the leading company of Nepal. Recently, I came across a problem. The problem was that one of the customized process to upload was not running from the application. The process runs a DTS package with command (xp_cmdshell). So, IT people have to run it from back end by logging is SQL Server with username sa.
This was troublesome as IT people have to be there whenever user requires to run that process. The problem was also that, the front-end showed only a message like " Execution failed" and then stops. And from back-end no error comes. For months, the same method continued. And also we were not able to login SQL Query Analyzer with other logins as the password was encrypted by front-end application.
After some research, I was able to decrypt password. For that, I just changed password to same password from Enterprise Manager. After that, I was able to login with other logins or usernames besides sa. When I try to run from there, it showed some error message like this.
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
Now, I am able to google in internet for error message. After some research, I found solution in Microsoft Support site. The error was due to the fact that we donot login application with username sa and other users are not assigned to role of system administrator in SQL Server and database owner for concerned database. So, I assigned system administrator role and database owner to the user of the application. Finally, users were able to run the process from front-end.
The error message shown is genuine. Whenever we try to run shell commands, which have to run in command prompt, it should be run as local system or windows account. Only the users mapped to system administrator role of SQL Server can get access as windows local administrator and run the shell command. As the user of application was not mapped to that role, it will then search for any proxy windows account. As proxy account was also not found, the message was displayed.
From this, it is clear that the users which are not system administrator of SQL Server, can also get access to DTS package by setting up proxy account for that in SQL Server.
For that, go to SQL Server agent, Properties, Job System, uncheck the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and enter the proper username, password and domain of windows. This is also a good method. But it gives access to all users to run DTS package, which may not be appropriate.