?
Solved

Crystal Reports Windows Forms Viewer requests a login id and password

Posted on 2009-12-29
28
Medium Priority
?
2,277 Views
Last Modified: 2012-05-08
Hello,
I replaced a server for a client that was using a custom application.
The firm that wrote the application has dissolved.
The program uses SQL and I have the application working fine and have upgraded it from SQL 2000 express to SQL 2005 Express.
Problem is with 2 of the 10 reports.  All reports are stored in one particular directory and all end with the .rpt extension.  When I run any report it opens the Crystal Reports Windows Forms Viewer.
Two of the reports ask for a login id and password, the rest do not.
I have tried all possible passwords on the system and even used Admin and admin as user names with no passwords.
Nothing works.
I have Googled this issue and looked everywhere I can think of, including here.
Does anyone have an idea please?
Thank you very much.
RG
0
Comment
Question by:4rg
  • 13
  • 9
  • 5
27 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 26139773
Do you have the Crystal Reports designer?

Can you modify the application that runs the reports?

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26139890
No, I do not have the Crystal Reports designer and cannot modify the application.
What I have found since I posted the question is a note that the programmer left.
I am attaching here.  Hope it helps someone to understand more.
Thanks.
spRetailAndServiceReport.sql.txt
0
 
LVL 1

Author Comment

by:4rg
ID: 26140115
I am losing my internet connection at 2:00 pm and cannot reestablish until tomorrow.  Please do not abandon this issue for lack of response today.  This is very important to my client (and so, me).
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 101

Expert Comment

by:mlmcc
ID: 26140157
You need to be able to do one or the other.

Either you need to revise the report file to use the new database or you need to change the application to point the report to the new database.

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26140198
mlmcc,
when I put a password and login in, it is going to the correct database, as are all the other reports that do work.
rg
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26140320
I thought you said you tried passwords and they didn't work.

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26145798
Sorry, let me clarify.
When I start the report, it first opens the Crystal Report Viewer. Then a box pops up, it has 4 boxes.  Top to bottom they are :
Server name (has SQL Server name in box)
Database      (has Database Name in box)
Login ID         blank
Password      blank

This box does not pop up for the reports that are working.  Just for two of the reports that do not work because of this box.
All reports use the same database.

When I enter a login and password and click on ok, it brings up an information box that says "Crystal Reports Windows Forms Viewer" across the top and Login Failed, please try again".

Hope that helps, thanks for your time.
RG
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26149778
DO the reports that don't work have a subreport or do you know anything about the structure and the datasource?

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26158456
Do not know if they have a subreport.
I attached a file in my first comment that had the programmer notes I had found.
The datasource?   I thought that was the database???
I thought it must be correct as I only have one database, and as mentioned above it is listed in the Crystal Reports Windows Forms Viewer login box.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26159800
Have you tried the SA login and password?

Since you are using a stored procedure, I suspect the login doesn't have permissions to it.

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26166791
not using sa, just windows authentication on database.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26172505
Have you checked the permissions on the stored procedures?

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26273970
Ok, there you just went over my head.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26274246
In most databases you have to explicitly give users permissions to execute a stored procedure.

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26308482
May I ask please, how you determined that I was using a stored procedure.  Could I be using a stored procedure on two of the reports and not on the other reports?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 400 total points
ID: 26325930
Yes, that is possible.

The txt file you attached shows you are using a stored procedure.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 26329517
I just saw this thread (thanks to the notification mentioned a couple of posts above this), so I'm coming to this late, but I have a few thoughts/observations.

 Re: the stored procedure

 More specifically, the text file that you posted _is_ a stored procedure.  The lines in that file create a stored procedure and then execute it.  We don't really now how that relates to the reports.  If that file was associated in some way with a report, then logic would dictate that the report is using that stored procedure in some way, but there's really no way for us to know that without seeing the report.

 If you don't have the CR design program, maybe you could d/l a trial version and use that to take a look at the reports (eg. to check their datasource).  You might not be able to make changes, in that the trial may be a later version of CR than the report viewer that you're using, in which case if you make changes, the report viewer may not be able to run the reports anymore.  But you could at least look at the reports.

 Or, if there's nothing "sensitive" in the reports, you could post them here so that we could take a look at them.  If you're going to post the reports, you may have to change the extension (eg. from .rpt to .txt).  EE did not allow the u/l of .rpt files in the past, although I think that may have changed.


 Getting back to the stored procedure, if these reports are using a stored procedure, was that stored procedure kept when you upgraded from SQL 2000 to SQL 2005?  Open SSMS (SQL Server Management Studio), select the db and select "Stored Procedures" under Programmability and see if spRetailAndServiceReport is listed.  If not, you can use the commands in that .txt file to create it.

 I don't really think that a missing stored procedure would cause CR to start asking for a login, but if you're going to need the procedure, you might as well make sure it's there.  And there's the chance that I'm wrong and the problem actually is that the stored procedure is missing.


 As for your original issue, I've never used the "Crystal Reports Windows Forms Viewer", so I don't really know anything about that.  I run reports from the CR 10 design program.  FWIW, when I run reports that use the db (whether they use tables or stored procedures), it's perfectly normal for CR to ask for a login.  The fields are a bit different from your list - Server, "User ID", Password and Database - but it's the same basic stuff.  Maybe the viewer prompts are just different, or maybe it's because you have a different version of CR.

 So, I'm not sure what to make of that.  The login is normal for me.  If these reports used to run without asking for a login, then I guess that's something different about how the viewer works.  OTOH, if these reports have always asked for a login, and the other reports do not, my first guess would be that it's something different about those other reports, like maybe they're not even using the db.  Maybe you've confirmed that they do use the db.  I'm just saying that from here, I can't be sure of anything.  :-)

 Or maybe it's like mlmcc said and the "login" reports use stored procedures and the other reports use tables, and the issue is access to the stored procedures.  There's just no telling at this point.

 James
