SQL CLR randomly fails in SQL Server

Question

I have created a .Net assembly and imported it into SQL Server and use it in a scalar-valued function (SVF).

When I execute the SVF it works fine. The second time also. But every third time the SVF fails and the underlying error is

System.NullReferenceException: Object reference not set to an instance of an object

I have added try/catch statements and debug data to the CLR, but I still have no clue as to why this CLR fails.

Can you help me out?

This is the C# code:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString HEAD(SqlString Uri)
{
    string statusCode = "";
    // Debug info
    string debugData = "1,";

    try
    {
        debugData += "2,";
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        debugData += "3,";
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(Convert.ToString(Uri));

        req.Method = "HEAD";
        req.AllowAutoRedirect = true;
        debugData += "4,";
        HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
        debugData += "5,";

        if (resp.StatusCode == HttpStatusCode.MethodNotAllowed)
        {
            // Use GET instead of HEAD
            debugData += "6,";
            req = (HttpWebRequest)WebRequest.Create(Convert.ToString(Uri));
            req.Method = "GET";
            resp = (HttpWebResponse)req.GetResponse();
        }

        debugData += "7,";
        statusCode = ((int)resp.StatusCode).ToString();
        debugData += "8,";
    }
    catch (WebException ex)
    {
        debugData += "9,";
        HttpWebResponse webResponse = (HttpWebResponse)ex.Response;
        debugData += "10,";
        statusCode = ((int)webResponse.StatusCode).ToString();
        debugData += "11,";
    }
    catch (Exception ex)
    {
        debugData += "12,";
        statusCode = debugData + ex.Message;
        debugData += "13,";
    }
    debugData += "14,";
    //return (statusCode);
    return debugData;
}

The method takes a string as input (which is a URL) and checks if the URL actually exists by doing a HttpWebrequest with a HEAD method.

It returns the status code, or an error message (hence the string as output).

The assembly has been successfully imported into my database:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;
GO

CREATE ASSEMBLY SQLHttpRequest
FROM N'C:\somefolder\SQLHttpRequest.dll'
WITH PERMISSION_SET=UNSAFE;
GO

And this is the SVF:

CREATE FUNCTION dbo.uf_head_webrequest
    (@Uri nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME SQLHttpRequest.[SQLHttpRequest.HTTPFunctions].HEAD;

This is how I test this function:

declare @uri nvarchar(1000) = 'https://logos-download.com/wp-content/uploads/2016/02/Microsoft_logo_SQL-700x566.png'
SELECT dbo.uf_head_webrequest(@uri)

The output is 1,2,3,4,5,7,8,14,

However, if I execute it a couple of times, I get this response:

Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during the execution of user-defined routine or aggregate "uf_head_webrequest":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at SQLHttpRequest.HTTPFunctions.HEAD(SqlString Uri)
.

I understand the concept of object instantiation but don't understand why I don't get any debug data back.

I have searched the internet and came up with this question on SO:

SQLCLR .NET Error: Object reference not set to an instance of an object

This describes problems with NULL values. But in my opinion, this is not the case here. Especially because I am using the same Uri again and again.

Thanks!

Answer 1

It might be this line (in catch (WebException ex) ):

HttpWebResponse webResponse = (HttpWebResponse)ex.Response;

You are assuming that ex.Response exists. Try checking if it's null first. This would explain why you aren't getting the debugData value back (due to an unhandled exception in the error handling).

HOWEVER, even if that fixes the error, there are still several problems with this code, the biggest being:

1. not cleaning up disposable resources (i.e. no using())
2. using TRUSTWORTHY ON (please see: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining)
3. Unnecessarily setting the assembly to UNSAFE instead of EXTERNAL_ACCESS

Minor problems:

1. setting DataAccessKind = Read (you aren't doing data access and it's a performance hit; might not add to performance of this type of operation but still)
2. using Convert.ToString(Uri) instead of just Uri.Value

In the end, why are you trying to reinvent the wheel by coding your own HTTP request? There are several existing options of this exact function that are more functional and have gone through a bit of testing. There are one or two good free options I've seen on GitHub (don't just grab source from some blog as there are several rather bad examples out there), and there's a non-free option via my SQL# (SQLsharp) project (while there is a free version, the INET_GetWebPages function is only in the paid version) that I believe has some functionality not available in those free options (at least not as of the last time I checked).