The request failed with HTTP status 401: Unauthorized. in SSRS

Question

HI All,

I am getting the error when trying to call SSRS web service from SQL Agent Job. I have written C# code in Script task of SSIS Which calls SSRS reports and genrates report in pdf format. Now, the issue is that from my local machine using BIDS i am able to generate reports. The issue arises when running from SQL Agnet job and I have been told that the service account has the same access rights as I have. Please let me know  how to resolve. For Referebnce I am attaching the C# code. I am using SSRS 2008R2.

RS2005.ReportingService2005 rs;
                RE2005.ReportExecutionService rsExec;
                // Create a new proxy to the web service    
                rs = new RS2005.ReportingService2005();
                rsExec = new RE2005.ReportExecutionService();
                // Authenticate to the Web service using Windows credentials   
                rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
                rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
      


                rs.Url = "http://" + (Dts.Variables["ReportingServicesServerName"].Value).ToString() + "/reportserver/reportservice2005.asmx";
                rsExec.Url = "http://" + (Dts.Variables["ReportingServicesServerName"].Value).ToString() + "/reportserver/reportexecution2005.asmx";



                string historyID = null;
                string deviceInfo = null;
                string format = "PDF";
                //string format = "WORD";
                Byte[] results;
                string encoding = String.Empty;
                string mimeType = String.Empty;
                string extension = String.Empty;
                RE2005.Warning[] warnings = null;
                string[] streamIDs = null;
                // Path of the Report    
               // string fileName = (Dts.Variables["DestinationPath"].Value).ToString() + "/" + (Dts.Variables["ControllerID"].Value).ToString() + "_" + DateTime.Now.ToString("MMddyyyy") + ".pdf";
               
                //In PDF format.
                String oFilePath = (Dts.Variables["DestinationPath"].Value).ToString() + "/" + "enVision_7DayReport_" + (Dts.Variables["ControllerID"].Value).ToString() + "_" + DateTime.Now.ToString("MMddyyyy") + ".pdf";
                String oFileName = "enVision_7DayReport_" + (Dts.Variables["ControllerID"].Value).ToString() + "_" + DateTime.Now.ToString("MMddyyyy") + ".pdf";
                String oFolderName= (Dts.Variables["ControllerID"].Value).ToString();

                Dts.Variables["FileName"].Value = oFilePath;
                

                // Name of the report 
                string _reportName = @"/Reports/Nalco 360 Service for Membranes Weekly Report";
                string _historyID = null;
                bool _forRendering = false;
                RS2005.ParameterValue[] _values = null;
                RS2005.DataSourceCredentials[] _credentials = null;
                RS2005.ReportParameter[] _parameters = null;

                {
                    MessageBox.Show("1");
                    _parameters = rs.GetReportParameters(_reportName, _historyID, _forRendering, _values, _credentials);
                    MessageBox.Show("2");
                    RE2005.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
                    RE2005.ParameterValue[] parameters = new RE2005.ParameterValue[1];
                    if (_parameters.Length > 0)
                    {
                        parameters[0] = new RE2005.ParameterValue();
                        parameters[0].Label = "ControllerID";
                        parameters[0].Name = "ControllerID";
                        parameters[0].Value =  Dts.Variables["ControllerID"].Value.ToString();
                    }
                    
                    rsExec.SetExecutionParameters(parameters, "en-us");
                    rsExec.Timeout = 400000;
                    results = rsExec.Render(format, deviceInfo,
                        out extension, out encoding,
                        out mimeType, out warnings, out streamIDs);
                    using (FileStream stream = File.OpenWrite(oFilePath))
                    {
                        stream.Write(results, 0, results.Length);
                    }
                }
            

Thanks,

Saikat

Answer 1

Set the impersonation for you web service in your web.config. This user must have permissions on the reporting server:

<identity impersonate="true" />

Answer 2

Hi irusul,

The identity impersonate property is already set to true with the user having the neccessary permissions on the report server but still I am facing the same issue.

Thanks,

Saikat

Answer 3

Hi There

Please have a look on this blog that might help you

http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

Many thanks

Syed Qazafi Anjum

Answer 4

Hi Saikat,

Generally, the 401 error occurs when the user is not authorized to the Report Server. Besides, if Reporting Services is configured to use Kerberos, however, we didn't register Service Principal Name (SPN) for the account the Reporting Services is running under, this error will occur as well.

To solve the issue, please follow these steps:

1. Make sure the user is able to connect to the Report Server.
2. If using Kerberos authentication, please make sure SPN is configured correctly.

For more information about Kerbero Authentication and how to register SPN for a report server in Reporting Services, please see the link Syed provided above and the articles below:

• Configure Windows Authentication on the Report Server
• Register a Service Principal Name (SPN) for a Report Server

If you have any more questions, please feel free to ask.

Regards,
Mike Yin

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Answer 5

Thanks Syed and Mike. We are working with our domain admins for making the Kerberos authentication changes as you have suggested. This will take some time. I will let you know if we still face the same issue after making the changes.

Thanks,

Saikat

Answer 6

Can you contact the report server administrators to check the log, wherever they get the message "Login failed for User ‘NT Authority\Anonymous’"?

If this is true, then it is a double hop issue and requires the usage of Kerberos.

Answer 7

Hi Saikat,

I have closed this thread. If you have any questions, please feel free to let me know.

Regards,
Mike Yin

Answer 8

Rusul,

You're doing great in these forums. Can you send me an email? It's edprice at Microsoft.

Thanks!

---

Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)