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.

score 9 · Accepted Answer · answered Dec 22 '15 at 13:41

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));

I had actually arrived at the first solution, as a working solution. I had also tried the second approach and it did not change the results I was getting. — Cos Callis, Jan 04 '16 at 13:47
"delete from URL this sequence: /Pages/ReportViewer.aspx." I've been searching for hours and that fix my issue. Thanks a lot — Raphael, Jun 20 '19 at 19:39

score 0 · Answer 2 · answered Jan 31 '23 at 16:02

you can pass the ssrs report as a parameter to the SSRS report server from powershell. SSRS redirects to the report. The reportname becomes a parameter to the SSRS report server.

$SSRSurl="http://reports.abc.company.com/ReportServer?/My+Directory+For++Reports/ABCReport&Field1Param=123&Field2Param=321&rs:Format=EXCEL"

$spreadsheet_file="abc.xls"
$Path = "C:\download_directory\$spreadsheet_file"
$WebClient = New-Object System.Net.WebClient
$WebClient.Credentials = New-Object System.Net.Networkcredential($User, $PasswordAsString)
$WebClient.DownloadFile( $SSRSurl, $path )

Intermittent 401-Unauthorized response from SSRS

2 Answers2