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 - 91 through 105 (of 168 total)

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

Elliott Whitlow (4/29/2011)
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

I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

Jonathan Kehayias (3/22/2010)
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?

Hey there. Sorry for being a year late to this, but I do have a function (several actually) to do exactly this in my SQL# (SQLsharp) library at: http://www.SQLsharp.com/. It does the recursion against subdirectories and allows for Regular Expression filtering (something that xp_cmdshell and dir /s isn't going to allow for). And my most recent release has proper streaming of the data so there is no real memory impact and it is scalable. The only caveat is that the full streaming capability was not properly done in SQL Server 2005 but in SQL Server 2008 and newer it is fixed. Also, the File System functions in the SQL# library are not free like most of the functions, but it is relatively cheap and sometimes you get what you pay for. Meaning: I have yet to see a blog post with example code (or any free code) that has either proper resource error handling or streaming. So if an error does occur when reading a file, it will be locked and you need to recycle the SQL Server service to release it!

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

Jeff Moden (4/29/2011)
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?

Jeff, basically it is to avoid setting the database to TRUSTWORTHY in order to run code that either accesses a resource outside of SQL Server or does something "unsafe" as far as managed code is concerned.

Jonathon has a good explanation here:

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 4

Solomon Rutzky (4/30/2011)
Jeff Moden (4/29/2011)
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?
Jeff, basically it is to avoid setting the database to TRUSTWORTHY in order to run code that either accesses a resource outside of SQL Server or does something "unsafe" as far as managed code is concerned.
Jonathon has a good explanation here:
http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx

That's a great post as is the MS link Jonathon provided in the post. But, it still doesn't appear to answer the question. Does the Asymetric key allow a low priv user to execute a proc that uses the assembly without being able to execute the CLR's directly?

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

Jeff Moden (5/1/2011)
Solomon Rutzky (4/30/2011)
Jeff Moden (4/29/2011)
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?
Jeff, basically it is to avoid setting the database to TRUSTWORTHY in order to run code that either accesses a resource outside of SQL Server or does something "unsafe" as far as managed code is concerned.
Jonathon has a good explanation here:
http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx
That's a great post as is the MS link Jonathon provided in the post. But, it still doesn't appear to answer the question. Does the Asymetric key allow a low priv user to execute a proc that uses the assembly without being able to execute the CLR's directly?

Jeff,

I don't understand what you are asking, or what you mean by "the CLR's"? What are you calling "the CLR's" exactly; the functions exposed by the assembly, the assembly itself, the CLR hosted environment, or the ability to create CLR Assemblies inside of the database?

The asymmetric key allows the assembly to to access external resources without having TRUSTWORTHY enabled. Access to the functions/procedures exposed by the assembly is the same as if it were a TSQL Function or Procedure, access to the assembly is based on the privileges a login has and would require the appropriate Assembly privileges in the database (BOL Topic for Assembly Privileges), as would the ability to create a CLR assembly in the database.

Not sure if that answers your question or not.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 6

Jonathan Kehayias (5/1/2011)
Jeff Moden (5/1/2011)
Solomon Rutzky (4/30/2011)
Jeff Moden (4/29/2011)
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?
Jeff, basically it is to avoid setting the database to TRUSTWORTHY in order to run code that either accesses a resource outside of SQL Server or does something "unsafe" as far as managed code is concerned.
Jonathon has a good explanation here:
http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/8/Default.aspx
That's a great post as is the MS link Jonathon provided in the post. But, it still doesn't appear to answer the question. Does the Asymetric key allow a low priv user to execute a proc that uses the assembly without being able to execute the CLR's directly?
Jeff,
I don't understand what you are asking, or what you mean by "the CLR's"? What are you calling "the CLR's" exactly; the functions exposed by the assembly, the assembly itself, the CLR hosted environment, or the ability to create CLR Assemblies inside of the database?
The asymmetric key allows the assembly to to access external resources without having TRUSTWORTHY enabled. Access to the functions/procedures exposed by the assembly is the same as if it were a TSQL Function or Procedure, access to the assembly is based on the privileges a login has and would require the appropriate Assembly privileges in the database (BOL Topic for Assembly Privileges), as would the ability to create a CLR assembly in the database.
Not sure if that answers your question or not.

I would add to this, for clarification, that CLR code (i.e. what is in the Assembly) cannot be called directly. The only way to access the methods within the Assembly are the T-SQL wrapper Procedures and Functions (as well as Types and Aggregates) that themselves can only point to the CLR method. You cannot mix, within a single Proc or Function, reference to a CLR method and regular logic/code.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 7

-- removed --

whoops didn't see there were more pages before replying.

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

Solomon Rutzky (5/1/2011)
The only way to access the methods within the Assembly are the T-SQL wrapper Procedures and Functions (as well as Types and Aggregates) that themselves can only point to the CLR method.

