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 - 46 through 60 (of 168 total)

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

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

Jeff Moden (2/6/2009)
Jonathan Kehayias (2/6/2009)
so I did it the right way, rather than the easy way
BWAA-HAA!!! Man, I'm right there with you on that one! Now, all we have to do is convince the rest of the world, especially the managers that want it real bad, to give people just a little more time so they don't get it that way... real bad.
Good talking with you again, Jonathan. Kudos on the article.

I've got some more coming that it will be fun to see your comments on. All along this same line of processing, but its always interesting to see what you come up with for TSQL equivalents to the SQLCLR. As you say, the places I can write CLR to beat TSQL has been limited, and some of your tricks like appending a '/' at the end of a URL in TSQL to make domain name extraction lightening fast are very interesting and something to learn from.

katesl SSCommitted Points: 1743 More actions · Answer 2

This solution is based on the premise that security is important enough to take the time to implement. If time is more important than preventing unrestricted access to the OS as the SQL Service account with xp_cmdshell, then this solution probably isn't for you.

There is nothing on the machine other than SQL Server databases protected by failover or log shipping. So what if the machine crashes; that's just a blip. If a hacker has ever been able to copy an .mdf file from a machine to outside the firewall because xp_cmdshell was turned on, I would like to read about it. Security isn't about the OS; it is about the company's data.

Data security principle -- developers make the access to the data visible to the administrators who are responsible for the security of production data, after the developers go on to other projects, by writing simple, compact T-SQL, tested for performance. What the application does with the data should not be hard to see inside a pile of clever code-- that is a data security issue. It's not about how easy or hard it is for the developer to write the code; it is about how the code is maintained and explained two years later.

I'm talking about database programming for business applications. I suppose UI considerations are primary for other applications.

My SQL Server policy is xp_cmdshell on and CLR off -- unless I hear of theft of an .mdf that was accomplished using xp_cmdshell.

And thanks to someone for fixing the window that displays code. It's normal width now.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

That was it and I agree... someone like you wouldn't pass a bad path.

Remember the advice in 'The C Programming Language by Dennis M. Richie and Brian W. Kernighan' to always put in error handling even when an error couldn't possibly happen? I've lost count of the number of times I've thanked those guys for that advice. I've seen the error 'this cannot possibly happen' more times than I'd like to admit.

I'm not going to back down over my gentle advice that the CLR must react properly to all possible inputs. After all, it was because people neglected this that SQL Injection became possible. If a routine is for your own private use, then bless you, but if it is published on the internet then prepare for a comment by Phil.

Oh and another thing; it was particularly the use of the Asymmetric key that I was so pleased about in my original comment. It is sooo important.

Thanks for the update. I've now trashed my old CLR Directory routine, and adopted yours instead.

I don't yet see CLR as being a simple substitute for OLE automation. OLE is much quicker to lash together and maintain, if you have good templates and test harnesses. I see CLR more as a way of providing extensions to the SQL Server system to handle complex data. Now that is really exciting.......

Best wishes,
Phil Factor

David Dye SSC Veteran Points: 273 More actions · Answer 4

David Dye

SSC Veteran

Points: 273

February 7, 2009 at 6:25 am

#939953

Great article Jonathan!

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

Jeff Moden (2/6/2009)
.... One of my major points was that someone posted some a link to some pretty bad T-SQL and the race between that
and a CLR and a test that probably wasn't even done correctly.....

Boy, am I glad to be "someone" now.....

I was perfectly aware that the T-SQL code is not the best. The idea of providing that link was to show the reasoning behind Andy's article on possible applications for SQLCLR. He explicitly wrote that the code is not the best.. I hope people read!

Besides that, it shows with plenty-o-external-links the process on creating a CLR, informative for anyone..

Yes, yes, mea culpa on trying to show everyone bad coding and trying to point that "people don't go CLR crazy". If someone just takes any code thrown at them without even thinking on the consequences of applying it in their environment, they have no business being a DBA/Developer.

At least it sparked a series of very informative discussion between you and Jonathan, with your always excellent code samples 🙂

Cheers

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Phil Factor (2/7/2009)
I see CLR more as a way of providing extensions to the SQL Server system to handle complex data. Now that is really exciting.......

For what it's worth, that's more of what I saw CLRs being used for instead of a convenient method for people who don't know T-SQL to still write code against a database. Regex, the things you wrote about, and, if you want to skip SSIS, file handling are a couple of great places for properly written CLRs.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Richard M (2/7/2009)
Boy, am I glad to be "someone" now.....

