September 16, 2016 at 4:48 pm
I've only used it on Azure SQL Datawarehouse, but I believe the Polybase feature in SQL Server 2016 solves the problem of BULK EXPORT.
September 17, 2016 at 2:10 am
van.yancey (9/16/2016)
I've only used it on Azure SQL Datawarehouse, but I believe the Polybase feature in SQL Server 2016 solves the problem of BULK EXPORT.
But it would be a little bit overwhelmed, if you use Polybase just to create a small 1-2KB HTML file on a network share (on your web server) that contains the next few scheduled downtimes (as we do)
God is real, unless declared integer.
September 17, 2016 at 6:49 am
I use both xp_cmdshell and powershell. xp_cmdshell is usually for easier things that I pretty much already know how to do. I don't write powershell scripts from scratch, yet. I find one that serves my purpose and modify it -- get WMI info, get server CPU/core info, script out all sql agent jobs, list sysadmins on all servers provided in a text file, get sql instance info, get server powerplan info, etc.
So yes, whether one is easier depends on how much you've done with the tool. I know quite a bit can be done with DOS batch language and NET commands.
September 17, 2016 at 2:32 pm
FrancoisFLageole (9/16/2016)
I use English rarely!
Heh... a lot of us in the U.S.A. have the same problem. 😀
--Jeff Moden
September 18, 2016 at 11:14 pm
I must admit I am a big fan of xp_cmdshell!
I have been using it for many years now, without any problems (security or otherwise). I suppose there are better ways of doing certain things, but in terms of simplicity nothing beats calling the odd ‘DOS’ command.
The fact that it is disabled by default is just as annoying as the ‘Prevent saving changes that require table re-creation’ option! 🙂
September 19, 2016 at 1:29 am
Stefan LG (9/18/2016)
I must admit I am a big fan of xp_cmdshell!I have been using it for many years now, without any problems (security or otherwise). I suppose there are better ways of doing certain things, but in terms of simplicity nothing beats calling the odd ‘DOS’ command.
The fact that it is disabled by default is just as annoying as the ‘Prevent saving changes that require table re-creation’ option! 🙂
Spot on to both!
--Jeff Moden
September 19, 2016 at 2:36 am
Jeff Moden (9/16/2016)
SQLNightOwl (9/16/2016)
[I agree the risk can be mitigated.That's what I'm talking about. People think there's a risk in using xp_CmdShell and there isn't. The only risks there are are in the privs that you give people and giving anyone or anything that's not a DBA privs to run xp_CmdShell directly is the mistake. Not xp_CmdShell itself.
Actually Microsoft fixed that more than a decade ago - if you ensure that the proxy account has no dangerous capabilities then non-SA people can use XP_CMDSHELL harmlessly (that wasn't true when I first encountered XP_CMDSHELL, but it is now); and of course if you don't create a proxy account for XP_CMDSHELL no-one but SQL Server SysAdmins can use it, and I certainly don't want every app that needs cmdshell access from SQL to have to run with SA privilege in SQL Server - that would be just plain crazy - so I'm going to create an XP_CMDSHELL proxy account that provides minimal permissions, unless I'm sure that no apps need to use XP_CMDSHELL. Unless, of course, no apps need it.
In my view, if you give someone you wouldn't trust to have a minimally privileged windows account the ability to construct and run SQL on your server you have a very insecure SQL instance. If you trust someone to run SQL, you might as well trust them to run windows commands.
I might be nice if things were organized so that anyone who logs into SQL Server with a Windows login gets his cmd_shell strings run under that Windows login; and to associate windows logins for all the SQL Server SQL logins to be used for xp_cmdshell. If you do have people you trust to run SQL Server but not DOS & windows commands you could associate those people's SQL logins with disabled windows accounts. All that is conceptually a small change, but it would probably be quite hard for Microsoft to implement; it would eliminate the difficulties with auditing that the current xp_cmdshell creates which might make some of the moaners stop moaning. I wonder if they would look at it as a possible future enhancement of SQL Server.
Disabling xp_cmdshell for sysadmins is pretty pointless, because they can just enable it whenever they want it.
Tom
September 19, 2016 at 5:19 am
It's easy to go bananas over one single configuration item such as sp_xpcmdshell. Of course noone should enable it wo having a good and well documented case for it.
And just as clear to me, after running systems 10+ years with and without it, I can't say xp_cmdshell per se have caused one single incident.
But we monitor the SQL side closely, we deny anyone that can't justify extensive access, we keep a close dialogue with the Infrastructure/windows team on security making sure they monitor and pamper the underlying OS and network.
Furthermore, it is dangerous to think security is a purely technical thing. Turning of this and that won't make a system secure if some other part of it is sloppy. Of course you have to know what and why you are doing things. Diving into NTFS per se is not dangerous...
We as DBAs must protect data and system, we must to protect personal integrity, and we need to protect the guys that pay our salaries.
Even when they don't know their own best, asking us to implement systems with crap security.
That's when we need to communicate. And with all necessary information given, those who pay our salary have the privilege of either accept putting a potentially dangerous system in a sandboxed environment/scrap it. Or not.
September 19, 2016 at 7:31 am
Just like a gun, it depends on how it's used.
September 19, 2016 at 8:38 am
By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 20, 2016 at 7:50 am
TomThomson (9/19/2016)
Jeff Moden (9/16/2016)
SQLNightOwl (9/16/2016)
[I agree the risk can be mitigated.That's what I'm talking about. People think there's a risk in using xp_CmdShell and there isn't. The only risks there are are in the privs that you give people and giving anyone or anything that's not a DBA privs to run xp_CmdShell directly is the mistake. Not xp_CmdShell itself.
Actually Microsoft fixed that more than a decade ago - if you ensure that the proxy account has no dangerous capabilities then non-SA people can use XP_CMDSHELL harmlessly (that wasn't true when I first encountered XP_CMDSHELL, but it is now); and of course if you don't create a proxy account for XP_CMDSHELL no-one but SQL Server SysAdmins can use it, and I certainly don't want every app that needs cmdshell access from SQL to have to run with SA privilege in SQL Server - that would be just plain crazy - so I'm going to create an XP_CMDSHELL proxy account that provides minimal permissions, unless I'm sure that no apps need to use XP_CMDSHELL. Unless, of course, no apps need it.
Yes, they did fix that long ago, but the fear remains with many people. I use it for my DBA stuff, but then again, my sa login is disabled and nobody has direct permissions to run any extended stored procedure.
September 20, 2016 at 7:52 am
Eric M Russell (9/19/2016)
By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.
The same applies for an attacker who breaks in with sysadmin privs. Hence, the sa login should be disabled and remain disabled.
Maybe I'm just naive, but I see no reason for any application user to have sysadmin privs...ever. I have no doubt that a case could be made for it, but that doesn't mean it'll be implemented in production. 😉
September 20, 2016 at 11:01 am
Ed Wagner (9/20/2016)
Eric M Russell (9/19/2016)
By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.The same applies for an attacker who breaks in with sysadmin privs. Hence, the sa login should be disabled and remain disabled.
I agree 100% with that. I start from the point that there shoul be no SQL logins - windows logins are all that's needed. However, it can happen that apps run on a server in a different domain and inter-domain trusts are not to be set up; iin that case SQL logins are needed, but SA should be given a ridiculously long and messy password, all record of what that password in should be destroyed, and then SA should be disabled. What's the point of that ridiculous password? With luck, it's pointless. If a naive sysadmin falls for some social attack and enables the SA login, the unknown password may be useful if the naive sysadmin doesn't fall for another social attack trying to get him to change SA the password.
Tom
September 20, 2016 at 1:14 pm
Peter Schott (9/16/2016)
OK, Jeff. Write your article(s). I'd love to see the "securing xp_cmdshell" article and the "how to use xp_cmdshell in a proc securely" article at the minimum. 🙂I'd love to switch our backups over to MinionWare's backups, but need to eliminate any/all concerns about the use of xp_cmdshell before we can do that.
Our standard response is Sean's blog, "Xp_cmdshell isn't Evil": http://www.midnightdba.com/DBARant/xp_cmdshell-isnt-evil/[/url]
September 20, 2016 at 1:35 pm
JenMidnightDBA (9/20/2016)
Peter Schott (9/16/2016)
OK, Jeff. Write your article(s). I'd love to see the "securing xp_cmdshell" article and the "how to use xp_cmdshell in a proc securely" article at the minimum. 🙂I'd love to switch our backups over to MinionWare's backups, but need to eliminate any/all concerns about the use of xp_cmdshell before we can do that.
Our standard response is Sean's blog, "Xp_cmdshell isn't Evil": http://www.midnightdba.com/DBARant/xp_cmdshell-isnt-evil/[/url]
I definitely appreciate that article, but having multiple people giving that opinion (in an article, not a forum thread) goes a long way towards establishing that, especially when there are articles out there, out of date or not, that talk about the evils of xp_cmdshell. 🙂 There's a lot of extra concern because we're dealing with potential HIPAA data and want absolutely no part of any sorts of the fines that could come if there were to be some form of data breach. That's unlikely to happen just due to xp_cmdshell, but those in charge are understandably wary of anything that could increase risk.
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply