Using the .NET Crystal Report Viewer to display a report

GaryRasmussen
GaryRasmussen used Ask the Experts™
on
I have a bad feeling that I am not going to get any responses to this post but here goes.  The folowing code was working perfectly just 1 month ago and never prompted for a password.  In this example, I have a single report with one database table and no parameters.  When I run this code, the form with the report viewer opens and pops up a small Database Login window with all of the fields populated.  I click Ok and it says:

Login failed.  Please try again.

I re-type the password and it still fails.  I am 100% sure that the credentials are correct and If I run the report in Crystal Reports with the exact same credentials, the report runs as expected.  

The application is running on one machine and the report resides in a folder on the SQL Server.  This is happening with all reports that connect to a database.  I have literaly spent the entire day researching this and have seen many, many posts like this but little or no suggestions and it there are any, they don't work.



ReportDocument reportDoc = new ReportDocument();
reportDoc.Load(fileName);

ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.UserID = userName;
connectionInfo.Password = password;
connectionInfo.ServerName = odbcName;
connectionInfo.DatabaseName = databaseName;
                
foreach (Table table in reportDoc.Database.Tables)
{
  TableLogOnInfo tableLogonInfo = table.LogOnInfo;
  tableLogonInfo.ConnectionInfo = connectionInfo;
  table.ApplyLogOnInfo(tableLogonInfo);
}

crystalReportViewer1.ReportSource = reportDoc;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
It pops the database prompt because something in the information is incorrect.
Have you deleted a user or changed passwords recently?
Many databases require passwords to be changed at set times say 30, 60, 90 days.

Can you change the data in the prompt?
If so use a known user and password.

mlmcc
It could also be you change the server which is opening the report? When you designed the report, you may be using a connection string where now the connection string change due to different computer name.

Author

Commented:
mimcc,
Yes, I realize that it "should" only prompt because something in the information is incorrect but I have confirmed that the information is 100% correct.  I can leave off the Server and Database Name and it automatically populates those fields with the correct values.  So that only leaves username and password.  I am using the SA account and have been for years as this is only a test environment.  I know the password and I also know it did not expire.  As I wrote, I can use the exact same credentials to run the report inside Crystal Reports and it works fine.

adriankohws,
I did not change the server.

Thanks!
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is there a subreport?

If you don't provide the logon information, can you add it and run the report?

mlmcc

Author

Commented:
Yes, there is a sub report.  I am processing the sub report credentials as follows.

Also, If I don't provide the credentials it will ask me for them and when I enter them and hit ok, it says Login Failed and I am 100% sure the credentials I am entering are correct and work as I use them to generate the report inside Crystal.


//Loop through all the sections to find all the sub reports
                foreach (Section section in reportDoc.ReportDefinition.Sections)
                {
                    //Loop through all the report objects to find all subreports
                    foreach (ReportObject reportObject in section.ReportObjects)
                    {
                        if (reportObject.Kind == ReportObjectKind.SubreportObject)
                        {
                            SubreportObject subreportObject = (SubreportObject)reportObject;

                            //Open the subreport object and logon as for the general report
                            ReportDocument subreportDoc = (ReportDocument)subreportObject.OpenSubreport(subreportObject.SubreportName);

                            foreach (Table table in subreportDoc.Database.Tables)
                            {
                                //Before Change
                                DisplayInfo("SUB REPORT BEFORE", table);
                                
                                TableLogOnInfo tableLogonInfo = table.LogOnInfo;
                                tableLogonInfo.ConnectionInfo = connectionInfo;
                                table.ApplyLogOnInfo(tableLogonInfo);

                                //After Change
                                DisplayInfo("SUB REPORT AFTER", table);
                            }
                        }
                    }
                }

Open in new window

Author

Commented:
Let's not worry about sub reports as that wil just complicate things.  I just copied the report and removed the sub report so that now there is just a simple "single" report and the Login still fails.

Author

Commented:
Also, I can print out the table LogOnInfo before I apply the login info and after and they are identical except for that the password is blank before applying the login fo to the tables and is not blank and correct after applying the login info to the tables which is expected.

I read somewhere that you have to specify the table location after you apply the login information to the table but when I try to do that just to see what happens, I get an exception.  Does this help?

System.Runtime.InteropServices.COMException (0x8004100F): Logon failed.
Details: IM002:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
GlobaLevelProgrammer
Commented:
Can you confirm these things first...to make sure the asics are covered:
1) you pinged the SQL server to see if the network sees it
2) you ran a sample SQL on the server(locally) andit returns fine.
3) your Windows authentication and SQL Auth(SA) both work fine
4) you verifed on the server locally that the logon info is fine.
5) you verified on the CR that all code and CR server works fine..please post results...

Author

Commented:
1) Yes
2) Yes
3) I am not using Windows authentication.  Only SQL and it is working
4) Yes
5) Huh?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Is this on the same machine as your development with Crystal?

Are you using the same ODBC connection?

Same user is logged into the machine?

mlmcc

Author

Commented:
"The application is running on one machine and the report resides in a folder on the SQL Server"

There is only 1 ODBC connection on the SQL Server thatr the report uses.

I am logged into my dev machine as me and the report credentials are not using Windows Auchrentication.  The report uses the SA account.  Also, All of these report were working 1 month ago.  I don't know what changed anbd I am the only one that uses this test server.
GlobaLevelProgrammer

Commented:
If your using SQL(sa)...make sure that you add him to the database and tables as a user

Author

Commented:
Thanks, but like I said,

If I run the report from Crystal Reports and use the SA user, the report runs fine.
Programmer
Commented:
Then what do you want to do ?? Use Windows Authentication?? Dumm up your sql string and post here so we can see..are you using..'Trusted Connection="true";'...

sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Terrrnert;Trusted_connection=true;"
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Any idea what updates have been done in the last month?

I don't know how ODBC connections work in a network environment.
Is the ODBC connection on the box the application is running or did you create a system ODBC connection that everybody on the network can use?

mlmcc

Author

Commented:
GlobaLevel:
I am not using Windows authentication and I don't want to use Windows authentication.  In my test environment, I am logged into a domain,  My test server is in another domain that knows nothing about the domain my dev machine is in.  So to answer what I want to do, I want to use SQL authentication just like I am when I launch the report from Crystal Reports.

mlmcc:
The system ODBC is on the server that hosts SQL Server and the Crystal Reports.  I do not have an ODBC on my DEV machine.  And nobody uses this server but me as it is on a seperate network.

Author

Commented:
OMG, I am an idiot.  I had an ODBC connection setup on the Server but not on my local machine where I was regerating the reports ftrom.

And I just figured out why I whought it was working before was that the stupid "Save data with report" check box was checked so I was just getting back cached data.
GlobaLevelProgrammer

Commented:
...we've all been there.. : )glad its solved...

Author

Commented:
How do I split up the points?
GlobaLevelProgrammer

Commented:
In the lower right corner of the experts comment box us "Acept multiple solution" click that and you can divide the point like you want..the experts like it when you give a good grade as it multiples the points and they are the ones that answer futures questions ...just an FYI ...all the best and keep posting your questions ! : )

Author

Commented:
The real solution was to add an ODBC connection to my local machine.  However I appreciate the time that was spent trying to help me out so I divided up the points equally.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial