• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2462
  • Last Modified:

Crystal Reports Windows Forms Viewer requests a login id and password

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
4rg
Asked:
4rg
  • 13
  • 9
  • 5
2 Solutions
 
mlmccCommented:
Do you have the Crystal Reports designer?

Can you modify the application that runs the reports?

mlmcc
0
 
4rgAuthor Commented:
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
 
4rgAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mlmccCommented:
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
 
4rgAuthor Commented:
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
 
mlmccCommented:
I thought you said you tried passwords and they didn't work.

mlmcc
0
 
4rgAuthor Commented:
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
 
mlmccCommented:
DO the reports that don't work have a subreport or do you know anything about the structure and the datasource?

mlmcc
0
 
4rgAuthor Commented:
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
 
mlmccCommented:
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
 
4rgAuthor Commented:
not using sa, just windows authentication on database.
0
 
mlmccCommented:
Have you checked the permissions on the stored procedures?

mlmcc
0
 
4rgAuthor Commented:
Ok, there you just went over my head.
0
 
mlmccCommented:
In most databases you have to explicitly give users permissions to execute a stored procedure.

mlmcc
0
 
4rgAuthor Commented:
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
 
mlmccCommented:
Yes, that is possible.

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

mlmcc
0
 
James0628Commented:
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
 
4rgAuthor Commented:
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
 
James0628Commented:
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
 
4rgAuthor Commented:
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
 
James0628Commented:
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
 
mlmccCommented:
There is no problem continuing this question.

mlmcc
0
 
4rgAuthor Commented:
Got swamped with a server crash, this is still on my list.
0
 
4rgAuthor Commented:
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
 
James0628Commented:
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
 
4rgAuthor Commented:
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
 
James0628Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now