Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1112131415»»»

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 9:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 2,036, Visits: 1,377
I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion.

And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.
Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.
Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.
Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.

Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.

Regards
Lempster
Post #1349218
Posted Thursday, August 23, 2012 1:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
Lempster (8/23/2012)
I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion.

And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.
Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.
Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.
Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.

Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.

Regards
Lempster


Very cool feedback all around.

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1349337
Posted Thursday, August 23, 2012 6:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
As for not having the source code, that might not be as big of a thing as you think. If you can extract the assembly as a file then you can the go ask the good folks at Red Gate. They have a fine product that helps with Dot Net assemblies.

ATB

Charles Kincaid

Post #1349409
Posted Friday, August 24, 2012 8:17 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Jeff Moden (8/23/2012)

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).


Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,
Rich
Post #1349697
Posted Friday, August 24, 2012 10:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
rmechaber (8/24/2012)
Jeff Moden (8/23/2012)

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).


Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,
Rich


I have a demo script at home that I could share. I'll post it tonight.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1349788
Posted Wednesday, October 3, 2012 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:51 AM
Points: 2, Visits: 8
Hi there
Sorry if this came up already, I skimmed through the thread and couldn't see anything, but I have upgraded a 2005 installation which had this CLR assembly installed for copying files. I set up the same assembly from an asymmetric key in SQL 2008 R2 but when I try to use it I get error:

The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.

Anybody have any pointers as to what permissions I need to set - would this be a permission problem for the login I created against the key?

Post #1367550
Posted Wednesday, October 3, 2012 6:19 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Jeff Moden (8/24/2012)
rmechaber (8/24/2012)
Jeff Moden (8/23/2012)

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).


Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,
Rich


I have a demo script at home that I could share. I'll post it tonight.

Hi, Jeff, any luck running down that demo? I'd very much appreciate it!
Rich
Post #1367609
Posted Wednesday, October 3, 2012 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:51 AM
Points: 2, Visits: 8
I resolved my error:
The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.
when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.
Sorry for polluting the conversation with newbie stuff
Post #1367734
Posted Wednesday, October 3, 2012 10:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
rmechaber (10/3/2012)
Jeff Moden (8/24/2012)
rmechaber (8/24/2012)
Jeff Moden (8/23/2012)

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).


Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,
Rich


I have a demo script at home that I could share. I'll post it tonight.

Hi, Jeff, any luck running down that demo? I'd very much appreciate it!
Rich


My apologies. I lost track of this thread. I'll try to post the demo tonight. Yeah... I know... said that before and, again, apologies for not following up.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1367855
Posted Wednesday, October 3, 2012 10:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
robert.baker 21596 (10/3/2012)
I resolved my error:
The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.
when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.
Sorry for polluting the conversation with newbie stuff


Nah... it's good stuff. Thank you for taking the time to post back. It'll help others.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1367865
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»»

Permissions Expand / Collapse