August 17, 2011 at 10:50 am
One of the things I dislike most about cmdshell is its lack of specificity.
As long as a valid DOS command is passed to it, cmdshell will run it happily (depending on perms of course).
Then there is a lack of granularity, as mentioned by someone earlier, in managing non-SA permissions, and I think this is key.
We have used SQL CLR to create a bunch of procedures and UDFs; each of these performs specific, narrow tasks on the file system:
for example, listing the contents of a folder or deleting a specified file passed as a parameter.
We then grant permissions to database roles according to this pattern:
- wide access to sprocs/UDFs that allow read-only access to the file system
- narrower access to sprocs/UDFs that allow modifications, such as copying files from one place to another
- sa-only access to sprocs/UDFs that permit more drastic modifications, such as deleting/moving files or directories.
I'm not sure how this sort of granularity in permissions could be managed with cmdshell (I'm not familiar with proxies and cmdshell).
There is more: one may decide they don't want any functionality that does any modifications to the file system programmatically.
Then one can create only objects that perform read-only access, such as listing folder contents and nothing else.
Then with cmdshell already disabled, no one can perform modifications to the file system through an app, unless they gain access to the actual server or to a network share and do the damage manually through Windows explorer.
With cmdshell it's all or nothing.
With SQL CLR one has control over what to allow and what not.
(BTW, I typed cmdshell instead of the full name because I am unable to save my post with the full name of the command; anyone else have this problem?)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 17, 2011 at 12:10 pm
Jeff Moden (8/17/2011)
ALZDBA (8/17/2011)
As I said to Perry, I have a real appreciation for that. Let me ask this though. If an attacker can get into your system with sufficient privs (PUBLIC with DataReader/DataWriter), is isolating xp_CmdShell going to keep your system safe? If an attacker can get in as an "SA", will any of those good and thoughtful steps help? What if I told you (and, it's true) that you could actually delete the xp_CmdShell DLL and that an attacker could still execute CMD.EXE through the likes of OPENROWSET?
Of course it is only like the fence around a house.
IMO my devs should think about delivering a controllable and maintainable system.
Integration is one thing, doing it in a controllable way is another story.
With your experience, you'll know most project teams at best only have a clue about - a part of - their project using then infrastructure. Maybe even how it is connected to other systems at a physical level.
We suffered a couple of company mergers during the lase decade. Making the inventory is one thing, checking it for correctness is another.
Kind of like using advanced sql when a simple select can do the thing with a good enough performance.
Just trying to avoid exceptions and trying to push structure as much as possible.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 17, 2011 at 3:19 pm
Matt Miller (#4) (8/17/2011)
While they did make some improvements to how it operates (the fact that you can in fact disable it is very good) - I still don't understand why they keep making asinine choices as to default security. Specifically - I don't understnad why it would EVER be a good thing to have it reuse context/privileges of the SQL Server service account. I just think they haven't gone far enough to put it in its own secure box from where you cannot get out, ever. There frankly has never been a rational reason to ever allow that to happen: if you need that much control of stuff happening outside, then you're using your SQL server to run things it has no business doing.The single proxy account also bothers me, a lot: that just invites having an account with altogether to many rights.
Another way to describe in my mind would be - you should not have to work to MAKE it secure, you should have to work to make it not secure. My vote is still no.
If there was a 100% secure way, as you asked, to prevent these weaknesses - I'd be compelled to look again. That said, to each action there is an equal and opposite reaction, so I'd be looking hard for what got opened up in order to allow this 100% secure solution.
Absolutely outstanding post, Marios. You’ve cited some excellent “problems” associated with the use of xp_CmdShell not the least of which is the apparent “all or nothing” capabilities associated with xp_CmdShell. To solve this problem, you’ve created CLR procedures and UDF’s that will handle, as you say, some “narrow tasks on the file system”.
You are certainly not alone in the “all or nothing” perception of the use of xp_CmdShell. Except for one other person that I know in the entire world, virtually everyone implements xp_CmdShell for non-SA users by granting them permission to execute it directly. It’s no wonder there’s such a fear of using xp_CmdShell in production code.
More specific to the problems you’ve resolved through the use of thoughtful and narrowly-scoped CLRs, my question on that would be, why do most (and I do mean “almost all”) people think such a thing isn’t possible through the use of xp_Cmdshell via a T-SQL based stored procedure? It certainly is possible to make T-SQL stored procedures do something very narrow in scope even with xp_Cmdshell based only on proper and “cleansed” input parameters. It certainly is possible to grant execute privs to “PUBLIC- only” users that don’t even have “DataReader” privs to execute the proc even though the proc contains a call to xp_CmdShell without giving those users any privs to execute xp_CmdShell directly. And, the users can’t even see what’s inside the proc. What is the difference between such a T-SQL stored procedure and the narrow-scope CLR’s you and others have created to solve such problems?
I’d like to suggest that the only difference is that most people don’t know how to allow very low privileged users to run stored procedures that do more than the user has privileges to do directly. I’d also suggest that, because of the problem I just stated, that most GUI logins have way too many privileges even if they aren’t using the likes of xp_CmdShell. Would you (or anyone else that has insight or opinion on this matter) agree or is there something that I’m overlooking?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 6:23 pm
Matt Miller (#4) (8/17/2011)
While they did make some improvements to how it operates (the fact that you can in fact disable it is very good) - I still don't understand why they keep making asinine choices as to default security. Specifically - I don't understnad why it would EVER be a good thing to have it reuse context/privileges of the SQL Server service account. I just think they haven't gone far enough to put it in its own secure box from where you cannot get out, ever. There frankly has never been a rational reason to ever allow that to happen: if you need that much control of stuff happening outside, then you're using your SQL server to run things it has no business doing.The single proxy account also bothers me, a lot: that just invites having an account with altogether to many rights.
Another way to describe in my mind would be - you should not have to work to MAKE it secure, you should have to work to make it not secure. My vote is still no.
If there was a 100% secure way, as you asked, to prevent these weaknesses - I'd be compelled to look again. That said, to each action there is an equal and opposite reaction, so I'd be looking hard for what got opened up in order to allow this 100% secure solution.
I'm never disappointed when you make an appearance, Matt. Your post above is no exception. Thank you for the response.
Matt Miller (#4) (8/17/2011)
If there was a 100% secure way, as you asked, to prevent these weaknesses - I'd be compelled to look again. That said, to each action there is an equal and opposite reaction, so I'd be looking hard for what got opened up in order to allow this 100% secure solution.
There are actually 3 different ways that I know of for a very low prived user to execute a stored procedure that contains a call to xp_CmdShell without that user being able execute xp_CmdShell directly. Oddly enough and totally unexpected by me, is what else I’ve found during the research I’ve done on xp_CmdShell. It turns out that the same methods to run xp_CmdShell safely through procs only are also the same methods necessary to properly secure access by virtually any UI. The sad part about that is how many people don’t actually know how to do either.
To wit and a bit contrary to your thoughts on the default state of security of SQL Server, I propose that, to start with, access to SQL Server objects is, in fact, denied to anyone and everyone except for the people who know the “SA” password. Then, we humans go in and muck it all up by granting privs to individuals, groups, database roles, and even other systems, overriding the security which occurs by default in SQL Server. Wouldn’t you agree?
In an ironic twist of fate, it turns out that the methods to successfully execute xp_CmdShell securely are the very same methods that we should all be following for all access never mind access to xp_CmdShell.
...if you need that much control of stuff happening outside, then you're using your SQL server to run things it has no business doing.
As you’ve probably already surmised, I do disagree with that premise for multiple reasons but we can save that conversation for a future (and lively, I’m sure) engagement. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 6:53 pm
Perry Whittle (8/17/2011)
Jeff, the biggest problem in the past (SQL Server 2000 era) is that XP_CmdShell could not be disabled or removed. It was an open invite for a hacker to[font="Arial Black"] gain OS access through SQL Injection[/font]. Things have moved on a lot since those days, i see nothing wrong with using for administrative use, [font="Arial Black"]just ensure your script enables and disables it after use.[/font]Getting in as SA boils down to permissions. I have in the past seen website connections using the SA account which i obviously vetoed immediately.
Well done on vetoing SA privs for webstie connections, Perry. I hope your "veto" actually stuck. I've seen many a DBA team get overridden by a "management decision important to the business" and the catastrophic results which occur shortly after such a decision.
Let's talk about SQL Injection for a minute. Most folks talk about what can happen if a login for a Website connection or even a local app has "SA" privs and what can happen if an attacker can successfully "get in as 'SA'". Instead, I'd like approach the subject of SAQL Injection from a different aspect. What can an attacker do if they successfully use SQL Injection to break in though a user/login that has NO privs except to execute stored procedures that they can't even see the internal workings of? I could certainly be incorrect but I propose the hacker could do absolutely nothing except run the stored procedures where the login they break in with has been granted EXECUTE privs. Am I incorrect in that "assumption"?
Also, since you've obviously taken steps to ensure that logins for WebSites don't actually have "SA" privs, why do you (or anyone else who would like to jump in here) bother turning off xp_CmdShell after you've used it for an Administrative task? Who would use it if you left it turned on? You've secured the WebSite to prevent those logins/users from using xp_CmdShell. As most people know it, it takes "SA" privs to both turn on xp_CmsShell and then use it. Of course, one could make the mistake of granting a login the privs to execute xp_CmdShell but considering the care you took with locking down the WebSite privs, would that ever happen on your systems? I agree.... not by your hand nor by the hand of anyone you work with. So, why turn it off after an administrative task? Heh.. and, no... I'm not being confrontational here. I know the reasons some folks justify turning it off... Since you’re one of the heavy hitters here on SSC and someone I have a strong trust in, I'd like to know yours. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 6:58 pm
colin.Leversuch-Roberts (8/17/2011)
If I need to use xp_cmdshell then I do - it depends upon client requests and their perception of course, There's always a certain amount of using what you have to achieve an end result, preferably without re-inventing the wheel to do so.
Practical as always, Colin. Thank you for taking the time to respond especially since I strongly agree with the premise of not "re-inventing the wheel" to do certain tasks.
If you have more time to invest in this thread, I'd really be interested in a 30,000 foot view of the methods you use to use xp_Cmdshell and whether or not you take any special safeguards. I'd also be interested in what you think the risks may be in using xp_CmdShell in a production environment and why.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 7:14 pm
Michael Valentine Jones (8/17/2011)
I think xp_cmdshell can be used securely if you take the trouble to set it up correctly.The one thing that I don't like is that you basically have two levels of privilege, users with sysadmin that run xp_cmdshell under the context of the service account, [font="Arial Black"]and non-sysadmin users who run in the context of the proxy account [/font](or nor at all if there is no proxy account).
It would be better if you could have multiple proxy accounts, and had the ability to assign proxy accounts at the level of login, database, database user, or stored procedure. Then it would be far easier to allow the use of xp_cmdshell with the proper level of security and no more than what is needed.
Ah, Micheal. Glad you showed up. Thank you for taking the time to make a response.
If you look at the words I bolded in the quote for your post, you've identified the single biggest problem with the more common method of allowing a user to use xp_Cmdshell... the fact that the user is allowed to run xp_CmdShell directly. So my question for you would be, if all users on a production system had no privs other than "PUBLIC" (and I mean no other privs... not even "DB_DataReader") and the EXECUTE permissions the role they were a member of had, would that satiate your requirement for accountability even though it was through a single proxy account? Even if they were able to run a stored procedure that made a call to xp_CmdShell (which can be done 3 different ways, BTW)?
Also and slightly off tangent, are you aware of the fact that an attacker can get to CMD.exe through T-Sql even if xp_CmdShell has been deleted from the system?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 8:02 pm
Jeff Moden wrote:
More specific to the problems you’ve resolved through the use of thoughtful and narrowly-scoped CLRs, my question on that would be, why do most (and I do mean “almost all”) people think such a thing isn’t possible through the use of xpCmdshell via a T-SQL based stored procedure? It certainly is possible to make T-SQL stored procedures do something very narrow in scope even with xpCmdshell based only on proper and “cleansed” input parameters. It certainly is possible to grant execute privs to “PUBLIC- only” users that don’t even have “DataReader” privs to execute the proc even though the proc contains a call to xpCmdShell without giving those users any privs to execute xpCmdShell directly. And, the users can’t even see what’s inside the proc. What is the difference between such a T-SQL stored procedure and the narrow-scope CLR’s you and others have created to solve such problems?
One reason I can think of why, to me, CLR would be preferable to T-SQL: it introduces an extra layer of complexity to developing and deploying code as an assembly (say with EXTERNAL_ACCESS permissions), as opposed to a T-SQL module.
CLR code within an assembly is not as "visible" or "hackable" as code in a T-SQL sproc, and, therefore not as easy to break through and manipulate.
Seems to me this code may be more secure in that sense than T-SQL code for accessing resources outside the SQL process.
That's not to say I don't use xpcmdshell from time to time; but I generally keep it off on all servers.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 17, 2011 at 8:04 pm
ALZDBA (8/17/2011)
Of course it is only like the fence around a house.
I couldn't have said it better, Johan. Worse than making the realization that it's only a "fence", I've found that most DBA's (and I do mean "most") have unwittingly left multiple gates open even if they don't allow the use of xp_CmdShell. As I stated in a couple of the previous posts on this thread, there's actually no need for any public facing login to have any more than raw "Public" privs with no other permissions granted save EXECUTE privs on stored procedures.
IMO my devs should think about delivering a controllable and maintainable system.
Integration is one thing, doing it in a controllable way is another story.
With your experience, you'll know most project teams at best only have a clue about - a part of - their project using then infrastructure. Maybe even how it is connected to other systems at a physical level.
We suffered a couple of company mergers during the lase decade. Making the inventory is one thing, checking it for correctness is another.
Kind of like using advanced sql when a simple select can do the thing with a good enough performance.
Just trying to avoid exceptions and trying to push structure as much as possible.
Agreed and I'm very happy to have you bring up those exceptions. In fact, you bring a scenario to mind that should add to the conversation about xp_CmdShell. Consider the following and let me know what you would do and what you think would ultimately happen...
Your company has been very successful in the acquisition and merger of many companies. You're company is about to acquire yet another company. This other company is renowned for certain ETL processes that your company desperately wants to add to their business model. Because you're the Senior DBA, they ask you to conduct an "inventory" and determine a "status" of the other company's SQL Servers, databases, and security model. Once you have "discovered" their servers (which was pretty easy because their DBA helped), you begin to take stock of things. After doing the normal "takeover" things that would be part of any such exercise, you discover that their key databases have not hundreds, but thousand of stored procedures. You open a couple of them and find them all to be well written and properly documented. You open a third proc and find that xp_CmdShell has been used. You quickly write an ad hoc script to find all stored procs that contain calls to xp_CmdShell. You find that hundreds and hundreds of the stored procedures all contain at least one call to xp_CmdShell. Digging deeper, you find that the proxy for xp_CmdShell had been set up properly and that two logins associated with the many "WebSite" interfaces have EXECUTE privs for xp_Cmdshell and that it appears that no reasonable attempts to prevent attacks through SQL injection have been made on any of the procs including but not limted to the all important "Login" screen-procs.
You bring the "problems" to the attention of management and their direction is simple... "We're buying the company, the servers, their data, and their technology. You have a week to make those security problems go away." Now, remember... there are hundreds and hundreds of stored procs that use xp_CmdShell and those stored procs are "absolutely critical to the technology" your company is buying.
What do you do?
Obviously, the standard and widely accepted (but incorrect) methods for using xp_CmdShell just won't do here but that's what they've done. I have to seriously thank you, Johan. This is a critical and not-so-uncommon scenario that had previosly escaped me. There are, however, correct methods to quickly resolve this problem... methods that should be deployed in every system whether or not xp_CmdShell will ever be used or not.
As you may have surmised (and some already know from other threads), I've been seriously considering writing about the 3 secure methods to safely use narrow-scope stored procedures that contain calls to xp_CmdShell. I've heard many a respected DBA say that they use it for certain "administrative" tasks and that they turn off xp_CmdShell when they're done with it. Although I've proven to myself, my System DBA, and all the associated folks at the company that I work for that xp_CmdShell can be used safely and securely to greatly simplify certain "production" tasks and I've successfully deployed Secure xp_CmdShell. However, the visceral fear of most people I've talked with outside my company about using xp_CmdShell in production led me to believe that such an article may not be necessary nor even appreciated. Thank you for indirectly convincing me that such an article should be written. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 8:15 pm
Marios Philippopoulos (8/17/2011)
Jeff Moden wrote:More specific to the problems you’ve resolved through the use of thoughtful and narrowly-scoped CLRs, my question on that would be, why do most (and I do mean “almost all”) people think such a thing isn’t possible through the use of xpCmdshell via a T-SQL based stored procedure? It certainly is possible to make T-SQL stored procedures do something very narrow in scope even with xpCmdshell based only on proper and “cleansed” input parameters. It certainly is possible to grant execute privs to “PUBLIC- only” users that don’t even have “DataReader” privs to execute the proc even though the proc contains a call to xpCmdShell without giving those users any privs to execute xpCmdShell directly. And, the users can’t even see what’s inside the proc. What is the difference between such a T-SQL stored procedure and the narrow-scope CLR’s you and others have created to solve such problems?
One reason I can think of why, to me, CLR would be preferable to T-SQL: it introduces an extra layer of complexity to developing and deploying code as an assembly (say with EXTERNAL_ACCESS permissions), as opposed to a T-SQL module.
CLR code within an assembly is not as "visible" or "hackable" as code in a T-SQL sproc, and, therefore not as easy to break through and manipulate.
Seems to me this code may be more secure in that sense than T-SQL code for accessing resources outside the SQL process.
That's not to say I don't use xpcmdshell from time to time; but I generally keep it off on all servers.
That's a very good point and a good point of view. However, if (as I've already stated) you have users with only "PUBLIC" privs and they've been granted only EXECUTE privs on a given stored procedure, who can see the code inside that given stored procedure?
Again, thank you for the wonderfully thoughtful responses. This is getting really good! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 11:32 pm
Jeff Moden (8/17/2011)
muthukkumaran Kaliyamoorthy (8/17/2011)
I tried to avoid using xp_cmdshell but sometime I can't.Most of time I used the xp_cmdshell for deleting the old backups because the drive got full and apps goes down. Once i had done I disabled it. (even scripts too)
The reason for using this some of the servers I don't have MSTSC access (OS level). Whatever the reason they didn't give(may be haven’t trust us).
Ah... there's the other side of the coin. Thank you for the response, Muthukkumaran.
As you have, some other folks in this thread have also justified the occasional need for the use of xp_CmdShell for administrative chores/tasks. I have a question for you that will help me solve one of the questions that I have about xp_CmdShell and the fear of using it, if you don't mind. Since you generally need to have "SA" privs to execute xp_CmdShell, why do you bother disabling xp_CmdShell at the server level when you are done with it as an "SA" prived DBA? Who or what else has "SA" privs on your production server(s) and how do you prevent them from simply turning xp_CmdShell back on?
Jeff,
Since you generally need to have "SA" privs to execute xp_CmdShell
why do you bother disabling xp_CmdShell at the server level when you are done with it as an "SA" prived DBA?
Yep most of the servers i am the only admin(SA) but some times the developers also needs xp_cmdshell to do some activities (BCP,ftp...)
Absolutely i have disabled the xp_cmdshell once i the tasks completed. Even if its stored procedures too.
Who or what else has "SA" privs on your production server(s)
It depends. most of the servers i am the only but some of the customer servers many others too.
how do you prevent them from simply turning xp_CmdShell back on?
i tried using PBM but what to do they have also sysadmins.
Thanks for the thread jeff.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
August 18, 2011 at 1:09 am
Jeff Moden (8/17/2011)
What do you do?
Work hard 🙂
I published an avoidance script a long time ago (0001/01/01):help to tighten use of cmdshell or sp_start_job[/url]
... However, the visceral fear of most people I've talked with outside my company about using xp_CmdShell in production led me to believe that such an article may not be necessary nor even appreciated.
IMO it is better to expose your ennemies, so you get to know their strong points, but especially their weaknesses.
There are many articles concerning SQLInjection.
We all know we're not supposed to do that, but we are not the only ones using the system. :crazy:
Thank you for indirectly convincing me that such an article should be written. 🙂
A direct approach might have scared you off :hehe:
As my colleague joyfully quotes "Great, Condi. Lay it on me." 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 18, 2011 at 2:21 am
Jeff Moden (8/17/2011)
Well done on vetoing SA privs for webstie connections, Perry. I hope your "veto" actually stuck. I've seen many a DBA team get overridden by a "management decision important to the business" and the catastrophic results which occur shortly after such a decision.
Yes it did stick, when i confronted the vendor support about it he was horrified
Jeff Moden (8/17/2011)
Let's talk about SQL Injection for a minute. Most folks talk about what can happen if a login for a Website connection or even a local app has "SA" privs and what can happen if an attacker can successfully "get in as 'SA'". Instead, I'd like approach the subject of SAQL Injection from a different aspect. What can an attacker do if they successfully use SQL Injection to break in though a user/login that has NO privs except to execute stored procedures that they can't even see the internal workings of? I could certainly be incorrect but I propose the hacker could do absolutely nothing except run the stored procedures where the login they break in with has been granted EXECUTE privs. Am I incorrect in that "assumption"?
Remember that the website users are likely\definitely not the only users on the SQL Server instance. It's not just about admin access either, even a standard user still has certain PUBLIC permissions to read certain metadata and SQL injection exploits this too, although it is mainly opportunist and relies on bad programming and poor security for the user account.
When i'm asked for a new user account and the requestor specifies "i want dbo, i want sa......" i say to them, tell me why you need these permissions. Generally they can't so i don't grant them. It's just an old hangover where heh grant dbo or sa and the application magically starts working 😉
In reality the account just needed permissions to execute a couple of stored procedures.
Jeff Moden (8/17/2011)
Also, since you've obviously taken steps to ensure that logins for WebSites don't actually have "SA" privs, why do you (or anyone else who would like to jump in here) bother turning off xp_CmdShell after you've used it for an Administrative task? Who would use it if you left it turned on? You've secured the WebSite to prevent those logins/users from using xp_CmdShell. As most people know it, it takes "SA" privs to both turn on xp_CmsShell and then use it. Of course, one could make the mistake of granting a login the privs to execute xp_CmdShell but considering the care you took with locking down the WebSite privs, would that ever happen on your systems? I agree.... not by your hand nor by the hand of anyone you work with. So, why turn it off after an administrative task? Heh.. and, no... I'm not being confrontational here. Since you’re one of the heavy hitters here on SSC and someone I have a strong trust in, I'd like to know yours. 🙂
I take steps to turn it off again as part of the surface area configuration. It's not just about one feature or component. Taking steps to secure and reduce the impact of a range of components helps to reduce the attack surface area. Even CLR is disabled by default and let's face it if a 3rd party\vendor wants to deploy CLR procedures to your server are you going to allow it? You certainly want to have verification that their coding promotes security and system stability.
Let's be honest here, if website programmers took more care with their code then SQL Injection would be vastly reduced. The builtin validation components available in the latest .NET technologies, thankfully make SQL Injection a lot harder, providing the developer uses them. After that it's down to instance\database level security
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2011 at 3:54 am
Jeff Moden (8/17/2011)
As you may have surmised (and some already know from other threads), I've been seriously considering writing about the 3 secure methods to safely use narrow-scope stored procedures that contain calls to xp_CmdShell. I've heard many a respected DBA say that they use it for certain "administrative" tasks and that they turn off xp_CmdShell when they're done with it. Although I've proven to myself, my System DBA, and all the associated folks at the company that I work for that xp_CmdShell can be used safely and securely to greatly simplify certain "production" tasks and I've successfully deployed Secure xp_CmdShell. However, the visceral fear of most people I've talked with outside my company about using xp_CmdShell in production led me to believe that such an article may not be necessary nor even appreciated. Thank you for indirectly convincing me that such an article should be written. 🙂
I think that would be an extremely useful article. Over the years I've made extensive use of xp_cmdshell to assist in admin tasks, error containment and recovery, status monitoring, and diagnostics, but the opposition that has sometimes arisen was, frankly, a pain in the rear and a waste of my time. Most of the oppositon of course came from people who haven't a clue how to secure an SQL Server in the first place.
As a general rule I will not permit ad hoc SQL on a production SQL Server installation that I control, except for users with CONTROL SERVER permission. In cases where I don't have control of all app code, this entails providing no read or write access to any table or view to any non-admin user, and I believe that restricting permissions like that is good practise even when I do control the apps as well as the databases (it took me some years to realise this). Also, I believe in having an xp_cmdshell proxy account with very restricted permissions (perhaps the intersection of the individual users' permissions, plus log_in_as_batch_job, but perferably not even that much permission) if xp_cmdshell access is to be granted to non sysadmin users, rather than letting them run code with the permissions of the service account - so if I want to allow a non-privileged user to initiate some privileged action that user can't do it by calling xp_cmdshell direct, he has to call some other stored procedure (provided by the database team) which runs with adminstrator privilege (and that privileged SP must not use one of its parameters as a command string!!!).
I don't see giving things with admin privileges access to xp_cmdshell as a security hole - if someone has an xp login with admin privileges they don't need xp_cmdshell (or any sql at all) to wreck the system, and if they have sa access they can create jobs which have jobsteps which are cmdexec or activescript jobsteps and schedule them, so they can already do anything that the SQLAgent Service account can do, which is usually enough to wreck the system. Only if the SQLAgent Service Account has privileges greatly restricted compared to the SQLServer Service account can there be a case for seeing xp_cmdshell as a potential security issue - and then disabling it doesn't help, since an attacker with sa permissions can easily enable it again. Will deleting xplog70.dll disable it cleanly, or will it disable too much else as well? (I don't know whether that's the right DLL in current SQL Server - that was the DLL that contained it in SQL Server 2000, where removing it certainly did disable far too much other stuff).
Tom
August 18, 2011 at 3:57 am
Be careful with “Xp_cmdshell “
I have to mention this also here. We know it’s easily create SQLinjection but, the another problem its removes the whole folder if you forget to remove the Q parameter in our codes.
That's my old story accidentally I had run my own DBA script. Just replaced the paths only I haven’t looked the parameter then finally the whole folder got deleted.(luckily the folders have only backups)
Disabling the XP_cmdshell is good thing not only disable it has to be totally removed from the SQL.but sometimes it needs because of poor management. What can we do? 😀
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 16 through 30 (of 93 total)
You must be logged in to reply to this topic. Login to reply