Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Question

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Viewing 15 posts - 76 through 90 (of 168 total)

You must be logged in to reply to this topic. Login to reply

Thor (Hammer of God) SSC Veteran Points: 246 More actions · Answer 1

Perry Whittle (2/7/2010)
hi i realised that. But whether sql, windows (NTFS), etc be careful with deny always revoke where possible

You can't "revoke" NTFS permissions - you have allow and deny. Same with firewall rule actions. That said, I actually leverage Deny access controls extensively within my infrastructures. Since DENY takes precedence over any other ALLOW permissions, and any combination or resultant permissions that might result in an ALLOW, DENY can be quite powerful and in some cases, desired.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

you revoke NTFS permissions by not adding entities to the resource ACL list, this prevents access but doesnt deny. Access could still be granted via membership of another group.

All I'm saying is be careful with deny

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Thor (Hammer of God) SSC Veteran Points: 246 More actions · Answer 3

Perry Whittle (2/8/2010)
you revoke NTFS permissions by not adding entities to the resource ACL list, this prevents access but doesnt deny. Access could still be granted via membership of another group.
All I'm saying is be careful with deny

Ah - understood. The term "revoke" was throwing me off. I think "don't assign" would be better placed, but your point is now taken. One certainly shouldn't just "deny" willy nilly without understanding what the ramifications are.

In fact, your point is probably better made within the actual context of my usage, where my SQL Server Service user belongs to a group of other service users that I explicitly assign "Deny access to this computer from the network" to (most) hosts to limit what a SQL compromise can yield in regard to attacks down-range. You've certainly got to implement this type of security measure with care.

t

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

totally agree Thor.

the revoke thing is a little ambiguous i agree. I understand it as

if you take the user account out of the ACL you have neither allowed or denied, its null or "Revoked". Microsoft use the term revoke to indicate an action that implies no access definition (i.e. neither allowed or denied). Once you done a few of the MS courses and taken some of their exams it soon starts to sink in 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ron Jennings SSC Enthusiast Points: 137 More actions · Answer 5

Jonathan,

This function is exactly what I was looking for as part of a SQL Server Disaster Recovery solution I'm developing. The only additional thing I would need to do would be to recurse through all the subdirectories and return information on all files discovered therein. Do you have any tips on how to go about making the function recursive?

Thanks!

~ Ron

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Ron Jennings (3/22/2010)
Jonathan,
This function is exactly what I was looking for as part of a SQL Server Disaster Recovery solution I'm developing. The only additional thing I would need to do would be to recurse through all the subdirectories and return information on all files discovered therein. Do you have any tips on how to go about making the function recursive?
Thanks!
~ Ron

You would have to change the C# code to perform the recursion, but I don't know that I would do that all at once for performance/memory scalability reasons. What specifically are you trying to do that needs it to recurse like that?

Ron Jennings SSC Enthusiast Points: 137 More actions · Answer 7

I want to pass in a top-level backup directory path and have the procedure list all files found in all subdirectories. The idea is to build a list of backup files found in the directory tree, so that I can then grab header information from the backup files themselves to build a list of databases that can be recovered from the files in the directory.

I'm thinking I will probably need to create my own recursive class that uses the GetFileSystemInfos method, then call that method in the os_directory_info method.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

It's a real shame that we have to try to rewrite that which works so well...

[font="Arial Black"]Dir c:\*.* /s /b[/font]

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Since this sounds like a DBA only function in a recovery scenario, I'd probably enable xp_cmdshell for the duration of the recovery and turn it back off once you finish restoring. In a recovery scenario are you really going to want to first Enable CLR for SQL, then deploy the function, and then be able to get your process for recovery running, or do you want to be able to open a script, fire it off, and have it begin the recovery process immediately?

Ron Jennings SSC Enthusiast Points: 137 More actions · Answer 10

It is a DBA only function, but we already have CLR enabled, and we already use managed code for other DBA functions. When we create our utility database on a recovery server, any CLR functions will be created along with it, so they will be available for DBA use. I could certainly write this using xp_cmdshell, but it would be kludge-y, and I'd prefer to avoid that if possible.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

Jeff Moden (3/22/2010)
It's a real shame that we have to try to rewrite that which works so well...
[font="Arial Black"]Dir c:\*.* /s /b[/font]

Good old DOS Jeff, you can't beat it can you 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Perry Whittle (3/22/2010)
Jeff Moden (3/22/2010)
It's a real shame that we have to try to rewrite that which works so well...
[font="Arial Black"]Dir c:\*.* /s /b[/font]
Good old DOS Jeff, you can't beat it can you 😀

Yes Sir... and I don't understand why MS doesn't understand that there is a need to do file handling in places other than SSIS and CLRs like T-SQL for instance. xp_DirTree will do similar (although the file path is a bit elusive there) if the 3rd operand is used. It seems only natural that file handling and ETL go together, so it seems only natural that there should be some additional file handling capabilites in T-SQL that is currently sorely missing.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 13

example here of using c# clr proc to zip and ftp backups. Also using asymmetric key.

http://sql-library.com/

www.sql-library.com[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

Sorry... posted against a 2 year old post and removed it...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 15

Oh well.. I released a SQL CLR file function library on codeplex in march 2011. It is at:

http://nclsqlclrfile.codeplex.com/[/url]

Let me know what you think, what is wrong with it, and anything that might be added.

CEWII