(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:
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.
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));