My apologies... my "lingo" on the subject certainly isn't correct. I was, in fact, speaking of the The "T-SQL wrappers" that Solomon pointed out.

I've got some more reading to do on the links Jonathon provided on assembly privs.

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

Martin Bastable SSCommitted Points: 1766 More actions · Answer 9

Just wanted to add my experience of CLR....

Used in the right place (i.e. where it was worth optimisation), it doubled the speed of some of my TSQL (doing some heavy string manipulation in my case), which was significant in the context of where I used it.

For me it also allowed code reuse (D.R.Y.). e.g. I have functions that are shared in use across a windows app, (indirectly) ms access, web app, tsql. All call the same .net functions. Very handy not having to re-write/test seperate versions and gives me consistency of problems. (This might sound strange, but as part of my case, im using various encoding methods for data matching/de-duplicating data from multiple sources. If there are problems due to a bug in one version of a function on one platform encoding differently to another, then thats bad. At least if all versions behave the same, then I have consistency. And a single point of update requirements). Luckily I haven't had any problems, but I feel its good to plan 🙂

M.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

Elliott Whitlow (4/29/2011)
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

Thanks for that, I found it via Google and have used the MFGetDirectoryList as a jumping off point. Based on Jonathan's comment earlier in this thread, I added in a check that the passed directory actually exists, and return an empty result set if not. I also added both the FileExtension and Archive attributes as 2 new columns returned in the TVF.

Including FileExtension (N.B.: you have to strip off a leading ".") allowed me to remove the passed-in file extension parameter: I just use a WHERE clause on the TVF. Yeah, I return more data than I need, but to me the logic is cleaner and I'm unlikely to be parsing directories with more than a few hundred files anyway.

The ability to grab the archive bit is great, b/c now I can use the TVF to find, say, SQL backup files that haven't been backed up to tape. Prior to this, I had a clunky work-around that (1) periodically called an Agent job, that (2) called a batch file with a DOS ATTRIB command, that (3) sent its output to a text file, that (4) was then consumed by SSIS and dumped into a SQL table. Whew! This is much cleaner.

So thanks to you and to Jonathan for encouraging me to look into CLR as a solution here. I'm new to CLR, and there's been a few gotchas I've had to learn and quite a few pieces to implement.

Jonathan, I couldn't ask for a better walk-through than http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx for creating an asymmetric key (and avoiding SET TRUSTWORTHY). Thanks!

Finally, I'd like to offer a nice quote from Beginning SQL Server 2005 Administration, publised by Wrox, on the issue of CLR and security:

"Before you get excited about using terms like 'Unsafe,' let's put this into perspective. We have had the capability to extend SQL Server's reach using external applications and components for several years in the form of command-line executables and extended stored procedures. Many trustworthy SQL Server solutions send email messages using COM-based Collaboration Data Object (CDO) code or interact with the file system using external VBScript.... The point is that these are all examples of what is now called 'unmanaged code' and falls into the category of 'Unsafe' code from the perspective of a SQL Server CLR object.
Using the Unsafe setting .... just means that the .NET CLR can't guarantee that it's safe -- you and your developers have to do that, just like many of us have been doing for the past 10 or 12 years."

Thanks,

Rich

Chrissy321 SSCoach Points: 15769 More actions · Answer 11

I know I'm late to this party but does this code traverse subdirectories?

Thanks

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

Chrissy321 (2/7/2012)
I know I'm late to this party but does this code traverse subdirectories?
Thanks

The SQL# package that Solomon mentioned a few posts up this thread does http://www.sqlsharp.com/[/url]

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

Jeff Moden (4/29/2011)
Elliott Whitlow (4/29/2011)
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
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?

Jeff,

I'm sorry I missed this one and I know it is an ancient post..

But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.

Not sure if this fully answered your question..

CEWII

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

Elliott Whitlow (2/7/2012)
Jeff Moden (4/29/2011)
Elliott Whitlow (4/29/2011)
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
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?
Jeff,
I'm sorry I missed this one and I know it is an ancient post..
But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.
Not sure if this fully answered your question..
CEWII

Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.

Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.

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

Rich Mechaber SSChampion Points: 11130 More actions · Answer 15

Jeff Moden (2/8/2012)
Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.
Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.

Jeff, if you're only manipulating data passed to a CLR-based UDF or SP, then SAFE works and no key is needed. The explanation of what you can and cannot access with CLR at the different security levels is summarized here:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

To quote:

"SAFE

Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. "

So if you don't need access to those external system resources, you can create the assembly as SAFE.

As an example, if you are using a CLR-based UDF to validate a passed-in email address, you only need to create the assembly with SAFE permission set. If you want to create a CLR-based TVF that returns file information, then SAFE won't work.

Rich