5

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)

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

  2. The .DLL that references System.Web is referenced within my C# class, which is required to be able to send it data in the first place.

  3. In regards to:

    Why would you not be able to use the methods I mentioned? They already exist in SQL Server's CLR host. This is for a web service, right

I am not sure I know enough about API's in general to answer this. I very would could and just lack the knowledge and experience to do so. It also might be due to the fact I have limitations on how I can interact with this particular API and I am not sure how those limitations apply to these methods. (I will investigate further and see if I can answer this question myself).

Though the more I think about it, I could create a "middle man" class which SQL Server can call, which then would call another class, which would have all the correct references and that might get me past my current situation. I still, however, I am interested in the specific feedback so that I can learn from this.

here is my c# class:

    using Perfion.Api;   -- this .DLL references System.Web. 
    using Perfion.Api.Import;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;

    namespace PerfInsert
    {
        public static class PerfionInsert 
        {
            public static bool CreateCommand(string tblString, string featureName, string connectionString, string perfionConnectionString, string logFile)
            {
                StringBuilder logInfo = new StringBuilder();
                try
                {
                    var wList = new Regex(@"[^0-9a-z\.\[\]_]#", RegexOptions.IgnoreCase);
                    if (wList.IsMatch(tblString))
                    {
                        logInfo.AppendLine($"{DateTime.UtcNow} - Regex Validation Failed for Table Name!");
                        return false;
                    }
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        var qryString = "SELECT * FROM " + tblString;
                        using (SqlCommand command = new SqlCommand(qryString, connection))
                        {
                            connection.Open();
                            using (var dataReader = command.ExecuteReader())
                            using (var dataTable = new DataTable())
                            {
                                dataTable.Load(dataReader);
                                PerfionApi api = new PerfionApi(perfionConnectionString);
                                Importer importer = new Importer(api.Connection);
                                importer.Status += (sender, e) => { logInfo.AppendLine($"{DateTime.UtcNow} - {e.Title}"); };
                                importer.LoadData(dataTable);
                                importer.ImportToDatabase(featureName);
                            }
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    logInfo.AppendLine($"{DateTime.UtcNow} - {ex.ToString()}");
                }
                finally
                {
                    File.AppendAllText(logFile, logInfo.ToString());
                }
                return false;
            }
        }
    }
7

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:

  1. 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).
  2. 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:

  1. 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.
  2. 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 this approach (i.e. loading your SQLCLR project into an isolated instance) is the potential of the code breaking if Microsoft ever updates System.Web (or any of its dependencies that get sucked in when loading it into SQL Server) to be a mixed-mode library. That might never happen, but if it does, then you will not be able to load that 3rd party DLL unless they switch to using HttpWebRequest, and they have almost no reason to make such a change.

UPDATE

O.P. Replied on a related question that they chose to change the project type to console app and will schedule it most likely with SQL Agent (but could also use Windows Task Scheduler

Related Posts (on StackOverflow):

  1. CREATE PROCEDURE gets “Msg 6567, Level 16, State 2” for SQLCLR stored procedure
  2. System.Security.SecurityException with CLR Function
  • @SolomonRutsky Thank you for your input.Ill go ahead and qualify my statements by saying that I may not be fully informed on this topic, so take my question with a grain of salt - I am curious as to why the 3rd party app not requiring the dll is relevant? I guess I could've elaborated more - When I tried to register my c# code, since the project contains a reference to the 3rd party app's dlll which has a reference to system.web in it, I got the error regarding the dll not being registered. Im not entirely sure I can use either method you suggested. Unless I am obviously missing the point? – Doug Coats Oct 28 at 15:10
  • Hi @DougCoats . (I wrote this before you updated your comment, so I'm editing this one). Do you need to reference the 3rd party DLL? Why would you not be able to use the methods I mentioned? They already exist in SQL Server's CLR host. This is for a web service, right? – Solomon Rutzky Oct 28 at 15:17
  • Ok - Im going to edit my post - please give me a few moments to do so. – Doug Coats Oct 28 at 15:22
  • Edited - I think I thought of a solution while typing that up - but I am still interested in learning form this. Your input is appreciated, thank you – Doug Coats Oct 28 at 15:37
  • @DougCoats I have updated my answer based on the new info. I don't really think that a "middle man" approach is worth it as that seems like a bunch of extra work. I proposed a different approach, but based on your code, I really don't see why it can't be a console app that is scheduled in one of those two ways. It looks like you could simply change the project type (to be a console app, compiling to an .EXE file), and be done (outside of scheduling it). – Solomon Rutzky Oct 28 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.