February 28, 2006 at 6:58 pm
Greetings, I am encountering the error...
Msg 50001, Level 1, State 50001
xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account
I have seen several items expressing concern about how best to grant privileges on this stored procedure without compromising security of the database but I don't quite understand the best way to proceed. I am looking for guidance and any suggestions will be greatly appreciated.
Thanks.
February 28, 2006 at 10:53 pm
backtrack a bit
why do you need xp_cmdshell? you may find that you don't actually need it afterall
March 1, 2006 at 8:34 am
A few things have to happen in order to allow access to xp_cmdshell.
First, you would have to set the proxy account, as the error indicates. That means that whoever can execute xp_cmdshell can run a command on your SQL Server with that user's credentials as if they were at a command prompt (which effectively they are). That's why typically only members of the sysadmin fixed server
Second, you would have to allow the user to execute xp_cmdshell, which it looks like you already did. How did you set these permissions up? By default no user account has been granted direct access to master db. That means all users come in as the guest account. If you've granted EXECUTE permissions either to guest or the public role, understand that means anyone who has a valid login to your SQL Server can now execute xp_cmdshell. That's typically a whole lot broader than most people want. Hopefully you've granted database access to Windows groups (or individual logins if there aren't many) and given those logins the ability to execute the stored procedure.
However, I go back to Jen's question: why is such access needed?
K. Brian Kelley
@kbriankelley
March 1, 2006 at 2:26 pm
I spoke with the users and am assured that the application, delivered by a third party, requires this functionality. I resolved the problem by creating a proxy account.
My next question is, understanding that this is a security risk, can someone provide guidelines for the permissions to grant to the Windows user I am using as the proxy user. What should I take into consideration as I am doing this?
Thanks.
March 1, 2006 at 2:31 pm
Follow the principle of least privilege. Give the proxy account only what rights are absolutely necessary. If it needs access to a particular directory, grant it access to that directory, etc. But I would challenge the third party on their use of xp_cmdshell as is reasonable. That's a known security no-no. I'm rather surprised that they advocate use of it. Well, no I'm not. But they should know better.
K. Brian Kelley
@kbriankelley
February 18, 2008 at 11:45 pm
Recently I am doing some RND regarding proxy accounts and still not clear about it.
I am using proxy account & xp_cmdshell to run DTS externally.
I have a limited sql usr and granted permission to execute xp_cmdshell. However this user cannot do any other activity in master database.
February 29, 2008 at 9:48 am
Granting execute on xp_cmdshell won't work as sql server requires the user to be a member of OS admin.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 3, 2009 at 10:11 am
I've got a SQL server box outside our domain, and I need to be able to use xp_cmdshell on it to access a filesystem that is on our domain.
Anyone know a reasonable way to do that? It won't let me set the proxy account to the service account on our domain that I want to use because it (obviously) can't log in.
The Redneck DBA
March 3, 2009 at 11:10 am
Sugesh Kumar (2/29/2008)
Granting execute on xp_cmdshell won't work as sql server requires the user to be a member of OS admin.
The operating system credentials of the user don't enter into this. If xp_cmdshell is enabled (as it is by default in SQL Server 2000 and below), then if you are a member of the sysadmin fixed server role within SQL Server, you can execute it and you access to the operating system is determined by the access the SQL Server service account has. Otherwise, you must be given explicit permission and the proxy account must be configured.
K. Brian Kelley
@kbriankelley
March 3, 2009 at 11:13 am
Jason Shadonix (3/3/2009)
I've got a SQL server box outside our domain, and I need to be able to use xp_cmdshell on it to access a filesystem that is on our domain.Anyone know a reasonable way to do that? It won't let me set the proxy account to the service account on our domain that I want to use because it (obviously) can't log in.
If the SQL Server is not on the same domain (or a trusted domain), you're not going to be able bridge across like this without doing a major hack or doing something which has some severe consequences, like enabling the guest Windows user account on the server with the file share.
Is there any trust relationship in place at all? Maybe one-way in the other direction?
K. Brian Kelley
@kbriankelley
March 3, 2009 at 1:06 pm
We ended up deciding to just do this outside of SQL.
The Redneck DBA
March 3, 2009 at 1:15 pm
giving up so easily? from my understanding you want to execute a dts package that needs to manipulate a file, perhaps as source or destination
1. set up the dts package as a job, that way there is no question about permission on the OS level and SQL level, usually, the job is owned by SA, unless you specified in your connection tasks a different account, then your application or whatever service it's being run must have both permissions
2. for file access, manipulate, bcp in or bcp out would do, otherwise you can always specify a file source for transformation tasks...
hope that helps.
March 3, 2009 at 1:38 pm
Jason Shadonix (3/3/2009)
We ended up deciding to just do this outside of SQL.
How did you deal with the untrusted situation? FTP?
K. Brian Kelley
@kbriankelley
March 3, 2009 at 1:46 pm
Just a batch file with the following line before we do the file copies:
net use
I'm sure it's not the most secure thing in the world, but it will meet our needs.
The Redneck DBA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply