Crystal Report Viewer Credential Prompt for Report with Dynamic Parameters

[Former Member]

The .NET Crystal Report Viewer is prompting for database credentials when launching a report containing dynamic parameters. This only occurs for reports created with SAP Crystal Reports 2011 designer. Reports created with Crystal Reports XI designer (where dynamic parameters were first introduced) work correctly.

The credential prompt window contains the following fields:

- Server Name: <server name> (disabled)

- Database Name: <database name> (disabled)

- User Name: <empty> (enabled)

- Password: <empty> (enabled)

- Use Single Signon Key: false (disabled)

The values in the prompt window which are disabled are the database connection values used during the design of the report in the SAP Crystal Reports 2011 designer.

Expected Result:

- No prompt for database credentials.

- Values read from the database should be populated in a drop down for the dynamic parameters.

Environment:

- Visual Studio 2010 (C#)

- Windows 7 Enterprise

- SAP Crystal Reports runtime engine for .NET Framework 4

- SAP Crystal Reports, version for Visual Studio 2010

- SAP Crystal Reports 2011

The database connection is being set to use a DSN. It must be a DSN as the calling application is only aware of the DSN/Username/Password values. These values are being passed to the Crystal Report Viewer contained in a Windows form.

The database connection for the report is being set as follows:

foreach (InternalConnectionInfo internalConnectionInfo in this.report.DataSourceConnections)
{
    // Must set the UseDSNProperties flag to True before setting the database connection otherwise the connection does not work
    if (internalConnectionInfo.LogonProperties.ContainsKey("UseDSNProperties"))
    {
        internalConnectionInfo.LogonProperties.Set("UseDSNProperties", true);
    }

    // Supposed to set the database connection for all objects in the report (ie. main report, tables, sub reports)
    internalConnectionInfo.SetConnection(this.DSN, string.Empty, this.LoginName, this.Password);
}

The SetConnection method's signature is as follows:

SetConnection(string server, string database, string name, string password)

As you can see from the code snippet above I am setting the DSN name as the server parameter, blank for the database parameter (a database connection using DSN should only require DSN name/Username/Password) and the database username and password respectively.

Is this a SAP bug?

Is this the correct way of setting the database connection to use a DSN?

Is there some other properties that need to be set somewhere else in the report through code?

Any help would be greatly appreciated.

[former_member183750 · Accepted solution]

Have not heard of the issue until now with CR 2011, but it sounds awfully familiar to an issue we used to have with CR 2008. The issue and work-around is documented in KB #[1412611|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333433313332333633313331%7D.do]. Please have a look and see if the work-around will help. And let me know irrespective as this will need to be reported to R&D for a fix.

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

Thanks for the response. It somewhat helped.

I have updated the database connection code to include explicitly setting the connection for the tables within the report. This resulted in the Credential prompt no longer being displayed but rather the expected dynamic parameter prompt. Great news!

Of note, setting the Tables connection info must occur before calling SetConnection for the reports DataSourceConnections. If it is called after, the credential prompt reappears.

ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.ServerName = this.DSN;
connectionInfo.UserID = this.LoginName;
connectionInfo.Password = this.Password;

Database database = this.report.Database;
Tables tables = database.Tables;

foreach (Table table in tables)
{
    TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
    tableLogOnInfo.ConnectionInfo = connectionInfo;
    table.ApplyLogOnInfo(tableLogOnInfo);
}

foreach (InternalConnectionInfo internalConnectionInfo in this.report.DataSourceConnections)
{
    // Must set the UseDSNProperties flag to True before setting the database connection
    if (internalConnectionInfo.LogonProperties.ContainsKey("UseDSNProperties"))
    {
        internalConnectionInfo.LogonProperties.Set("UseDSNProperties", true);
    }

    // Sets the database connection for all objects in the report (ie. main report, tables, sub reports)
    internalConnectionInfo.SetConnection(this.DSN, string.Empty, this.LoginName, this.Password);
}

However, the dynamic parameter prompt is not displaying a list of selectable values which are contained in the database (the primary function of a dynamic parameter). It is only displaying a text field which allows entry of a value. Entering a valid value (ie. contained in the database field) does result in the report opening correctly and filtering the results based on the parameter. It seems as if it is acting as a Static parameter rather then a Dynamic one. I did double check the parameter in the report, it is set to Dynamic.

One thing I noticed that is interesting. In the about window of the Crystal Report designer it says v14.0.0.760 RTM. The merge module and msi installer for Visual Studio is v13. Are there any redistributable runtimes for v14? Is this some sort of default behavior for backwards compatibility? I recall that when we created reports with Crystal XI that contained Dynamic parameters, when opened up in Crystal 9 the parameter prompt just had a text box instead of a dropdown. This is the same behavior as what I am seeing now.

Any ideas?

[former_member183750 · Accepted solution]

The version "stuff" is ok. I think the powers that be decided CR was not complicated enough, so they released version 13 dlls (with CRVS2010) and version 14 dlls with CR 2011. Version 14 is equivalent to version 13. There is no version 14 runtime...

Let me check on the dynamic parameters and get back...

- Ludek

[former_member183750 · Accepted solution]

I have no news re. known issues with dynamic parameters. That's not to say there are'nt any...

Couple of things to try though:

1) See if the code created by the utility described in [this|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333533353333333933323331%7D.do] kb will help.

