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 - 61 through 75 (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

OK - I'm back 😉 I've been doing far more research on the subject now, and have gone through your code bit by bit (no pun intended, Johnathan) and I've figure most things out.

I've got your code running and deploying fine in VS2010, and I'm sure it would have manually posted as well. I've ported my original code over to C# just to be playing on the same field, and it runs fine on its own, and compiles fine as a CLR project.

Here's my issue: I need system.directoryservices - the code alters user group membership. Adding the reference works fine in a console app, but you can't add the reference via the GUI for a CLR C# project. Messing around a bit, I found that adding the using clause as well as editing the .csproj file to include the same (in the xml) allows the project to parse and build just fine.

However, when I go to deploy the assembly, I get:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

I tried to manually CREATE ASSEMBLY for the system.directoryservices.dll, but it failed verification. Can someone please point me in the right direction here? This seems pretty straight forward, and I'm not sure what I'm missing.

t

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

Thanks for your input. but I check the server logs : i see the following :

" AppDomain 35 (DBName.DBName[runtime].34) is marked for unload due to memory pressure. "

I also see the following message :

"

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 582332,

"

I have SQL Server 2005 SP3 installed on the Server.

any ideas .. How i can have the DLL not marked for unloading.

Thanks advance

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

Pooja-462303 (2/5/2010)
Thanks for your input. but I check the server logs : i see the following :
" AppDomain 35 (DBName.DBName[runtime].34) is marked for unload due to memory pressure. "
I also see the following message :
"
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 582332,
"
I have SQL Server 2005 SP3 installed on the Server.
any ideas .. How i can have the DLL not marked for unloading.
Thanks advance

That working set trim message telling you that the process was paged out is a sure sign that you aren't getting locked pages in SQL. The AppDomain Unload will happen as long as you are having memory pressure issues on the SQL Server and you'd need to start by getting lock pages corrected so that the process isn't paged out. What else is happening on the SQL Server? Large file copies, AV scan's, third party backup tools, SSRS, SSIS, SSAS?

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

So, is there some secret handshake that I don't know? 🙂 Just hoping for some input to the system.directoryservices issue - if I'm not phrasing the question properly or not giving enough information, please let me know. Thanks.

t

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

thor 67519 (1/29/2010)
OK - I'm back 😉 I've been doing far more research on the subject now, and have gone through your code bit by bit (no pun intended, Johnathan) and I've figure most things out.
I've got your code running and deploying fine in VS2010, and I'm sure it would have manually posted as well. I've ported my original code over to C# just to be playing on the same field, and it runs fine on its own, and compiles fine as a CLR project.
Here's my issue: I need system.directoryservices - the code alters user group membership. Adding the reference works fine in a console app, but you can't add the reference via the GUI for a CLR C# project. Messing around a bit, I found that adding the using clause as well as editing the .csproj file to include the same (in the xml) allows the project to parse and build just fine.
However, when I go to deploy the assembly, I get:
Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.
I tried to manually CREATE ASSEMBLY for the system.directoryservices.dll, but it failed verification. Can someone please point me in the right direction here? This seems pretty straight forward, and I'm not sure what I'm missing.
t

I'd recommend that you change your implementation because this isn't a Database task it is an application task. What is the dependency here that is making you think this should be handled by the database engine and not your application tier? I need to know more so I can offer ideas about the best alternative. If your goal here is to cause an update/change in AD as the result of a change in the database Service Broker and External Activation are a better solution and don't require you doing hokey, hacked up SQLCLR implementations.

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

thor 67519 (2/5/2010)
So, is there some secret handshake that I don't know? 🙂 Just hoping for some input to the system.directoryservices issue - if I'm not phrasing the question properly or not giving enough information, please let me know. Thanks.
t

Nope, no secret handshake, it just depends on when/if I get notification of a post back to comments I follow and I when/if I have the time to post a response back. I replied to your question as well. See my last response.

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

Thanks for the repsonse - much appreciated. The goal here is multi-fold: Primarly, this serves as an example of replacing xp_cmdshell where the SQL engine is acting on database changes - in this case, specifically, SQL is the logging mechanism for firewall proxy logs. SQL is constantly evaluating log entries for user-defined destination patterns where the originating user is automatically placed in an AD group that blocks outbound traffic at the firewall. The group and rules are already set up on ISA, and just the membership needs to be altered. The SQL user (meaning the user context the SQL service is running under) has delegated permissions to alter the group membership (and only that). This approach is far more secure than xp_cmdshell'ing out to a net group cmd. The point here is for SQL to autonomously react to log entries and execute the transaction to add the offending user to the deny group.

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

Jonathan Kehayias (2/5/2010)
thor 67519 (2/5/2010)
So, is there some secret handshake that I don't know? 🙂 Just hoping for some input to the system.directoryservices issue - if I'm not phrasing the question properly or not giving enough information, please let me know. Thanks.
t
Nope, no secret handshake, it just depends on when/if I get notification of a post back to comments I follow and I when/if I have the time to post a response back. I replied to your question as well. See my last response.

That was, of course, tongue in cheek 🙂 My reply is posted as well. Thanks again.

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

thor 67519 (2/5/2010)
Thanks for the repsonse - much appreciated. The goal here is multi-fold: Primarly, this serves as an example of replacing xp_cmdshell where the SQL engine is acting on database changes - in this case, specifically, SQL is the logging mechanism for firewall proxy logs. SQL is constantly evaluating log entries for user-defined destination patterns where the originating user is automatically placed in an AD group that blocks outbound traffic at the firewall. The group and rules are already set up on ISA, and just the membership needs to be altered. The SQL user (meaning the user context the SQL service is running under) has delegated permissions to alter the group membership (and only that). This approach is far more secure than xp_cmdshell'ing out to a net group cmd. The point here is for SQL to autonomously react to log entries and execute the transaction to add the offending user to the deny group.

I disagree that this is a place where CLR should replace xp_cmdshell functionality because this should have never have been handled using xp_cmdshell to begin with, its application functionality not SQL functionality. Using xp_cmdshell was akin to using a hammer because that's all you have. This should be abstracted to an external service that handles the AD changes under a different account. Whatever TSQL is doing the evaluation of this currently could be called by an external application and rather than trying to CLR out of SQL the SQL Engine just returns a datatable to the app and the app takes care of it from there.

You are being vague in your description, so I can't offer more details to implementation. You have multiple ways to do this that don't involve xp_cmdshell or SQLCLR that are better fits.

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

I disagree that this is a place where CLR should replace xp_cmdshell functionality because this should have never have been handled using xp_cmdshell to begin with, its application functionality not SQL functionality. Using xp_cmdshell was akin to using a hammer because that's all you have. This should be abstracted to an external service that handles the AD changes under a different account. Whatever TSQL is doing the evaluation of this currently could be called by an external application and rather than trying to CLR out of SQL the SQL Engine just returns a datatable to the app and the app takes care of it from there.
You are being vague in your description, so I can't offer more details to implementation. You have multiple ways to do this that don't involve xp_cmdshell or SQLCLR that are better fits.

I can certainly see that logic, but would have to say one could always abstract out the functionality to a separate application layer process, even within the directory posting example you build this tutorial around. I'm not qualifying the validity of your application to CLR, but simply identifying the fact that one could appy that logic to just about any process.

I suppose we could discuss the differences between a stand-alone application accessing the log data and executing the process on its own as opposed to the SQL box doing it, but I don't really see that much of a difference in where the process is called from when comparing similar methodologies of executing the function.

I take it that such a function living within CLR has associated risks that I am not aware of? If so, can you take a moment to elaborate on them? Your suggestion actually makes it much easier for me to deploy, but again, this is more of an example of structure and capability than it is a "preferred" way of doing this. I think if the purpose of the SQL deployment is to log firewall data, perform analysis on it, and execute commands based on defined criteria, that SQL executing the AD addtion would be OK. Of course, one may take heed of the fact that system.directoryservices is NOT yet "approved" for use with SQL CLR (hense it's obviation from selection) so that may mean something on its own.

t

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

Thor (Hammer of God) (2/5/2010)
I can certainly see that logic, but would have to say one could always abstract out the functionality to a separate application layer process, even within the directory posting example you build this tutorial around. I'm not qualifying the validity of your application to CLR, but simply identifying the fact that one could appy that logic to just about any process.

There is a fundamental different between the example and the process you are attempting to produce. The example has many uses internally for administration and retrieves a result set for use in further SQL processes which is what makes using a standalone application to get the directory information a more complex solution without benefit. It would in fact require delays in usage code to wait for the external app to dequeue a request, gather the directory info and then queue it back for the processing to continue (which is really simple to do with Service Broker and External Activation if you so chose to go that route). I have used the example function to get information for automated restore processes of development/test servers from production backups on demand by the developers/testers. The information feeds back into dynamic SQL restore script generation.

I take it that such a function living within CLR has associated risks that I am not aware of? If so, can you take a moment to elaborate on them? Your suggestion actually makes it much easier for me to deploy, but again, this is more of an example of structure and capability than it is a "preferred" way of doing this. I think if the purpose of the SQL deployment is to log firewall data, perform analysis on it, and execute commands based on defined criteria, that SQL executing the AD addtion would be OK. Of course, one may take heed of the fact that system.directoryservices is NOT yet "approved" for use with SQL CLR (hense it's obviation from selection) so that may mean something on its own.

You name one of the biggest problems I would have with it, its not approved which leads to some other issues. First you had to go with TRUSTWORTHY ON for your database, which creates a security risk because now code internally can escalate permissions based on the permissions of the login that owns the database. If that happens to be sa, you have unsecured access to the entire instance. You also had to jump to UNSAFE here which isn't necessarily something I have a problem with in general, but it isn't the best fit in this case which makes it a problem.

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

You name one of the biggest problems I would have with it, its not approved which leads to some other issues. First you had to go with TRUSTWORTHY ON for your database, which creates a security risk because now code internally can escalate permissions based on the permissions of the login that owns the database. If that happens to be sa, you have unsecured access to the entire instance. You also had to jump to UNSAFE here which isn't necessarily something I have a problem with in general, but it isn't the best fit in this case which makes it a problem.

That is indeed what my initial concern was. However, in this case, the permissions are tightly constrained. Nothing is done as SA. However, your points are well taken.

So, the biggest part to me here (please correct me if I am wrong) is the global setting of TRUSTWORTHY to the instance in order to accomodate a single function, and that function in itself being of questionable disposition insofar as its appropriateness within CLR in the first place.

(Thinking out loud here…) Given this, one should not take this function as an opportunity to represent the capabilities of CLR since the context of this example represents security risks made greater by attempting secure the base process to begin with. Yeah. That.

So, write the app and call it from a job then. SQL can just write the offending usernames to a table and the app access a view with read-only permissions and execute AD functions from that. I like that.

Thank you for taking the time to share your ideas.

t

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

the other issue here is your explicit use of deny, always revoke rather than deny excedpt where absolutely necessary. Deny can have some very undesirable effects

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

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

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

Perry Whittle (2/6/2010)
the other issue here is your explicit use of deny, always revoke rather than deny excedpt where absolutely necessary. Deny can have some very undesirable effects

Hey Perry - my "explicit deny" was in regard to ISA/TMG firewall policies, not SQL permissions; but thank you for the input.

t

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

hi i realised that. But whether sql, windows (NTFS), etc be careful with deny always revoke where possible

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

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