Search code examples
c#vb.netcrystal-reports

Crystal Reports - (logon failed error) change the provider from OLEDBSQL to MSOLEDBSQL to support TLS 1.2 by code

I am trying to change the provider of my Crystal Reports from OLEDBSQL to MSOLEDBSQL as OLEDBSQL does not support TLS 1.2

My Code:

Private Sub ProcessReport()
    Dim crxRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
    crxRpt.Load("D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\SummaryReport.rpt")

     ChangeOleDbConnectionInfo(crxRpt, "SERVER", "DBNAME", "MSOLEDBSQL", False, "USERID", "PASSWORD")
End Sub

Friend Shared Sub ChangeOleDbConnectionInfo(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String)

    Dim boMainPropertyBag = New NameValuePairs2()
    Dim boInnerPropertyBag = New NameValuePairs2()
    boInnerPropertyBag.Add(New NameValuePair2("Application Intent", "READWRITE"))
    boInnerPropertyBag.Add(New NameValuePair2("Auto Translate", "-1"))
    boInnerPropertyBag.Add(New NameValuePair2("Connect Timeout", "15"))
    boInnerPropertyBag.Add(New NameValuePair2("Data Source", server))
    boInnerPropertyBag.Add(New NameValuePair2("DataTypeCompatibility", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("General Timeout", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Initial Catalog", database))
    boInnerPropertyBag.Add(New NameValuePair2("Integrated Security", If(integratedSecurity, "True", "False")))
    boInnerPropertyBag.Add(New NameValuePair2("Locale Identifier", "1033"))
    boInnerPropertyBag.Add(New NameValuePair2("MARS Connection", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("OLE DB Services", "-5"))
    boInnerPropertyBag.Add(New NameValuePair2("Provider", oledbProvider))
    boInnerPropertyBag.Add(New NameValuePair2("Tag with column collation when possible", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Trust Server Certificate", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Use DSN Default Properties", "False"))
    boInnerPropertyBag.Add(New NameValuePair2("Use Encryption for Data", "0"))


    boMainPropertyBag.Add(New NameValuePair2("Database DLL", "crdb_ado.dll"))
    boMainPropertyBag.Add(New NameValuePair2("QE_DatabaseName", database))
    boMainPropertyBag.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))
    boMainPropertyBag.Add(New NameValuePair2("QE_LogonProperties", boInnerPropertyBag))
    boMainPropertyBag.Add(New NameValuePair2("QE_ServerDescription", server))
    boMainPropertyBag.Add(New NameValuePair2("QE_SQLDB", "True"))
    boMainPropertyBag.Add(New NameValuePair2("SSO Enabled", "False"))

    Dim conAttributes As New CrystalDecisions.Shared.DbConnectionAttributes()
    conAttributes.Collection = boMainPropertyBag

    Dim boConnectionInfo = New ConnectionInfo With {
        .Attributes = conAttributes,
        .DatabaseName = database,
        .ServerName = server
    }

    If Not integratedSecurity Then
        boConnectionInfo.UserID = userId
        boConnectionInfo.Password = password
    End If

    Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables = reportDocument.Database.Tables

    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables
        Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
        myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()
        myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
    Next

    Dim mySections As CrystalDecisions.CrystalReports.Engine.Sections = reportDocument.ReportDefinition.Sections
    For Each mySection As CrystalDecisions.CrystalReports.Engine.Section In mySections
        Dim myReportObjects As CrystalDecisions.CrystalReports.Engine.ReportObjects = mySection.ReportObjects
        For Each myReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In myReportObjects
            If myReportObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                Dim mySubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject = CType(myReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                Dim subReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = mySubreportObject.OpenSubreport(mySubreportObject.SubreportName)
                Dim mytablessub As CrystalDecisions.CrystalReports.Engine.Tables = subReportDocument.Database.Tables
                For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In mytablessub
                    Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
                    myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()

                    myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
                    
                Next
            End If
        Next
    Next

    reportDocument.VerifyDatabase()

    reportDocument.SaveAs("D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\CrystallReportConvertVB\Rpt-changed3.rpt")
End Sub

Im using the following Crystal Report Versions:

Crystal Reports References

I have noted the following

  1. Calling verifyDatabase() or refresh() on the ReportDocument changes the provider property back into original.
  2. I have tried the ApplyLogOnInfo() - Found that it only reset the username and password and not the provider. Calling this method also resets the changed provider.
  3. Now that I've managed to change the Provider by the following code, but It always reset the provider to the original provider.

How can this be done? Are there any workarounds for this?

Further Update

I was able to change the provider to MSOLEDBSQL in the main report by using the code suggested by Frank (With thanks) below. But still, it fails for the subreports and gives the following error.

Not supported within subreports

rpt.ReportClientDocument.DatabaseController.Database.Tables -> Not supported within sub reports.

Further Update (2) - on Subreport provider change I was able to change the provider for Crystal Report Subreports by the following code.

Friend Shared Function ChangeOleDbConnectionInfoSubReports(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal schema As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String) As ReportDocument

    Dim boInnerPropertyBag = New PropertyBag()
    boInnerPropertyBag.Add("Application Intent", "READWRITE")
    boInnerPropertyBag.Add("Auto Translate", "-1")
    boInnerPropertyBag.Add("Connect Timeout", "15")
    boInnerPropertyBag.Add("Data Source", server)
    boInnerPropertyBag.Add("DataTypeCompatibility", "0")
    boInnerPropertyBag.Add("General Timeout", "0")
    boInnerPropertyBag.Add("Initial Catalog", database)
    boInnerPropertyBag.Add("Integrated Security", If(integratedSecurity, "True", "False"))
    boInnerPropertyBag.Add("Locale Identifier", "1033")
    boInnerPropertyBag.Add("MARS Connection", "0")
    boInnerPropertyBag.Add("OLE DB Services", "-5")
    boInnerPropertyBag.Add("Provider", oledbProvider)
    boInnerPropertyBag.Add("Tag with column collation when possible", "0")
    boInnerPropertyBag.Add("Trust Server Certificate", "0")
    boInnerPropertyBag.Add("Use DSN Default Properties", "False")
    boInnerPropertyBag.Add("Use Encryption for Data", "0")
    Dim boMainPropertyBag = New PropertyBag()
    boMainPropertyBag.Add("Database DLL", "crdb_ado.dll")
    boMainPropertyBag.Add("QE_DatabaseName", database)
    boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)")
    boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag)
    boMainPropertyBag.Add("QE_ServerDescription", server)
    boMainPropertyBag.Add("QE_SQLDB", "True")
    boMainPropertyBag.Add("SSO Enabled", "False")

    Dim boConnectionInfo = New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo With {
        .Attributes = boMainPropertyBag,
        .Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
    }

    If Not integratedSecurity Then
        boConnectionInfo.UserName = userId
        boConnectionInfo.Password = password
    End If

    For Each subreport As ReportDocument In reportDocument.Subreports
        For Each table As CrystalDecisions.ReportAppServer.DataDefModel.Table In reportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables

            Dim boTable = New CrystalDecisions.ReportAppServer.DataDefModel.Table With {
                .ConnectionInfo = boConnectionInfo,
                .Name = table.Name,
                .QualifiedName = schema & "." & table.Name,
                .[Alias] = table.[Alias]
            }

            reportDocument.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, boTable)
        Next
    Next

    reportDocument.VerifyDatabase()
    Return reportDocument

End Function

Lessons Learnt

Noticed that, I can not update the driver if the installed Crystal Report Runtime does not match with the Visual studio references (I have taken the DLLs from some Nuget packages that are not official releases). I Couldn't make the code work with Crystal Reports 10.5.3700 that I used and I updated the runtime to 13.0.2000 (As per this article)

enter image description here

Crystal Reports also have tight rules with the processor architecture. Since I'm using a 32bit version, I have set all the settings in Visual Studio to compile the project as a 32bit app.

Solution

  • You can replace a report's connection info programmatically by looping through the ConnectionInfos and swapping out the provider. There's a (IMO hack-y) explanation about adding connection/driver info here which references an SAP KB that assists in writing DB logon code, but the link in the article is dead (instead, use this one - this may be more-useful for you needs).

    UPDATE 2020-Jul-7 (includes subreport handling)

    
// Add these using's (for readability in here):
using RasTables = CrystalDecisions.ReportAppServer.DataDefModel.Tables;
using RasTable = CrystalDecisions.ReportAppServer.DataDefModel.Table;
using RasConnectionInfo = CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo;
// ...

/// <summary>
/// Updates all of the tables contained within the given Crystal Report with new OLEDB connection parameters 
/// </summary>
/// <param name="reportDocument">A <see cref="CrystalDecisions.CrystalReports.Engine.ReportDocument"/> of the Crystal Report</param>
/// <param name="server">The new server (Data Source) to use</param>
/// <param name="database">The new database (Initial Catalog) to use</param>
/// <param name="oledbProvider">The new OLEDB provider (see remarks)</param>
/// <param name="integratedSecurity">Whether to use a trusted connection</param>
/// <param name="userId">The new user id to use</param>
/// <param name="password">The new user&apos;s password</param>
/// <remarks>Some valid values for a SQL Server OLEDB provider (note: matching vendor components <b>must</b> be installed)<br/>
/// <p>
/// <dt>OLEDBSQL</dt>
/// <dd>Legacy OLE DB provider for Microsoft SQL Server</dd>
/// <dt>SQLNCLI11</dt>
/// <dd>SQL Native Client v11</dd>
/// <dt>MSOLEDBSQL</dt>
/// <dd>latest OLE DB provider for Microsoft SQL Server (use this if TLS 1.0/1.1 are disabled on the SQL Server).</dd>
/// </remarks>
internal static void ChangeOleDbConnectionInfo(ReportDocument reportDocument, string server, string database, 
    string oledbProvider, bool integratedSecurity, string userId, string password)
{
    // boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
    var boMainPropertyBag = new PropertyBag();
    // boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
    // In the main property bag (boMainPropertyBag)
    var boInnerPropertyBag = new PropertyBag();

    // Set the attributes for the boInnerPropertyBag
    boInnerPropertyBag.Add("Application Intent", "READWRITE");
    boInnerPropertyBag.Add("Auto Translate", "-1");
    boInnerPropertyBag.Add("Connect Timeout", "15");
    boInnerPropertyBag.Add("Data Source", server);
    boInnerPropertyBag.Add("DataTypeCompatibility", "0");
    boInnerPropertyBag.Add("General Timeout", "0");
    boInnerPropertyBag.Add("Initial Catalog", database);
    boInnerPropertyBag.Add("Integrated Security", integratedSecurity ? "True" : "False");
    boInnerPropertyBag.Add("Locale Identifier", "1033");
    boInnerPropertyBag.Add("MARS Connection", "0");
    boInnerPropertyBag.Add("OLE DB Services", "-5");
    boInnerPropertyBag.Add("Provider", oledbProvider);
    boInnerPropertyBag.Add("Tag with column collation when possible", "0");
    boInnerPropertyBag.Add("Trust Server Certificate", "0");
    boInnerPropertyBag.Add("Use DSN Default Properties", "False");
    boInnerPropertyBag.Add("Use Encryption for Data", "0");

    // Set the attributes for the boMainPropertyBag
    boMainPropertyBag.Add("Database DLL", "crdb_ado.dll");
    boMainPropertyBag.Add("QE_DatabaseName", database);
    boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");
    // Add the QE_LogonProperties we set in the boInnerPropertyBag Object
    boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
    boMainPropertyBag.Add("QE_ServerDescription", server);
    boMainPropertyBag.Add("QE_SQLDB", "True");
    boMainPropertyBag.Add("SSO Enabled", "False");

    // Create a new ConnectionInfo object
    var boConnectionInfo = new RasConnectionInfo
    {
        // Pass the database properties to a connection info object
        Attributes = boMainPropertyBag,
        // Set the connection kind
        Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
    };

    if (!integratedSecurity)
    {
        boConnectionInfo.UserName = userId;
        boConnectionInfo.Password = password;
    }

    // Local function will return a copy of the given table using the provided connection info
    RasTable CreateBoTableCopy(RasTable originalTable, RasConnectionInfo connectionInfo)
    {
        // Create a new Database Table to replace the reports current table.
        return new RasTable
        {
            // Pass the connection information to the table
            ConnectionInfo = connectionInfo,

            Name = originalTable.Name,
            QualifiedName = originalTable.QualifiedName,
            Alias = originalTable.Alias
        };
    }

    var reportClientDocument = reportDocument.ReportClientDocument;

    // Get the Database Tables Collection for your report
    RasTables boTables = reportClientDocument.DatabaseController.Database.Tables;

    // For each table in the report, set the report's table location to use a newly-modified
    // copy of itself with the new connection info
    foreach (RasTable table in boTables)
    {
        reportClientDocument.DatabaseController.SetTableLocation(table, CreateBoTableCopy(table, boConnectionInfo));
    }

    // Now drop this same science on all of the subreports
    foreach (ReportDocument subreport in reportDocument.Subreports)
    {
        var subreportName = subreport.Name;
        foreach (RasTable table in reportClientDocument.SubreportController.GetSubreportDatabase(subreportName).Tables)
        {
            reportClientDocument.SubreportController.SetTableLocation(subreportName, table, CreateBoTableCopy(table, boConnectionInfo));
        }
    }

    // Verify the database after adding substituting the new table to ensure that the table 
    // updates properly when adding Command tables or Stored Procedures.
    reportDocument.VerifyDatabase();
}

    Usage:

    var newSqlProvider = "MSOLEDBSQL";
if (!string.IsNullOrWhiteSpace(overrideSqlProvider))
    ChangeOleDbConnectionInfo(myReportDocument, newServerName, newDatabase, newSqlProvider, false, userName, userPassword);