Crystal Reports Log On Failed in Different Environment

Question

We have several environments:

• Development
• Azure
• On Prem

The Crystal Report pulls data from a SQL server, but the database is different depending on the environment.

There is a config file in the project called connectionStrings.config and the connection string that we use for the website is stored there. The contents of the file looks like this:

<connectionStrings>
  <add name="dataModel" connectionString="data source=...;initial catalog=...;user id=...;password=...;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

To load the report I setup the following class:

public class CrystalReportUtil
{
    static string ReportsDirectory = ConfigurationManager.AppSettings["ReportsDirectory"];
    private static string ConnectionString = ConfigurationManager.ConnectionStrings["dataModel"].ConnectionString;
    public static ReportDocument GetReport(string reportName)
    {
        var report = new ReportDocument();
        var fileDirectory = $"{Utilities.GetProjectRoot()}/Reports";
        if (System.Web.HttpContext.Current != null)
        {
            fileDirectory = System.Web.HttpContext.Current.Server.MapPath(ReportsDirectory);
        }
        string file = Path.Combine(fileDirectory, $"{reportName}.rpt");
        if (!File.Exists(file))
        {
            throw new System.Exception($"Unable to find report file: {file}");
        }
        report.Load(file);

        var builder = new SqlConnectionStringBuilder(ConnectionString);
        var dataSource = builder.DataSource;
        var user = builder.UserID;
        var password = builder.Password;
        var database = builder.InitialCatalog;

        RecursivelyRemapConnection(report, user, password, dataSource, database);

        report.VerifyDatabase();
        return report;
    }

    private static void RecursivelyRemapConnection(ReportDocument report, string username, string password, string server, string database)
    {
        foreach (IConnectionInfo connection in report.DataSourceConnections)
        {
            connection.SetLogon(username, password);
            connection.SetConnection(server, database, false);
        }

        report.SetDatabaseLogon(username, password);

        foreach (Table table in report.Database.Tables)
        {
            table.LogOnInfo.ConnectionInfo.ServerName = server;
            table.LogOnInfo.ConnectionInfo.DatabaseName = database;
            table.LogOnInfo.ConnectionInfo.UserID = username;
            table.LogOnInfo.ConnectionInfo.Password = password;
            table.LogOnInfo.ConnectionInfo.IntegratedSecurity = false;
        }

        if (!report.IsSubreport)
        {
            foreach (ReportDocument subreport in report.Subreports)
            {
                RecursivelyRemapConnection(subreport, username, password, server, database);
            }
        }

    }
}

The issue is that in my development environment, it is working just fine whereas in the other environments I get the following exception:

Log on failed. No error.

at CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocumentClass.VerifyDatabase()

at CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.VerifyDatabase()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.VerifyDatabase()

I verified that the connection is changing in my development environment by doing the following:

1. Creating a copy of my datasource
2. In the designer, setting the datasource to the copied database
3. Deleting the copy
4. Previewing the report in the designer fails to load (as expected)
5. Generating the report using my utility class

The last step successfully generates the report which tells me that the datasource is changing, otherwise I would expect a logon failure.

What makes this particularly frustrating is that some reports in the non-developing environments work fine whereas some are producing the exception. So I don't understand why it works for some, but not for all.

Update

If I change my connection string in my development environment to the connection string used in Azure or in the On-Prem, it still works in my development environment. I really don't believe that the issue is with my connection string, but then again I have no idea what is causing the logon failure at this point.

Answer 1

After several days of looking into this, I finally found out what was causing the issue.

The particular report that was failing was using the MSOLEDBSQL provider instead of SQLOLEDB.

To fix the issue, I:

1. Opened the Crystal Report in Visual Studio
2. Right-clicked on the report
3. Database > Set Datasource Location
4. Expanded the properties of the current datasource
5. Double-clicked on the provider
6. Changed the value from MSOLEDBSQL to SQLOLEDB
7. Reentered the logon credentials (even though they get changed at runtime)
8. Saved the report
9. Build the solution
10. Republished the project