Solved

Database Logon Failed (ASP.NET/Crystal Reports XI R 2)

Posted on 2008-10-03
10
4,108 Views
Last Modified: 2013-11-07
I have a crystal report with several subreports linked to the main report via a parameter, in an asp.net application. Whenever I am trying to convert it behind the scenes into a PDF, it does the job just fine on my local machine. The problem is that it throws a database logon failed exception on the server and I have no idea what else I can do to make it work...

Please, help!
''' <summary>

    ''' This method converts a crystal report into a PDF file

    ''' </summary>

    Private Sub ExportToStream()

        Dim oStream As New IO.MemoryStream

        Dim rptDocument As ReportDocument = New ReportDocument()

        Dim subreport As ReportDocument

        Dim parmName As String = "@MrId"

        Dim parmValue As Integer = SessionData.EssInfo.MrId

        Dim exportOpts As ExportOptions = New ExportOptions()

        Dim pdfOpts As PdfRtfWordFormatOptions = ExportOptions.CreatePdfRtfWordFormatOptions()

        Dim logon As New TableLogOnInfo

        Dim tbl As Table

        Dim serverName1 As String = PCON.APP.db_Server

        Dim dbName As String = PCON.APP.dbName

        Dim userID As String = PCON.APP.UserName

        Dim password As String = PCON.APP.Pass
 

        Try

            exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat

            exportOpts.ExportFormatOptions = pdfOpts

            rptDocument.FileName = PCON.APP.FileName(SessionData.EssInfo.EssCategoryId)

            rptDocument.SetParameterValue(parmName, parmValue)
 

            ' Set Database Logon to main report

            For Each tbl In rptDocument.Database.Tables

                logon = tbl.LogOnInfo

                SetLogonInfo(logon)

                tbl.ApplyLogOnInfo(logon)

            Next tbl
 

            'Provide database credentials to subreports

            For Each subreport In rptDocument.Subreports

                For Each tbl In subreport.Database.Tables

                    logon = tbl.LogOnInfo

                    SetLogonInfo(logon)

                    tbl.ApplyLogOnInfo(logon)

                Next 'Tbl

            Next 'Subreport
 

            rptDocument.SetDatabaseLogon(userID, password, serverName1, dbName)

            oStream = CType(rptDocument.ExportToStream(ExportFormatType.PortableDocFormat), IO.MemoryStream)

            Response.ContentType = "application/pdf"

            rptDocument.Close()

            rptDocument.Dispose()

            Response.BinaryWrite(oStream.ToArray())
 

            HttpContext.Current.ApplicationInstance.CompleteRequest()
 

        Catch ex As Exception

            Response.Write(ex)

        Finally

            oStream.Flush()

        End Try

    End Sub
 

    Private Sub SetLogonInfo(ByRef logon As TableLogOnInfo)

        logon.ConnectionInfo.ServerName = PCON.APP.db_Server

        logon.ConnectionInfo.DatabaseName = PCON.APP.dbName

        logon.ConnectionInfo.UserID = PCON.APP.UserName

        logon.ConnectionInfo.Password = PCON.APP.Pass

    End Sub

Open in new window

0
Comment
Question by:GrigoryR
  • 5
  • 2
  • 2
  • +1
10 Comments
 

Expert Comment

by:bigmike12396
Comment Utility
What app is running on the server?
0
 

Author Comment

by:GrigoryR
Comment Utility
IIS and SQL Server 2005
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try commenting out line 41.

You are setting the logon information for each table.  You may be overriding it when you set the report document logon info.

mlmcc
0
 

Author Comment

by:GrigoryR
Comment Utility
I think I already tried that and decided to keep it there because it actually made it possible to login to the DB on my local machine... For some reason, it doesn't work the same way on the server... It seems that ApplyLogOnInfo method doesn't work...
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Where do youopen the report document?

Are the logon values set correctly?

mlmcc
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:GrigoryR
Comment Utility
The report is being opened in the browser. The logon values are retrieved from the web.config, and I already tested those values by logging into the SQL Server with those credentials. I am not sure why I am getting this exception, since I am providing all the information required for successful login... At least, I think so.
0
 

Expert Comment

by:KS_Mis
Comment Utility
I would try three things.
  1. try making sure that your data souce (if it is not localhost) uses the ip address of the machine, if you are using a dataset and setting the report source that way then make sure your Data Source key in your connection string value is setup like this: Data Source=tcp:127.0.0.1,1433; User ID= yada yada).
  2. try looping through your subreports the same as you do now and instead of using the tables logoninfo just set each subreports connection info like this:

    for(int i=0;i<reportDoc.subreports.count;i++)
  3. Make sure that the server has both the same crystal decisions dll's as your local machine and also make sure that if your report uses the SQL Native Client ADO connection type that the SQL Native Client is actually setup on the host machine.  I did a project once that did not have the native client setup on it and it continuously threw logon exception errors.
0
 

Expert Comment

by:KS_Mis
Comment Utility
i don't know what happened on my previous post but the code was supposed to look like this:
for(int i=0;i<reportDoc.subreports.count;i++) {
        reportDoc.subreports[i].SetDatabaseLogon("userid","password","server","db");
}
0
 

Accepted Solution

by:
GrigoryR earned 0 total points
Comment Utility
I solved the problem by implementing the push approach and embedding the subreports into the main report instead of using pre-existing reports as subreports. This way I made sure that all the subreports and the main report used exactly the same connection.
0
 

Author Comment

by:GrigoryR
Comment Utility
SetDatabaseLogon does not work for subreports
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now