October 11, 2014 at 11:47 am
I am trying to get the following script to run on a newly created SQL server.
I've set up the xp_cmdshell proxy account to be the same account as the service account for purposes of troubleshooting at this point.
xp_cmdshell 'whoami.exe'
go
master.dbo.xp_cmdshell 'dir d:\<path>'
go
master.dbo.xp_cmdshell 'echo %username%'
go
If I run as myself, it as expected returns the SQL service account for the first two calls, and the contents of that folder in the 3rd call.
If I run as a non-sysadmin SQL login, it again returns the SQL service account for the first two calls, but returns "access is denied" on the 3rd call.
I'm sure I'm missing something really stupid, but can't think what it may be. Any suggestions?
The Redneck DBA
October 12, 2014 at 2:06 am
Quick thought, check the file system permissions for the account on the folder specified (<path>)
😎
October 12, 2014 at 9:40 am
Jason Shadonix (10/11/2014)
I am trying to get the following script to run on a newly created SQL server.I've set up the xp_cmdshell proxy account to be the same account as the service account for purposes of troubleshooting at this point.
xp_cmdshell 'whoami.exe'
go
master.dbo.xp_cmdshell 'dir d:\<path>'
go
master.dbo.xp_cmdshell 'echo %username%'
go
If I run as myself, it as expected returns the SQL service account for the first two calls, and the contents of that folder in the 3rd call.
If I run as a non-sysadmin SQL login, it again returns the SQL service account for the first two calls, but returns "access is denied" on the 3rd call.
I'm sure I'm missing something really stupid, but can't think what it may be. Any suggestions?
It's actually a good thing that this failed. SQL Server is trying to save your life as an admin.
First and with the understanding that I'm very "pro" on the use of xp_CmdShell, allowing a "non-sysadmin" SQL login to execute xp_CmdShell directly is one of the absolute worst security violations that you could ever have. Doing such a thing can allow those non-sysadmin users to gain access to things that they're not supposed to have access to.
The ONLY way that non-system admins should be able to execute anything having to do with xp_CmdShell is through the use of VERY directed/limited, singular-purpose, well written stored procedures that checks for DOD INJECTION and other potential misuse. To do that, the owner of the database should probably be "SA" (of course, the "SA" user SHOULD be disabled and this will still work) and the stored procedure should contain an "EXECUTE AS OWNER".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2014 at 10:24 am
Eirikur Eiriksson (10/12/2014)
Quick thought, check the file system permissions for the account on the folder specified (<path>)😎
I finally figured this out. I'm not exactly sure why my permission worked, but it is working.
I verified permissions to the folder I was trying to hit. So just as an exercise, I made that folder a share and granted that account rights to the share. Then it magically started working.
I'm not sure why, because in theory the same account should have been used in both the sysadmin and non sysadmin cases, so I can't explain how it would work one way and not the other.
The Redneck DBA
October 12, 2014 at 10:34 am
Don't do it the way you're doing it. YOU MUST NOT GRANT NON-SYSTEMADMINS PRIVS TO RUN XP_CMDSHELL DIRECTLY!!! Please, please, PLEASE! For the good of yourself and the company that you work for, please see my previous post on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2014 at 10:38 am
Jason Shadonix (10/12/2014)
Eirikur Eiriksson (10/12/2014)
Quick thought, check the file system permissions for the account on the folder specified (<path>)😎
I finally figured this out. I'm not exactly sure why my permission worked, but it is working.
I verified permissions to the folder I was trying to hit. So just as an exercise, I made that folder a share and granted that account rights to the share. Then it magically started working.
I'm not sure why, because in theory the same account should have been used in both the sysadmin and non sysadmin cases, so I can't explain how it would work one way and not the other.
Good stuff as explicit permissions is what you want, but read Jeff's post and come back if you have any further questions on the subject. As Jeff points out, the xp_cmdshell permissions are something not to be taken lightly, wrongly applied it's just as bad as handing over the Administrator's password.
In other words, the SQL Server should never be an "Anonymizer" for any operations on the SQL Server host system, anything needed should be explicitly granted and everything else denied. In my experience, the SQL Server is a far more secure platform than the Windows host OS, containment of privileges is therefore very important.
😎
October 12, 2014 at 5:54 pm
I share your concerns, and appreciate you pointing this out, but unfortunately (as I'm sure you experts know) there are some cases where we have no choice but to use xp_cmdshell.
I am curious why making a share made any difference because I'm not accessing the path with a share, I'm actually using the full path to it.
I would think if you have the proxy account set to the same thing as the sql service account (yes, I know that's tacky, so no more lectures need Jeff :p), then you would have the same behavior from xp_cmdshell with and without a sysadmin account.
The Redneck DBA
October 12, 2014 at 7:08 pm
Please put xp_cmdshell down, and back away, now. If you are opposed to exhibiting some control around the implementation of xp_cmdshell then you should not be using it at all. Switch it out for something safer, SQLCLR objects that implement only the specific file system operations you need to offer to your application.
I recently implemented these SQLCLR objects from CodePlex with some modifications to suit my environment, and they work quite well. There is one for getting a directory listing that would handle the example you showed in your original post:
FileSystemHelper SQL Server CLR[/url]
I am not a fan (despise actually) of accessing the file system from within a T-SQL context but I was retrofitting a legacy application that made heavy use of xp_cmdshell and could not completely eliminate the file system access due to project constraints. Personally I object to the mere enabling of xp_cmdshell on my instances, let alone widespread use of it for application functionality, mainly due to how it is misunderstood and eventually misused and abused exposing the system to malicious users or even users unknowing or of the risks (such as yourself). Your attitude towards xp_cmdshell only supports me in my long-running effort to steer people away from using it.
If you must access the file system from a T-SQL context, please trade in xp_cmdshell for SQLCLR. You can read this article for more information on the topic from a SQL Server MVP:
Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url]
And the spirited discussion that followed is also a great read to learn about the debate.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 12, 2014 at 8:42 pm
Jason Shadonix (10/12/2014)
I share your concerns, and appreciate you pointing this out, but unfortunately (as I'm sure you experts know) there are some cases where we have no choice but to use xp_cmdshell.I am curious why making a share made any difference because I'm not accessing the path with a share, I'm actually using the full path to it.
I would think if you have the proxy account set to the same thing as the sql service account (yes, I know that's tacky, so no more lectures need Jeff :p), then you would have the same behavior from xp_cmdshell with and without a sysadmin account.
You misunderstand me. I'm [font="Arial Black"]pro[/font]-xpCmdShell. It's a powerful tool that should be used that's frequently overlooked or even forbidden because of a lot of bad press (your current method of it's use will someday lend to the bad press), FUD, and a whole lot of misunderstanding because of its poor history prior to 2005 . Along with the power comes the need for safety. As you know, proper usage by sysadmins and properly configured jobs do not require a proxy. The only time a proxy is required is when you want to let someone or something without sysadmin privs execute xp_CmdShell.
I'm suggesting that it's horribly dangerous to let someone or something (an app, perhaps) execute anything they want by allowing execution of xp_CmdShell directly because it allows them to elevate their privs in just about any way they see fit.
Yes,[font="Arial Black"] I absolutely agree with the use of xp_CmdShell but it has to be done correctly[/font]. You MUST NOT give anyone or anything other than trusted DBAs that necessarily have "SA" priv to run xp_CmdShell directly. Like I said in my first post, [font="Arial Black"]instead of giving such individuals or things privs to run xp_CmdShell directly, give them or it privs only to run a properly created stored procedure design to do only the job that needs to be done.[/font]
DO use xp_CmdShell but use it correctly or someone will realize what they can do with it and you will be burned by it. It's simple to control in the manner that I've previously prescribed.
To summarize again, [font="Arial Black"]do NOT give non-sysadmin users the privs to run xp_CmdShell directly. Instead, give them privs to a proc that uses it only for the task at hand [/font]and that the users cannot modify as to purpose. If you're not going to take that precaution, then I strongly agree with Orlando (opc.three)... use something else, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply