Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Crystal Reports in deployed environment Logon Failed

I have read about every blog and tried every solution but to no avail.  This is my code:

Imports Microsoft.VisualBasic

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Public Class cReportSetup

    Dim crtableLogoninfos As New TableLogOnInfos()

    Dim crtableLogoninfo As New TableLogOnInfo()

    Dim crConnectionInfo As New ConnectionInfo()

    Dim CrTables As Tables

    Dim CrTable As Table

    Dim TableCounter

    Dim o As New aspdata.cData

    Dim boMainPropertyBag As New DbConnectionAttributes()

    Dim boInnerPropertyBag As New DbConnectionAttributes()

    Public Function configureCR(ByRef reportName As String) As ReportDocument

        With crConnectionInfo

            If o.dbInstance Is Nothing Or o.dbInstance.Length = 0 Then

                .ServerName = o.dbServer & o.dbPort

            Else

                .ServerName = o.dbServer & "\" & o.dbInstance & o.dbPort

            End If

            .DatabaseName = "ASP"

            .UserID = o.dbUser

            .Password = o.dbPassword

            .IntegratedSecurity = False

            .Type = ConnectionInfoType.SQL

            .AllowCustomConnection = True

        End With

        'Set the attributes for the boInnerPropertyBag

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Auto Translate", "-1"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Connect Timeout", "15"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Data Source", crConnectionInfo.ServerName))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("DataTypeCompatibility", "0"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("General Timeout", "0"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Initial Catalog", "ASP"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Integrated Security", "False"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Locale Identifier", "1033"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("MARS Connection", "0"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("OLE DB Services", "-5"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Provider", "SQLNCLI10"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Tag with column collation when possible", "0"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Trust Server Certificate", "0"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Use DSN Default Properties", "False"))

        boInnerPropertyBag.Collection.Add(New NameValuePair2("Use Encryption for Data", "0"))

        'Set the attributes for the boMainPropertyBag

        boMainPropertyBag.Collection.Add(New NameValuePair2("Database DLL", "crdb_ado.dll"))

        boMainPropertyBag.Collection.Add(New NameValuePair2("QE_DatabaseName", "ASP"))

        boMainPropertyBag.Collection.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))

        'Add the QE_LogonProperties we set in the boInnerPropertyBag Object

        boMainPropertyBag.Collection.Add(New NameValuePair2("QE_LogonProperties", boInnerPropertyBag))

        boMainPropertyBag.Collection.Add(New NameValuePair2("QE_ServerDescription", crConnectionInfo.ServerName))

        boMainPropertyBag.Collection.Add(New NameValuePair2("QE_SQLDB", "True"))

        boMainPropertyBag.Collection.Add(New NameValuePair2("SSO Enabled", "False"))

        crConnectionInfo.Attributes = boMainPropertyBag

        Dim cryRpt As New ReportDocument

        cryRpt.Load(reportName)

        setReportDb(cryRpt, False)

        Return cryRpt

    End Function

    Private Sub setReportDb(ByRef report As ReportDocument, ByVal subR As Boolean)

        'Loop through each sub report in the report and apply the LogonInfo information

        'There seems to be an issue with subreports causing the app to crash. Solution for now is to set your log on info for the subreports first and then set the main report log on info.

        Dim sSubReportName As String

        Dim objTmpReport As ReportDocument

        For Each ReportObject In report.ReportDefinition.ReportObjects

            If ReportObject.GetType().Equals(GetType(SubreportObject)) Then

                sSubReportName = ReportObject.SubreportName

                objTmpReport = report.OpenSubreport(sSubReportName)

                setReportDb(objTmpReport, True)

            End If

        Next

        report.SetDatabaseLogon(o.dbUser, o.dbPassword, crConnectionInfo.ServerName, "ASP")

        For Each dsc As InternalConnectionInfo In report.DataSourceConnections

            ' Must set the UseDSNProperties flag to True before setting the database connection otherwise the connection does not work    

            If (dsc.LogonProperties.ContainsKey("UseDSNProperties")) Then

                dsc.LogonProperties.Set("UseDSNProperties", True)

            End If

            dsc.LogonProperties.Set("Data Source", crConnectionInfo.ServerName)

            dsc.SetConnection(crConnectionInfo.ServerName, "ASP", o.dbUser, o.dbPassword)

            Dim prt As String = ""

            For i As Integer = 0 To dsc.LogonProperties.Count - 1

                Dim nvp As NameValuePair2 = dsc.LogonProperties.Item(i)

                prt = prt & "Name=" & nvp.Name & ", Value=" & nvp.Value & System.Environment.NewLine

            Next

            MsgBox(prt)

        Next

        'Loop through each table in the report and apply the LogonInfo information

        CrTables = report.Database.Tables

        For Each Me.CrTable In CrTables

            crtableLogoninfo = CrTable.LogOnInfo

            crtableLogoninfo.ConnectionInfo = crConnectionInfo

            CrTable.ApplyLogOnInfo(crtableLogoninfo)

            Try

                CrTable.Location = CrTable.Name

            Catch ex As Exception

                MsgBox(ex.ToString)

                Exit Sub

            End Try

        Next

        If Not subR Then

            report.Refresh()

        End If

    End Sub

