March 25, 2008 at 2:10 pm
I have the following line:
EXEC master.dbo.xp_cmdshell 'net use x: \\server1\sql$ /user:domain\sqltest passwd /persistent:no'
I want to replace the xp_cmdshell call with a call to a CLR sproc, like this:
exec dbo.CLR_usp_RunGenericCmdPromptCommand 'net use x: \\server1\sql$ /user:domain\sqltest passwd /persistent:no'
Does anyone have code to do this sort of thing?
(Any code sample running a command that is ordinarily run from Windows command prompt will do)
__________________________________________________________________________________
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]
March 29, 2008 at 3:33 pm
I haven't tried this in the CLR stored procedure but the System.Diagnostic.Process class might be of use.
Example:
Dim strCommand as string = "NET USE R: \\Server\Share"
System.Diagnostics.Process.Start("CMD.EXE",strCommand)
March 29, 2008 at 5:02 pm
Of course you should exercise caution in who has access to this procedure. For all of xp_CmdShell's security issues, custom-written replacements are likely to have even more.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2008 at 6:49 pm
Marios Philippopoulos (3/25/2008)
I want to replace the xp_cmdshell call with a call to a CLR sproc...
WHY??? A proxy account for xp_CmdShell will be much more secure than some adhoc CLR code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2008 at 7:58 am
Thanks all for your input.
Yes, it's true that by trying to replace xp_cmdshell with SQL CLR code that is just as generic opens the door to even more security-related issues.
I will stick to the more specialized SQL CLR objects I have been creating, each doing a strictly specialized task.
After all, I'm trying to get away from the generic nature of xp_xmdshell by using SQL CLR. The object I was trying to create when opening this thread defeats that goal, and I hadn't even realized that...
Thanks again!
__________________________________________________________________________________
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]
March 30, 2008 at 8:08 am
Jeff Moden (3/29/2008)
Marios Philippopoulos (3/25/2008)
I want to replace the xp_cmdshell call with a call to a CLR sproc...WHY??? A proxy account for xp_CmdShell will be much more secure than some adhoc CLR code.
The two main security concerns with xp_cmdshell seem to be the ability to execute arbitrary commands and potential shell injection attacks.
Both of those can be addressed by a CLR stored procedure. First, it would be easy to limit the types of commands to run within the shell. Second, the possibility of shell injection attacks can be minimized. Depending on what's being done in the code, using impersonation could make OS level auditing and access controls available based on a clients credentials, not a sysadmin or proxy account.
Adding command restrictions, input validation, auditing and access controls are all marks of improved security.
I just don't see how the OP has a bad idea.
References:
March 30, 2008 at 8:19 am
All great ideas Todd. I need to read up more on these issues, esp. how to code access security into my SQL CLR code. Lots to learn and you've opened that door!
Thanks!
I will take a look at those links u just posted.
__________________________________________________________________________________
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]
March 30, 2008 at 9:07 am
Todd Engen (3/30/2008)
Both of those can be addressed by a CLR stored procedure.
No... not in this case... not when the CLR does what the OP requested which is to basically replace any call to xp_CmdShell as if it were running xp_CmdShell. Go back and look at what the OP requested... the OP is essentially bypassing all security with the CLR. At least if you set up a non-mallable stored procedure and a secure proxy to use xp_CmdShell for that sproc, you avoid all the security problems you speak of.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2008 at 12:50 pm
Marios Philippopoulos (3/30/2008)
Thanks all for your input.Yes, it's true that by trying to replace xp_cmdshell with SQL CLR code that is just as generic opens the door to even more security-related issues.
I will stick to the more specialized SQL CLR objects I have been creating, each doing a strictly specialized task.
After all, I'm trying to get away from the generic nature of xp_xmdshell by using SQL CLR. The object I was trying to create when opening this thread defeats that goal, and I hadn't even realized that...
Thanks again!
I think that the OP (Marios) has already realized what many of us have been saying: replacing one general purpose command shell with another general purpose command shell won't do much to help your security. Thats because the real nature of the security hole in xp_CmdShell is the "General Purpose" part.
Given that Windows account delegation is not an acceptable approach for the general case (and it isn't, but that's a different discussion), the only other cost-effective thing that you can do to improve the situation is what Marios is now talking about: creating a set restricted functionality and restricted parameter interfaces.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 8:44 am
It's tough.
I have been trying to enforce this new initiative of leaving xp_cmdshell turned off on all our SQL Server 2005 instances and using SQL CLR instead. But there is a price to pay: developers come to me asking "how do I do this?" and "how do I do that without xp_cmdshell?", and there is pressure to find a quick solution.
I still think it's a worthy path, even if it's long and narrow... 🙂
__________________________________________________________________________________
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]
March 31, 2008 at 7:18 pm
rbarryyoung (3/30/2008)
Given that Windows account delegation is not an acceptable approach for the general case (and it isn't, but that's a different discussion), the only other cost-effective thing that you can do to improve the situation is what Marios is now talking about: creating a set restricted functionality and restricted parameter interfaces.
Again, wasn't suggesting a "general case". Proxy account to run reviewed stored procedures is going to be a lot easier on the DBA than trying to review some other source code written in a different language.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 4:04 am
Jeff Moden (3/31/2008)Proxy account to run reviewed stored procedures is going to be a lot easier on the DBA than trying to review some other source code written in a different language.
Jeff, I see your point.
It's a matter of perspective though. I personally think that DBAs have the responsibility to learn and master ALL features of SQL Server, which, for better or worse, make it to the product. That includes the CLR.
It's a tough proposition nowadays with the drastic expansion of the product since the 2000 version, but it should still be the goal of every DBA.
Being able to catch and review all instances of xp_cmdshell use can become a challenge in a large organization with a large number of databases, applications and developers. The inconvenience of DBAs learning how to use the CLR pales in comparison to the possibility of a SQL injection attack.
__________________________________________________________________________________
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]
April 1, 2008 at 6:40 am
So, you're saying that a DBA must learn all possible languages that may be compiled into a CLR and have the ability to review the source code from same? Heh... I don't think so.
And properly written stored procs that are properly and securely proxied as well as the proper security measures of "no developer shall have enough access to production to impart changes in code or data" that prevent all the SQL Injection Attacks and other security loop holes.
Heh... you can't even get most DBA's to review T-SQL before they allow it or put it into their database... you think a DBA is going to learn and seek out the source code for a CLR? With the possible exception of carefully written Regex, CLR's are the biggest security hole their is.
Besides, with the possible exception of Regex replacement code, there's not much that can't be done in T-SQL and with better performance than CLR's. Like cursors, While loops, and most DTS apps, CLR's are for those who don't know T-SQL well enough to get the job done in T-SQL.
Hey Matt and Sergiy! Get ready... here we go again!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 8:14 am
Jeff Moden (4/1/2008)
So, you're saying that a DBA must learn all possible languages that may be compiled into a CLR and have the ability to review the source code from same? Heh... I don't think so.
It's not that bad... There are 2 basic .NET languages, and in most organizations there are policies in place that restrict .NET development to 1. In my case it is C#. DBAs have the obligation to become familiar with all SQL Server technologies. It's a tough road, no doubt, but it needs to be taken.
And properly written stored procs that are properly and securely proxied as well as the proper security measures of "no developer shall have enough access to production to impart changes in code or data" that prevent all the SQL Injection Attacks and other security loop holes.
How do you propose that's done? xp_cmdshell is a free-for-all. Anything can be passed to it, and it's not always easy to find out what that is, especially if the argument is supplied dynamically at run time.
Heh... you can't even get most DBA's to review T-SQL before they allow it or put it into their database... you think a DBA is going to learn and seek out the source code for a CLR? With the possible exception of carefully written Regex, CLR's are the biggest security hole their is.
A totally unfounded statement. I don't know where you base this.
__________________________________________________________________________________
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]
April 1, 2008 at 9:43 am
Marios Philippopoulos (4/1/2008)
It's not that bad... There are 2 basic .NET languages, and in most organizations there are policies in place that restrict .NET development to 1. In my case it is C#. DBAs have the obligation to become familiar with all SQL Server technologies. It's a tough road, no doubt, but it needs to be taken.
Fine... let's flip that thought around... insist that the developers learn how to write performance enabled and scalable T-SQL... "It's not that bad". 😉
How do you propose that's done? xp_cmdshell is a free-for-all. Anything can be passed to it, and it's not always easy to find out what that is, especially if the argument is supplied dynamically at run time.
It's a free for all ONLY if you write a proc that allows it. I'm not suggesting that anything other than the proxy be allowed to run the shell. If you set up a proxy that has cmd shell privs and write a proc that does one thing well as all procs should, AND you don't allow developers to ever know the proxy login AND you don't make cmd shell available to the developers in production, it's easy. With a CLR, there are no guarantees... developers could slip in code to do just about anything they desire.
A totally unfounded statement. I don't know where you base this.
Personal experience and about a zillion posts on this very forum where about 80% of all those that call themselves DBA's say that "peer reviews" of code would slow things down so they don't do it! Ask how many DBA's allow developers to have write privs to production. As how many DBA's that review EVERY piece of code that goes in.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply