MSSQL CLR function webreqest 401 unauthorized

Question

I have a CLR function getting data from cookie authorized website. The first request gets a login cookies and the second request gets xml data I need. The problem is in that I am always getting 401 unauthorized on a second request when run it from SQL Server as a function. The testing console app using the same DLL is working fine. Looks like the second request has no cookies but I checked in exception the amount of cookie container of the second request, it is not empty.

        String encoded = Convert.ToBase64String(Encoding.UTF8.GetBytes(UserName + ":" + Password));

        try
        {
            HttpWebRequest loginrequest = (HttpWebRequest)WebRequest.Create(string.Format("{0}", BaseOrdersAddress));
            CookieContainer logincookies = new CookieContainer();
            loginrequest.Headers.Add(HttpRequestHeader.Authorization, "Basic " + encoded);
            loginrequest.AllowAutoRedirect = false;
            loginrequest.CookieContainer = logincookies;
            loginrequest.Method = WebRequestMethods.Http.Get;

            HttpWebResponse loginresponse = (HttpWebResponse)loginrequest.GetResponse();

            loginresponse.Close();

            if (loginresponse.StatusCode == HttpStatusCode.Found)
            { 
              location = loginresponse.Headers[HttpResponseHeader.Location];
              HttpWebRequest request = (HttpWebRequest)WebRequest.Create(location);
              request.CookieContainer = logincookies;
              request.Method = WebRequestMethods.Http.Get;

              HttpWebResponse response1 = request.GetResponse() as HttpWebResponse;


              var xresult = new XmlSerializer(typeof(Local_Response));
              r = (Local_Response)xresult.Deserialize(response1.GetResponseStream());

            }

SQLCLR is *NOT* meant for web requests. That's the job of client applications. SQLCLR functions use the *same* memory used for buffers and *prolong* the duration of both implicit and explicit transactions, which means locks are held for much longer. — Panagiotis Kanavos, Aug 02 '18 at 09:34
What you try to do here is the job of eg a simple console application that uses eg HttpClient instead of WebRequests to download XML files and deserialize them — Panagiotis Kanavos, Aug 02 '18 at 09:36
BTW this code leaks a *lot*, eg the response is never closed. `response1.GetResponseStream()` returns a stream that's never disposed. There are no `using` blocks which means any error will leave requests, responses, streams alive. After the 2nd undisposed request to the same URL it'll get blocked because by default only two concurrent connections are allowed per server. Forgetting to dispose the response means the connection remains active — Panagiotis Kanavos, Aug 02 '18 at 09:41
I actually have a "response1.Close()" after it. The main strange thing for me is fine work as a console application. Maybe you are reallly right about SQL server memory using and I cannot use CLR here. But it so convenient... — Mikhail, Aug 02 '18 at 09:50
Which won't be called in case of exceptions, like that 401. The stream isn't disposed. As for convenience, this is *anything* but - can't pass arguments, can't debug, can't log. SQL Server already has an ETL tool, SSIS. You could easily put that code into an SSIS script task [as shown here](https://stackoverflow.com/questions/6684317/how-to-make-an-http-request-from-ssis). SSIS has XML tasks too, which probably means there's no need for deserialization. — Panagiotis Kanavos, Aug 02 '18 at 09:51
If that call is to a *Web service* though, there's a [Web Service task](https://learn.microsoft.com/en-us/sql/integration-services/control-flow/web-service-task?view=sql-server-2017) — Panagiotis Kanavos, Aug 02 '18 at 09:54
Just tried the SSIS Script task and got the same 401 error on the second request. — Mikhail, Aug 02 '18 at 10:33
Which means there's a problem with the code, the credentials or the cookie. Use Fiddler to check what's actually sent to the server. How does the request differ from the requests send when testing? — Panagiotis Kanavos, Aug 02 '18 at 10:35

score 2 · Answer 1 · answered Aug 02 '18 at 11:45

2

Solved. The problem was in using HttpWebRequest.CookieContainer, don't know why but it does not work while running as a CLR function, no cookies are sent. Have to do it manually adding HttpRequestHeader.Cookie to request headers collection.

answered Aug 02 '18 at 11:45

Mikhail

21
3

score 0 · Answer 2 · answered Aug 02 '18 at 10:48

0

Don't forget, your SQLCLR code executes in the context of SQL Server. I see you have a username, password in the code - what does that do and where is the username/password retrieved from. My bet is that there is something wrong with this based on what I said earlier.

answered Aug 02 '18 at 10:48

Niels Berglund

1,713
8
6

Username and password are fixed strings. While used a SSIS script I was able to check requests in Fiddler (dont know why Fiddler dont show anything while running a clr function) and I see that the second reuqest sent without any cookie nedded for authorization. I used the common cookie authorization scheme from here [link](https://stackoverflow.com/questions/5443667/httpwebrequest-pass-credentials-to-next-httpwebrequest) now i am sure something wrong with it. – Mikhail Aug 02 '18 at 11:11

MSSQL CLR function webreqest 401 unauthorized

2 Answers2