September 18, 2007 at 3:37 pm
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
September 19, 2007 at 4:19 am
I don't deal much with proxy accounts myself, but SQL Server 2005 does offer the new "Execute AS" capability, where you can force the stored procedure to execute under the permissions of a sysAdmin account.
But before you do that, make sure you know exactly what these SSIS pkgs and the XP_CmdShell scripts are doing. And make sure to lock them down so they can't be changed if the user who is running them decides to get nasty. Because if he changes those packages, or the SP, to deliberately screw with the system then you could be in serious trouble. EXECUTE AS can be a great help or a huge security hole, depending on how it's used.
BTW, you can look it up in Books Online for more details on how the command works.
September 20, 2007 at 11:38 am
What you have said is a good point...but the problem with EXECUTE AS is if the login is not an SA and he wants to run the stored procedure which has SSIS packages ...In this sever Iam an SA ..so in this case if he runs from his id using the EXECUTE AS clause and the SA id then does the stored proc run as an SA or does it run as the login???....In this case it has to run as the login itself but cannot be anyone else......And in the post you have even mentioned abt the locking down of the SSIS pkgs and the XP_CmdShell....how do we do this ....
Thanks and regards,
Raja.V
September 20, 2007 at 12:24 pm
Locking down the SSIS packages and such means, take everything away from him once it's developed. Do not allow him access to Integration Services on the Production server, make sure his login doesn't have the ability do import / export packages from the Integration Services engine and store the packages in the MSDB rather than a file system that he (or anyone else) may have access to.
As far as XP_CmdShell goes, it's harder locking down this, but the best thing to do is make sure share permissions are limited to the only shares that this command is using and make sure not to give EXEC permissions on this particular stored procedure (that runs XP_CmdShell) to any other logins. Also, make sure the command going to XP_CmdShell is very explicit and doesn't leave room for someone to hijack it with their own inserted T-SQL code.
Lastly, the EXECUTE AS command runs the proc with the permissions given under "EXECUTE AS" (read the first 2 sentences of the BOL entry carefully). I don't recommend excuting the proc under your own login or under the SA login. Create a SQL Server login with a high security password that has the proper permissions. Check out sp_xp_cmdshell_proxy_account and xp_cmdshell in BOL for more details on creating a proxy account which can run this command (you don't have to be SysAdmin for this). Also, if job is created to run the SSIS package, you can run it under the permissions of the SQLServer Agent Service account if you use an operating system command to call the package. The only caveat to this is if the package has to access file systems outside of the Server, the Agent account must use a domain user account (plain vanilla, not admin).
September 21, 2007 at 6:09 pm
You are doing it wrong you don't create the proxy account for a user rather you create the proxy for SQL Server Agent to execute the package with a job, the requirements are the Agent proxy account must be admin level and the owner of the package must be admin. The first link covers the how and the last two covers known issues. This solution have worked since SQL Server 7.0 so if yours is not working you have to find out what is missing in your setup. Hope this helps.
http://support.microsoft.com/kb/912911
http://support.microsoft.com/kb/918760/
http://support.microsoft.com/kb/938086/en-US
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply