System.Web in SQL Server CLR Function

I have done some light research into this topic and I would like to know what are all the pro's and con's or enabling/registering this particular .dll within SQL Server?

Back information - we are integrating with a third party application (not my decision, unfortunately) which requires this .dll for some of it others .dlls. What I need the CLR Function for is to be able to write SQL Queries in SSMS and have that data sent to the third party application's API which then would in turn do the correct data load/changes (inserts and deletes to/from this application has to be done via its API).

EDIT - maybe I shouldve included this detail

When trying to register my c# class I obviously got the error "system.web not registered blah blah blah" which has then in turn prompted my research on this topic.

end edit

So, my conundrum is that to be able to register my C# class/.dll, I have to register all the dependent .dll's, however based on my research I know that this particular one can be quite problematic.

So seeing in how I am not terribly familiar with the pitfalls outside of my google research, I was wondering if one of you fine people could help me understand how to make the best decision in regards to this.

Also, what else can I add to this post so that being able to give insight is easier? I wasnt too sure the C# code was relevant? I understand this might be a bit broad, but I was hoping that its specific enough to not get flagged?

To be more specific to whats occurring here (per Solomon's request)

-
The 3rd party app uses an "API" (used loosely because I am told it is not a great API) to send data back and forth. You'll notice it calls an Importer function which only takes a data table or an excel file which it converts. I have no other option as the the company told me that inserts and deletes via normal XML is terribly slow and has unexpected behavior.

-
The .DLL that references System.Web is referenced within my C# class, which is required to be ab

That the 3rd party software requires the DLL does not seem relevant here. It seems that the issue is simply:

I need ... to be able to write SQL Queries in SSMS and have that data sent to the third party application's API, which then would in turn do the correct data load/changes (inserts and deletes to/from this application has to be done via its API).

I believe your best two options are:

-
If this process is as simple as you have stated, and you really want to stick with System.Web, then why not create a console application that does the query and then posts whatever you need to the API? If this needs to be done on a regular basis, it can be scheduled in either Windows Task Scheduler or SQL Server Agent (via an "Operating system (CmdExec)" job step).

-
If this process is a little more complex and/or you need the ability to do these queries in a more dynamic / ad hoc fashion, then System.Web isn't required, it just makes some of the coding easier. Intead, look into HttpWebRequest and HttpWebResponse. Those two are included in System.Net, which is one of the supported .NET Framework Libraries for SQLCLR. The main difference is that you will need to construct the XML request manually. Same goes for dealing with the response. This approach is more stable and secure than importing System.Web.

Along these lines, for anyone needing such functionality but not wanting (or able) to do the coding of it, I wrote a library of SQLCLR functions and procedures — SQL# — that includes a stored procedure, INET_GetWebPages, that implements those two methods. While there is a Free version, this particular stored procedure is only available in the Full (i.e paid) version. However, it does handle a variety of options / scenarios, including: setting various HTTP headers, optional basic authentication, proxy server, sending GET vs POST data, handling of PUT requests, and options for reducing potential negative impact on performance.

IF you are saying that you are using the 3rd party app's DLL to make the API calls via their supplied methods, and it is their DLL that references and uses System.Web (and not your code directly), then it is hard to say with complete certainty what all of the impacts are. But here are some things to consider regarding this approach:

System.Web is not in the Supported .NET Framework Libraries list for SQLCLR. This means, at the very least:

• This library is not guaranteed to be loadable in SQL Server. Even if it currently can be loaded, SQL Server only allows pure MSIL libraries and will not load mixed-mode libraries (i.e. DLLs with both managed and unmanaged code in them). There is no way around this restriction. Because this library is not on the list, it can change from pure to mixed in a future .NET Framework update. If this happens, this code will stop working since this is a Framework library and is loaded into the GAC, and libraries loaded into both SQL Server and the GAC must be the exact same version (hence you cannot continue using the older, working version). This change from pure to mixed doesn't happen often, but it has happened before.

• This library needs to be loaded as UNSAFE. This is not inherently a problem, but does allow for behavior that can easily be a problem. For example, if any of the code paths being used by this 3rd party DLL use a static variable for caching info, if that value is not standard (i.e. it can change between, or even during, calls to this code), then you can get into a race-condition since SQL Server uses a shared App Domain and all sessions share the same memory / static data.

There is some code that has to be loaded as UNSAFE simply due to not being on the "Supported" list and it contains something that violates the security policy, even if you will never use that particular code path. In these cases, the code can be technically "SAFE", but without seeing the source code, you can't really be certain. So you need to test, but testing requires more than 1 person clicking a few times: it requires multiple concurrent sessions hitting this code to see if race-conditions exist. But there are other scenarios that are even harder to test for, such as memory leaks due to exceptions. Some methods in supported libraries are restricted due to this, but likely nobody even knows if methods in unsupported libraries have such issues because I don't believe they were tested.

If this functionality is intended to be non-user-facing (i.e. system process, internal company functionality, etc), then you can mitigate most of the issues (security, performance, stability) by spinning up an instance of SQL Server Express and loading your project there. That would isolate / quarantine the non-ideal SQLCLR functionality from your Production application. Assuming that you are already using a regular, external SqlConnection (not the internal / trusted connection), then this doesn't require any changes to your code.

The only risk that you cannot mitigate with

StackExchange Database Administrators Q#252077, answer score: 7