none
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


    Monday, June 11, 2012 12:55 PM

Answers

All replies