Results 1 to 3 of 3

Thread: [RESOLVED] Crystal Reports Log On Failed in Different Environment

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Resolved [RESOLVED] Crystal Reports Log On Failed in Different Environment

    Let me preface this with saying that Crystal Reports is becoming the bane of my existence.

    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:
    Code:
    <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:
    Code:
    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;
    
            report.SetDatabaseLogon(user, password, dataSource, database);
    
            foreach (Table databaseTable in report.Database.Tables)
            {
                databaseTable.ApplyLogOnInfo(new TableLogOnInfo()
                {
                    ConnectionInfo = new ConnectionInfo()
                    {
                        ServerName = dataSource,
                        DatabaseName = database,
                        UserID = user,
                        Password = password
                    },
                    ReportName = reportName,
                    TableName = databaseTable.Name
                });
            }
    
            report.VerifyDatabase();
            return report;
        }
            
    }
    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 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 that exception. So I don't understand why it works for some, but not for all.

    I've gone through God knows how many SO and SAP forum threads, but the general consensus is that the error is vague and can mean one of a hundred different things went wrong when connecting to the database.

    What do y'all suggest in trying to debug this issue?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Crystal Reports Log On Failed in Different Environment

    I found this recursive method to set the report's datasource connection, the table's datasource connection, and any subreport datasource connections and converted it to C#:
    Code:
    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);
            }
        }
    
    }
    Unfortunately this didn't solve the issue either.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Crystal Reports Log On Failed in Different Environment

    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


    TLDR: Crystal Reports suck.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width