Intermittent 401-Unauthorized response from SSRS

Question

(using C#, Web API, SQL Server2012 w/report server, authentication is NTLM)

I am getting an intermittent error when attempting to download a report (as an excel document) from SSRS. I build out the correct URL to render the report like:

http://sqlServer/ReportServer/Pages/ReportViewer.aspx?/TheReportName&rs:Command=Render&rs:format=excel&rc:Parameters=false&Region=WEST&CutOffDate=10/25/2015

WebClient webClient = new WebClient();
webClient.Credentials = new NetworkCredential("myDom\\myReportReader", "P@55W0rd");
     //string credentials = Convert.ToBase64String(
     //    Encoding.ASCII.GetBytes("myDom\\myReportReader" + ":" + P@55W0rd"));
     //webClient.Headers[HttpRequestHeader.Authorization] = string.Format("Basic {0}", credentials);

//401 Unauthorized thrown here:
return new MemoryStream(webClient.DownloadData(reportUrl));

The goal here is that a Web API controller on public facing IIS downloads the filestream from an internal/firewall protected SSRS and then relays the stream to browser. This WORKS sometimes...when it doesn't it returns a 401 error on the last line... The remarked out line represent an attempt to resolve the problem which did not work.

Answer 1

One solution is to change the requested URL.

SSRS responds by redirect (http status 302), when you want to export a report through ReportViewer.aspx. And WebClient does not resend the credentials to the redirected page.

You should request the report in this format: http://sqlServer/ReportServer?/TheReportName&rs:Format=EXCEL&rc:Parameters... so simply delete from URL this sequence: /Pages/ReportViewer.aspx.

More about exporting a report using URL access is here: https://msdn.microsoft.com/en-us/library/ms154040.aspx

Another solution is to leave the non-optimal URL and use CredentialCache, it will provide the credentials to the redirected page, but don't forget to set Uri parameter only to the URL prefix, that is 'http://sqlServer' or 'http://sqlServer/ReportServer', not more.

WebClient webClient = new WebClient();
var nc = new NetworkCredential("myReportReader", "P@55W0rd", "myDom");
var cc = new CredentialCache{{new Uri("http://sqlServer"), "Ntlm", nc}};
webClient.Credentials = cc;
return new MemoryStream(webClient.DownloadData(reportUrl));