Bulk Update of DB Password in Crystal Reports

If you need to update the database password for large amount of Crystal Reports, the Crystal Management Console may be too time consuming to change the password for each individual report. A programmatic approach may be more helpful, in that case.

On the Crystal Reports server, reports and other objects such as paths, are stored in the CR_INFOOBJECTS table, which you can query with the Query Builder, accessible from your web browser, for instance http://reports.someserver.com/AdminTools/.

Most properties in the CR_INFOOBJECTS table can be programmatically modified quite easily by changing their corresponding property value (via the setProperty() method on an IProperties instance), however, as many developers have found out, this does not work for certain fields such as SI_LOGON_INFO.SI_LOGON1.SI_PASSWORD. It turns out that setting this field through the regular property methods will result in the password stored clear text. In addition, it will result in authentication failure.

The Java code example below shows how you can modify the password stored in SI_LOGON_INFO.SI_LOGON1.SI_PASSWORD. The example below shows how it can be done for 1 report with 1 DB connection (reports can have multiple connections to different databases, although in most cases this is not done).

import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.CePropertyID;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
import com.crystaldecisions.sdk.plugin.desktop.common.IReportLogon;
import com.crystaldecisions.sdk.plugin.desktop.report.IReport;
import com.crystaldecisions.sdk.properties.IProperties;
import com.crystaldecisions.sdk.properties.ISDKList;

public class Cr {

    public static void main(String[] args) throws Throwable {
        if (args.length != 2) {
            System.out.println("Syntax: Cr report_name db_password");
            System.exit(1);
        }
        Cr cr = new Cr();
        cr.setCrDbPassword(args[0], args[1]);
    }

    private void setCrDbPassword(String reportName, String dbPassword)
        throws Throwable
    {
        ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();

        IEnterpriseSession enterpriseSession = sessionMgr.logon(
            "username", "password",
            "reports.someserver.com:6400", "secEnterprise");

        IInfoStore iStore = (IInfoStore)
            enterpriseSession.getService("InfoStore");
        IInfoObjects infoObjects = iStore.query(
            "Select * From CI_INFOOBJECTS Where SI_NAME = '"
            + reportName + "' And SI_INSTANCE=0");
        IInfoObject infoObject = (IInfoObject) infoObjects.get(0);

        // Set DB Password in SI_LOGON_INFO.SI_LOGON1.SI_PASSWORD.
        IReport report = (IReport) infoObject; 
        ISDKList list = report.getReportLogons();
        IReportLogon logon = (IReportLogon) list.get(0);
        logon.setPassword(dbPassword); 

        // Set other properties in SI_LOGON_INFO.SI_LOGON1.
        IProperties logonInfo = (IProperties) infoObject
            .getProcessingInfo().properties()
            .getProperty(CePropertyID.SI_LOGON_INFO).getValue();
        IProperties logon1 = (IProperties)
            logonInfo.getProperty("SI_LOGON1").getValue();
        logon1.setProperty("SI_DELTA_FLAG", false);
        logon1.setProperty("SI_SSO_ENABLED", false);
        logon1.setProperty("SI_PROMPT_USER_VIEWING", false);

        // Commit the changes and log off.
        iStore.commit(infoObjects);
        enterpriseSession.logoff();
    }
}

One thought on “Bulk Update of DB Password in Crystal Reports

Leave a Reply

Your email address will not be published. Required fields are marked *

*