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
– Panagiotis Kanavos, Commented Aug 2, 2018 at 9: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
– Panagiotis Kanavos, Commented Aug 2, 2018 at 9: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
– Panagiotis Kanavos, Commented Aug 2, 2018 at 9: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
– Mikhail, Commented Aug 2, 2018 at 9: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. SSIS has XML tasks too, which probably means there's no need for deserialization. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 2, 2018 at 9:51

Mikhail · Accepted Answer · 2018-08-02 11:45:04Z

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 2, 2018 at 11:45

Mikhail

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Niels Berglund · Accepted Answer · 2018-08-02 10:48:06Z

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 2, 2018 at 10:48

Niels Berglund

1,70810 silver badges6 bronze badges

1 Comment

Mikhail Over a year ago

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 now i am sure something wrong with it.

Collectives™ on Stack Overflow

MSSQL CLR function webreqest 401 unauthorized

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related