Web Crystal reports produce Database logon failed

Question

I developed a Crystal report on my local machine and I can render the report on my local website using .ExportToHttpResponse but as soon as I move my code to the production server I get a "Database logon failed" error. I basically used a .XML dataset file as the source to develop the report locally. This was my code:

I call a method to render the report:

private void RenderCrystalReports(string rptName, string pdfReportName, DataSet dataForReport)
  {
    string reportPath = Server.MapPath(@"\App_Data\Reports\" + rptName);
    ReportDocument report = new ReportDocument();
    report.Load(reportPath, OpenReportMethod.OpenReportByTempCopy);
    SetDBLogonForReport(ref report);
    report.SetDataSource(dataForReport);
    SetDBLogonForReport(ref report);
    report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, System.Web.HttpContext.Current.Response, true, pdfReportName);
  }

and to get the dataset I have:

private DataSet GetReportData(DateTime from, DateTime to, int userID, bool totalsOnly)
  {
    DataTable Job = _reportRepository.GetAccountHistoryJob(from, to, userID, totalsOnly);
    Job.TableName = "Accounts";
    DataSet ds = new DataSet("AccountsDS");
    ds.Tables.AddRange(new DataTable[] { Job });
    return ds;
  }

and as far as I am concerned the method below is what is supposed to help me to connect to the remote SQL server:

private void SetDBLogonForReport(ref ReportDocument reportDocument)
  {
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString());
    ConnectionInfo connectionInfo = new ConnectionInfo();
    connectionInfo.DatabaseName = builder.InitialCatalog;
    connectionInfo.UserID = builder.UserID;
    connectionInfo.Password = builder.Password;
    connectionInfo.ServerName = builder.DataSource;

    Tables tables = reportDocument.Database.Tables;
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
      TableLogOnInfo tableLogonInfo = table.LogOnInfo;
      tableLogonInfo.ConnectionInfo = connectionInfo;
      table.ApplyLogOnInfo(tableLogonInfo);
    }
  }

Why does this not work? I just figured out that even locally the .xml dataset is used so how do I use the sql dataset that I get?

I believe that this problem has been asked and solved numerous time on the crystal reports support forum. That being said, I had this issue myself and to solve it I did pretty much everything from poking around with permissions to re-installing crystal reports. — Joakim
Ok, so how did you solve it in the end? What did you do to debug it? I looked at SQL profiler and I could see that the web server is calling the stored procedure. — Jack Smit
I did too many things to actually pinpoint the exact issue I'm afraid, it could be that the crystal reports version on the server isn't up to date, user permissions isn't right, using .net 4.0 but haven't added "<startup useLegacyV2RuntimeActivationPolicy="true" ></startup>" in the configuration file and so on. — Joakim
I installed the same "SAP Crystal report runtime engine for .Net Framework 4 (64 bit)" on both machines. Does Crystal use the standard port to communicate(1433)?? Oh, and I do not have useLegacyV2RuntimeActivationPolicy in my web.config. I have access to another website's code where they are runnig Framework4 and the same Crystal and it is not in their config? — Jack Smit
it depends on if sap have fixed that issue or not in later versions, we are using version 13.200 along with .net 4.0 which forced us to use useLegacyV2RuntimeActivationPolicy. regarding which port it uses I don't know. CrystalReports cries a lot, sometimes you need to specify that you compile for x64 instead of "Any CPU" and sometimes you don't. It's a huge mystery which can be solved by fiddling with pretty much everything and nothing to make it work across several environments. I've done it for web and winforms on several computers and it's a pain to find out what is causing what. — Joakim

Jack Smit Jack Smit · Accepted Answer · 2012-01-17T04:19:24

This is the code that worked for me:

public ActionResult TestReport() {
  RenderCrystalReports("demoRPT.rpt", "Some Report", GetReportDs());
  return View();
}


#region Private methods
private void RenderCrystalReports(string rptName, string pdfReportName, DataSet dataForReport)
{
  string reportPath = Server.MapPath(@"\Reports\" + rptName);
  ReportDocument report = new ReportDocument();
  report.Load(reportPath, OpenReportMethod.OpenReportByTempCopy);

  DataTable jobTable = GetReportTable();
  report.Database.Tables[0].SetDataSource(jobTable);

  report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, System.Web.HttpContext.Current.Response, true, pdfReportName);

}

private DataTable GetReportTable()
{
  DataTable Job = GetReportData();
  Job.TableName = "Accounts";
  return Job;
}

private DataSet GetReportDs()
{
  DataTable Job = GetReportData();
  Job.TableName = "Accounts";
  DataSet ds = new DataSet("AccountsDS");
  ds.Tables.AddRange(new DataTable[] { Job });
  return ds;
}

private DataTable GetReportData()
{
  DataTable table = new DataTable();
  table.Columns.Add("Date", typeof(DateTime));
  table.Rows.Add(DateTime.Now);
  return table;
}
#endregion

Web Crystal reports produce Database logon failed

1 Answers