End Class

This is the prompts I am getting:

I noticed is that for some reason the datasource is picking up a database instance that is incorrect in the deployed environment.  Not sure where that comes from....  I have a database instance in my development environment but it is different.

Any help will be appreciated...

Tags:
Former Member
Former Member replied

I solved the issue.  For sub-reports you have to set the connection.  Below is my final code:

<<<--- Start of code ---->>>

Imports Microsoft.VisualBasic

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Imports CrystalDecisions.ReportAppServer.DataDefModel

Public Class cReportSetup

    Dim boConnectionInfo As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo

    Dim o As New aspdata.cData

    Dim boMainPropertyBag As New PropertyBag

    Dim boInnerPropertyBag As New PropertyBag

    Public Function configureCR(ByRef reportName As String) As ReportDocument

        'Set the attributes for the boInnerPropertyBag

        boInnerPropertyBag.Add("Auto Translate", "-1")

        boInnerPropertyBag.Add("Connect Timeout", "15")

        boInnerPropertyBag.Add("Data Source", o.dbServer)

        boInnerPropertyBag.Add("DataTypeCompatibility", "0")

        boInnerPropertyBag.Add("General Timeout", "0")

        boInnerPropertyBag.Add("Initial Catalog", "ASP")

        boInnerPropertyBag.Add("Integrated Security", "False")

        boInnerPropertyBag.Add("Locale Identifier", "1033")

        boInnerPropertyBag.Add("MARS Connection", "0")

        boInnerPropertyBag.Add("OLE DB Services", "-5")

        boInnerPropertyBag.Add("Provider", "SQLNCLI10")

        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", "ASP")

        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", o.dbServer)

        boMainPropertyBag.Add("QE_SQLDB", "True")

        boMainPropertyBag.Add("SSO Enabled", "False")

        'Pass the database properties to a connection info object

        boConnectionInfo.Attributes = boMainPropertyBag

        boConnectionInfo.UserName = o.dbUser

        boConnectionInfo.Password = o.dbPassword

        boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE

        Dim boReportDocument As New ReportDocument

        boReportDocument.Load(reportName, OpenReportMethod.OpenReportByTempCopy)

        setReportDb(boReportDocument)

        Return boReportDocument

    End Function

    Private Sub setReportDb(ByRef report As ReportDocument)

        Try

            For Each subReport As CrystalDecisions.CrystalReports.Engine.ReportDocument In report.Subreports

                For Each connection As CrystalDecisions.Shared.IConnectionInfo In subReport.DataSourceConnections

                    report.DataSourceConnections(0).SetConnection(o.dbServer, "ASP", o.dbUser, o.dbPassword)

                    subReport.DataSourceConnections(0).SetConnection(o.dbServer, "ASP", o.dbUser, o.dbPassword)

                Next

            Next

            'Get the Database Tables Collection for your report

            Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables

            boTables = report.ReportClientDocument.DatabaseController.Database.Tables

            'Create a new Database Table to replace the reports current table.

            Dim boTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table

            Dim curTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table

            boTable.ConnectionInfo = boConnectionInfo

            'For each table in the report:

            ' - Set the Table Name properties.

            ' - Set the table location in the report to use the new modified table

            Dim cnt As Integer = 0

            For Each curTable In boTables

                boTable.Name = curTable.Name

                boTable.QualifiedName = "ASP.dbo." & curTable.Name

                boTable.Alias = curTable.Name

                report.ReportClientDocument.DatabaseController.SetTableLocation(boTables(cnt), boTable)

                cnt += 1

            Next

            'Verify the database after adding substituting the new table.

            report.VerifyDatabase()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

End Class

<<<--- End of Code --->>>

Thank you for all your help!

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question