Crystal Reports in deployed environment Logon Failed

[Former Member]

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...

[Former Member · Accepted solution]

One more Image:

[Former Member · Accepted solution]

Can anybody help?  I am really out of ideas on what to do next.

[former_member183750 · Accepted solution]

Hello Hank

We should start with a few clarifications:

What version of CR are you using?

What CR Service pack are you using?

What version of .NET?

Web or Win app?

What OS?

What database?

What is the db connection method (OLE DB, ODBC, etc.)?

Are there any subreports involved?

Where did the code come from?

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Ludek,

Thanks for assisting.

a) What version of CR are you using?

SAP Crystal Reports, version for Microsoft Visual Studio 13.0.5.891

b) What CR Service pack are you using?

SP 5

c) What version of .NET?

.Net Framework 4 (32-bit)

d) Web or Win app?

Win app

e) What OS?

Development box is Windows 7 32-bit (Everything work here)

Deployment workstation Windows 7 64-bit

f) What database?

Development box is Microsoft SQL EXPRESS

Deployment server is MIcrosoft SQL 2008 (i need to verify with the client)

g) What is the db connection method (OLE DB, ODBC, etc.)?

OLE DB

h) Are there any subreports involved?

Yes and each call a stored procedure

i) Where did the code come from?

Snippets I grabbed from Google.  Converted C# to Vb... I tried to use Don's code but don't have the property bag assembly.

Thanks,

-Henk

[former_member183750 · Accepted solution]

Hello Henk

1st thing to note the following:

MS SQL Server 2008 does not work with the MDAC version of the Native drivers, you must install the MS SQL Server Client. You'll see the driver name is "MS SQL Server Native client 10". This is the one you need to use. Alternatively, using ODBC, it seems to resolves many issues also.

If the above does not help, or is already installed, I am thinking I's like you to use slightly different code;

Download the database code writing utility attached to KBA 1553921 - Is there a utility that would help in writing database logon code? Test the db code with a report that does not have subreports. If that works, test with a report that does have a subreport. Note that subreports shoul all preferable have the same connection type, but this is probably just my preference as it simplifies things a bit.

- Ludek

[Former Member · Accepted solution]

Ludek,

Sorry for the delay in responding but I have to ship the code to Africa for it to be tested.  I used the generated code by the utility you provided and created a test report with referencing the fields of the tables.  Here is the code:

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

Imports CrystalDecisions.ReportAppServer.DataDefModel

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Public Class fAssessmentStarted

    Dim o As New aspdata.cData

    Private Sub f_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Dim r As New cReportSetup

        Dim cryRpt As New ReportDocument

        'cryRpt = r.configureCR("AssessmentsStarted.rpt")

        cryRpt = ChangeConnectionInfo()

        CrystalReportViewer1.Top = 0

        CrystalReportViewer1.Left = 0

        CrystalReportViewer1.Height = Height

        CrystalReportViewer1.Width = Width

        CrystalReportViewer1.ReportSource = cryRpt

    End Sub

    Private Sub f_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize

        CrystalReportViewer1.Top = 0

        CrystalReportViewer1.Left = 0

        CrystalReportViewer1.Height = Height

        CrystalReportViewer1.Width = Width

    End Sub

    Private Sub crv_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load, CrystalReportViewer1.Resize

        CrystalReportViewer1.Top = 0

        CrystalReportViewer1.Left = 0

        CrystalReportViewer1.Height = Me.Height

        CrystalReportViewer1.Width = Me.Width

    End Sub

    Public Function ChangeConnectionInfo() As ReportDocument

        Dim boReportDocument As New ReportDocument

        Try

            boReportDocument.Load("AssessmentsStarted.rpt", OpenReportMethod.OpenReportByTempCopy)

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

            Dim boTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table

            'boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

            Dim boMainPropertyBag As New PropertyBag

            'boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

            'In the main property bag (boMainPropertyBag)

            Dim boInnerPropertyBag As New PropertyBag

            '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")

            'Create a new ConnectionInfo object

            Dim boConnectionInfo As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo

            'Pass the database properties to a connection info object

            boConnectionInfo.Attributes = boMainPropertyBag

            'Set the connection kind

            boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE

            '**EDIT** Set the User Name and Password if required.

            boConnectionInfo.UserName = o.dbUser

            boConnectionInfo.Password = o.dbPassword

            'Pass the connection information to the table

            boTable.ConnectionInfo = boConnectionInfo

            'Get the Database Tables Collection for your report

            Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = _

            boReportDocument.ReportClientDocument.DatabaseController.Database.Tables

            'For each table in the report:

            ' - Set the Table Name properties.

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

            boTable.Name = "Sessions"

            boTable.QualifiedName = "ASP.dbo.Sessions"

            boTable.Alias = "Sessions"

            boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(0), boTable)

            boTable.Name = "Inmates"

            boTable.QualifiedName = "ASP.dbo.Inmates"

            boTable.Alias = "Inmates"

            boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(1), boTable)

            boTable.Name = "Assessment_Types"

            boTable.QualifiedName = "ASP.dbo.Assessment_Types"

            boTable.Alias = "Assessment_Types"

            boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(2), boTable)

            'Verify the database after adding substituting the new table.

            'To ensure that the table updates properly when adding Command tables or Stored Procedures.

            boReportDocument.VerifyDatabase()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        Return boReportDocument

    End Function

End Class

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

The report first gives this error message and then displays the report:

On my other report using the generated code but contain sub-reports, I still get the above error as well as the login screen...

Thanks,

-Henk

[former_member183750 · Accepted solution]

Hello Henk

For some reason, your screenshots are coming in as red X. Can you please type out the errors?

Also, please confirm that you are using the  "MS SQL Server Native client 10"(?).

- Ludek

[Former Member · Accepted solution]

Ludek,

The client installed is MS SQL Server 2012 Native Client version 11.0.2100.60

The error is: Logon failed. Error in File AssessmentsStarted{DFA033A7-EFB9-4849-BE12-028B7FB912A6}.rpt:

Unable to connect: incorrect logon parameters.

The strange thing is that it displays the report that directly reference the tables but not the one with the sub-reports.

Thanks for your help.

-Henk

[former_member183750 · Accepted solution]

Ha! Now it all fits. See this thread re. support of MS SQL Server 2012 Native Client version 11.0.2100.60:

http://scn.sap.com/thread/3342222

- Ludek

[Former Member · Accepted solution]

Ludek,

I changed the SQL Native Client to 2008 version 10 and now the report that accesses the tables directly works.  The report that contains sub-reports don't and gives an error: "Failed to open the connection. Details Vendor code 53.".  I googled error code 53 and am not seeing anything I can do.  The two reports connects to the same database with the same credentials.  Is there something different that I have to do with a sub-report regarding the connection?  I have seen sample code that search the connection in the sub-report and set the dbconnection...

Thanks,

-Henk

[Former Member · Accepted solution]

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!

[former_member183750 · Accepted solution]

Many thanks for sharing the solution Henk

Have a great weekend

- Ludek