More than that, I've got a pretty healthy respect for you because of the way you just responded and have responded in the past. It wasn't my intent to make it personal and, thank goodness, you didn't take it personal. Thank you, Sir!

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

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

Jonathan Kehayias (2/6/2009)
I've got some more coming that it will be fun to see your comments on. All along this same line of processing, but its always interesting to see what you come up with for TSQL equivalents to the SQLCLR. As you say, the places I can write CLR to beat TSQL has been limited, and some of your tricks like appending a '/' at the end of a URL in TSQL to make domain name extraction lightening fast are very interesting and something to learn from.

Knowing your abilities, there will undoubtedly be things where T-SQL and I just don't stand a chance, but I sure do look forward to trying. I always look forward to anything written by Jonathan Kehayias. And, like Phil, I'm tickled that, if our group at work does come up with the need for a viable CLR, we now have a great template to follow that considers the right kind of security that the Systems DBA's are likely to buy. Thank you, Sir, and very well done!

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

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

Hey there - thanks for the article; it was just what I was looking for.

I've decided to deploy my CLR via VS2010, using a VB SQL CLR project. However, my code requires system.directoryservices. My directoryservices code works fine in a normal vb module where I can add the reference to system.directory services, however, in my VB SQL CLR module, I don't have the ability to do so. I'm suing the CLR project for the "auto deployment" scenario you speak of.

Do you have any recommendation on how to 1) import system.directoryservices into my CLR project, or 2) compile my code and "manually" add it as a clr?

Thanks man.

Thor (Hammer of God)

Pooja-462303 SSC Veteran Points: 210 More actions · Answer 10

Thank you for the article, i was able to implement a simple SQLCLR assebly, It just does basic file operations , the function receives the Source path and Destination path from the calling procedure and the SQLCLR function ( written in C#) only copies the file to the destination path.

Everything was excelled when deployed, the apps showed a big boost in performance..

Suddenly after about 3hr, the SQLCLR function start throwing .NET exceptions. :

" An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues.

System.IO.FileLoadException: Could not load file or assembly 'FileCopy' "

The fuction was called extensively ( about 10 call evey 10 secs ).

Could someone please comment on this, about why this happened. I thought .NET takes care of memory leaks on its own, and this does not need to be handeled in the code.

Any comments on this will be greatly appreiciated.

Thanks in Advance!!

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

What is your C# code for the function? I am guessing that this is a 32 bit SQL Server? It may not have anything to do with a memory leak, or failure to deallocate the memory used, but instead with VAS fragmentation over time when coupled with other VAS consumers in your environment, something very specific to 32 bit servers where the VAS is limited and SQL Server only takes a default reservation of 384MB (which is very small) for running VAS consumers like SQL CLR. We need more information like the C# code, whether SQL is 32 or 64 bit, and the amount of physical ram installed on the server.

Pooja-462303 SSC Veteran Points: 210 More actions · Answer 12

Hi Jonathan

Thanks for replying back..

Server is Win Server 2003 R2 , 64 bit edition,

32 GB RAM installed,

Microsoft SQL Server 2005 - 9.00.4229.00 (X64)

Jul 29 2009 14:59:51

Standard Edition (64-bit) on Windows NT 5.2

(Build 3790: Service Pack 2)

C# code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlInt32 udf_doFilecopy(SqlString sSrc, SqlString sDest)

{

try

{

if (File.Exists(sDest.Value) == false && File.Exists(sSrc.Value))

{

FileInfo fileInfo = new FileInfo(sDest.Value)

if (Directory.Exists(fileInfo.DirectoryName))

{

File.Copy(sSrc.Value, sDest.Value);

return (0);

}

else

{

Directory.CreateDirectory(fileInfo.DirectoryName);

File.Copy(sSrc.Value, sDest.Value);

return (0);

}

else if (File.Exists(sDest.Value) == true)

return (1);

else if (File.Exists(sSrc.Value) == false)

return (2);

else

return (3);

}

catch (Exception e)

{

return (-1);

}

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

What is Max Server Memory configured to on the server and is it using Lock Pages in Memory?

Pooja-462303 SSC Veteran Points: 210 More actions · Answer 14

Max Memory for SQL: 20GB

Lock pages in memory is enabled in for the user with which the SQL runs

Perry Whittle SSC Guru Points: 233854 More actions · Answer 15

just a sanity check, have you verified in the sql server logs that SQL server is using locked pages. Check the instance startup text in the log to verify lock pages is in force

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

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