0
 
LVL 1

Author Comment

by:4rg
ID: 26331434
James and mlmcc,
Thanks so much for the info and suggestions.
I will go look for the stored procedure.  
I did not intentionally keep the procedures because i did not know of their existence.  I backed up the database and then restored it.  
I will get back to this client at the end of next week and will let you know.
To Clarify:
None of the reports ever asked for a password before.
4RG
0
 
LVL 35

Expert Comment

by:James0628
ID: 26332087
OK.  Let us know what you find.

 As for the procedures, I have a client that went through the same upgrade (from SQL Server 2000 to 2005).  I was not involved in the process, but I do seem to recall having to recreate some stored procedure, or possibly something else, after the upgrade, so I think it is possible that something, like a stored procedure, may have been lost in the process.

 FWIW, to expand on an answer that mlmcc gave you earlier, yes, it's certainly possible that some reports read tables and others use stored procedures.  Every report has its own datasource and they could all be different (tables or stored procedures in a db, Excel files, other db's, etc.).  The best way to check that is normally to just open a report in the CR design program and check the datasource.  As I said before, at this point we don't even know if any of those reports actually use a stored procedure.


 Hmm.   I just thought of this.  Don't know why I didn't think of it before.  I guess I was just focused on other aspects of the problem.

 When you run SSMS and connect to the db, do you have to enter a login and password?  If so,  have you tried using the same login and password when you run those reports?

 James
0
 
LVL 1

Author Comment

by:4rg
ID: 26380237
James,
Thanks for the input.
I cannot get back to this client until next week or maybe this weekend remotely.  Do you think I should close this question and ask another if I find out that it is now about stored procedures?
4RG

mlmcc, I appreciate you comments but guess I just needed it spelled out a bit clearer.
0
 
LVL 35

Expert Comment

by:James0628
ID: 26385883
Personally, I'd suggest that you just stick with this question, as long as it's not going to be too long before you can get back to it.  Finding out that the reports use stored procedures doesn't seem like a reason to start a new question to me.  But if mlmcc feels differently, I'll defer to his judgment, since he helps out as a moderator on EE (so he would know better than I).

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26386165
There is no problem continuing this question.

mlmcc
0
 
LVL 1

Author Comment

by:4rg
ID: 26427289
Got swamped with a server crash, this is still on my list.
0
 
LVL 1

Author Comment

by:4rg
ID: 26574264
mlmcc & James - Thank you for keeping this open.
I have tried to find a SQL person in the area, can't believe there is no one around.  

I went to the SSMS as you suggested and found the stored procedures.  I do not have to enter any user name or password with I open SSMS.  
There are many stored procedures, one for each report.  I chose to modify (not that I am modifying) and have copied and pasted the contents on two reports.  One that works and one that is inoperative.  I can see no difference in the calls? at the beginning but admittedly am groping in the dark here.  At the top of the page it shows the same ServerName\database.  

I looked at the "view dependencies" and "view connection properties" and all of the reports have the same "windows authenticity" and the same "Domain\Administrator" as user name.  
I  narrowed it down to only one report not working and I found another report that uses the same tables under "Objects on which (reportname) depends" setting.  That report works fine so I imagine that access to the tables is not a problem.

Is there any more information I can gather from there to help?
Thanks again for all your time.
TopProducers---operative.txt
RetailandServiceReport--inop.txt
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1600 total points
ID: 26577514
First of all, if you haven't already, you should make sure that those are the correct procedures.  The datasource name that you initially see in a CR report is an internal name.  The actual datasource name can be different.  Open a report and go to Database > "Set Datasource Location".  Click on the + by the datasource name to open it up, then open up the Properties below the datasource name.  You should see a "Table Name".  That's the name of the actual datasource that CR is calling.  Is it the same?

 Assuming that those are the correct procedures:

 The difference between them is that the "operative" procedure uses dynamic SQL, while the other procedure is just straight SQL.  I have no idea if that would account for CR being able to run one procedure and not the other, but that is the big difference between them.  If there was going to be a problem with one of those procedures, I would actually expect it to be the dynamic one, just because that adds another layer to the execution of the procedure (SQL has to build the query in a string, and then execute the string), and because dynamic SQL is inherently inefficient.  But it is possible that using dynamic SQL is affecting the way that the procedure is executed and that, somehow, means that CR doesn't prompt you for the login.

 If we can't figure out why the non-dynamic stored procedure isn't working, one possibility would be to make that procedure dynamic too.  It would not be my first choice.  As I said, dynamic SQL is inherently inefficient.  But it is an option, and if the performance is satisfactory for your needs, then it may be fine.

 OTOH, if we can get the non-dynamic stored procedure to work, I'd suggest that you consider changing the dynamic stored procedure to regular SQL.  I may be missing something (I don't use dynamic SQL.  I've just never needed it), but the only thing I see in that procedure that is "dynamic" is the section at the end that sets the ORDER BY clause depending on @Mode, and that could be handled easily in regular SQL using a CASE.

 James
0
 
LVL 1

Author Closing Comment

by:4rg
ID: 31670848
Still working on this but feel I must close the question.  If I cannot figure this out with the information provided, I will have to find another way to get the information out of the database.  Thanks for all your help.
0
 
LVL 35

Expert Comment

by:James0628
ID: 26830095
Sorry we couldn't be of more help.  Good luck with it.

 FWIW, if you can't get the login issue resolved with the stored procedure, you could try creating a view on the SQL server and using that instead, or create a command in CR (which is like a stored procedure or view, but stored in the report instead of on the server).  Both approaches may very well have the same problem, but you could give them a try.

 James
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 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