2) Search this forum on ReplaceConnection_Click. Don posted a DB replacement code that may also help.

- Ludek

[Former Member · Accepted solution]

Thanks for the pointer to the database connection code generator. After taking a look at the output from the tool I was able to finally get the dynamic parameters to load and populate properly without prompting for credentials. I needed to tweak the outputted code a bit to match my requirements of using a DSN only connection.

Instead of updating the database connection properties contained within the Report.Database.Tables collection from the CrystalReports.Engine namespace, I changed it to replace the database connection properties in the Report.ReportClientDocument.DatabaseController.Database.Tables collection from the CrystalDecisions.ReportAppServer.DataDefModel namespace. For one reason or another, using the RAS namespace solved the problem.

Below is the updated code with the change made:

using RAPTable = CrystalDecisions.ReportAppServer.DataDefModel.Table;

foreach (InternalConnectionInfo internalConnectionInfo in this.report.DataSourceConnections)
{
    // Must set the UseDSNProperties flag to True before setting the database connection
    if (internalConnectionInfo.LogonProperties.ContainsKey("UseDSNProperties"))
    {
        internalConnectionInfo.LogonProperties.Set("UseDSNProperties", true);
    }

    // Sets the database connection for all objects in the report (ie. main report, tables, sub reports)
    internalConnectionInfo.SetConnection(this.DSN, string.Empty, this.LoginName, this.Password);
}

// The attributes for the QE_LogonProperties which is part of the main property bag
PropertyBag innerPropertyBag = new PropertyBag();
innerPropertyBag.Add("DSN", this.DSN);
innerPropertyBag.Add("UserID", this.LoginName);
innerPropertyBag.Add("Password", this.Password);
innerPropertyBag.Add("UseDSNProperties", "true");

// The attributes collection of the tables ConnectionInfo object
PropertyBag mainPropertyBag = new PropertyBag();
mainPropertyBag.Add("Database DLL", "crdb_ado.dll");
mainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");
mainPropertyBag.Add("QE_LogonProperties", innerPropertyBag);

// Pass the database properties to a connection info object
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.Attributes = mainPropertyBag;
connectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
connectionInfo.UserName = this.LoginName;
connectionInfo.Password = this.Password;

// Replace the database connection properties of each table in the report
foreach (RAPTable oldTable in this.report.ReportClientDocument.DatabaseController.Database.Tables)
{
    RAPTable table = new RAPTable();

    table.ConnectionInfo = connectionInfo;
    table.Name = oldTable.Name;
    table.QualifiedName = oldTable.QualifiedName;
    table.Alias = oldTable.Alias;

    this.report.ReportClientDocument.DatabaseController.SetTableLocation(oldTable, table);
}

this.report.VerifyDatabase();

Thanks again Ludek for the help.