Arthur_Wood
asked on
LOGON Failed error using CR for .NET and ORACLE 9i/ASP.NET
I have an ASP.NETapp that I have built on my development machine, using CR for .NET for the reporting (a rather small part of the overall app). My development box has access to Oracle 8i, and everything runs PERFECTLY.
I have distributed the app to a customer site where the customer is using ORACLE 9i. Everything about the app is perfect on the customer box WITH ONE EXCEPTION:
when I try to open a report, I get "Logon Failed..."
As I said, everything else about the app runs perfectly on both boxes. The same UserName and Password opens the Reports and the rest of the app (not the same username and pwd acroos boxes) and there are no errors with other parts of the app.
I have been in communication with the CR Help Desk - They are in INDIA - via email, and the results have been less than helpful.
The reports are being connnected at runtime, using the ConnectionInfo object, and I am assigning the connection properties to the 'tables' (a single View for each report) also at runtime (the report files are NOT Embedded in the DLL)
Any Ideas?
AW
I have distributed the app to a customer site where the customer is using ORACLE 9i. Everything about the app is perfect on the customer box WITH ONE EXCEPTION:
when I try to open a report, I get "Logon Failed..."
As I said, everything else about the app runs perfectly on both boxes. The same UserName and Password opens the Reports and the rest of the app (not the same username and pwd acroos boxes) and there are no errors with other parts of the app.
I have been in communication with the CR Help Desk - They are in INDIA - via email, and the results have been less than helpful.
The reports are being connnected at runtime, using the ConnectionInfo object, and I am assigning the connection properties to the 'tables' (a single View for each report) also at runtime (the report files are NOT Embedded in the DLL)
Any Ideas?
AW
post your code. Connnection to the db is giving the error
ASKER
here is the code:
Dim strRPT As String
Dim CRServer As String = System.Configuration.Confi gurationSe ttings.App Settings(" Server")
Dim crUser As String = System.Configuration.Confi gurationSe ttings.App Settings(" CRUser")
Dim crPwd As String = System.Configuration.Confi gurationSe ttings.App Settings(" CRpwd")
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With myConnectionInfo
.ServerName = CRServer
.UserID = crUser
.Password = crPwd
End With
Loc = System.Configuration.Confi gurationSe ttings.App Settings(" ReportLoca tion")
strRPT = CType(Session("Report"), String)
rpt.Load(Loc.Replace("XXX" , strRPT))
SetDBLogonForReport(myConn ectionInfo , rpt)
CrystalReportViewer1.Repor tSource = rpt ' FAILS HERE at customer site (9i at customer, 8i on Dev)
' executes PERFECTLY on dev box
End Sub
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, _
ByVal myReportDoc As ReportDocument)
Dim myTables As Tables = myReportDoc.Database.Table s
Dim myTable As Table
For Each myTable In myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
myTableLogonInfo.Connectio nInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myT ableLogonI nfo)
Next
End Sub
AW
Dim strRPT As String
Dim CRServer As String = System.Configuration.Confi
Dim crUser As String = System.Configuration.Confi
Dim crPwd As String = System.Configuration.Confi
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With myConnectionInfo
.ServerName = CRServer
.UserID = crUser
.Password = crPwd
End With
Loc = System.Configuration.Confi
strRPT = CType(Session("Report"), String)
rpt.Load(Loc.Replace("XXX"
SetDBLogonForReport(myConn
CrystalReportViewer1.Repor
' executes PERFECTLY on dev box
End Sub
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, _
ByVal myReportDoc As ReportDocument)
Dim myTables As Tables = myReportDoc.Database.Table
Dim myTable As Table
For Each myTable In myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
myTableLogonInfo.Connectio
myTable.ApplyLogOnInfo(myT
Next
End Sub
AW
Where do you set the location property of the table. If you dont do that, the chnages to the server , db name are discarded and you will get an error
I believe there is a different database driver for 8i and 9i. You may need to change the database driver.
Can you install 9i on a server you can use in DEV?
mlmcc
Can you install 9i on a server you can use in DEV?
mlmcc
ASKER
mlmcc>>not possible at this time. But if the database friver were the issue, then the rest of the app would die on the Customer box, and as I keep saying, the rest of the app is fine, it's the CR reports that get trashed - CR is doing something under the covers, that doesn't want to play nicely.
ebolek>> The table (a VIEW in this case) is bound to the report (*.rpt) that is actually being loaded in the statement
rpt.Load(Loc.Replace("XXX" , strRPT))
the setup is this:
I have a report selecter page, which diplays a dropdonlist showing all of the availabel reports. Each report is present as a separate .rpt file app directory. I then pass the report name, via a Session variable to the page( strRPT = Session("Report")) which will display the report (via a ReportViewer control). In the Web.config file is hold the path to the rpt files as
ReportLoaction=C:\inetpub\ wwwroot\ap pdirectory \XXX.rpt
and this is then converting into the true path via:
rpt.Load(Loc.Replace("XXX" , strRPT)
and that file (which is bound to a view in the database) is loading into the ReportDocument object rpt.
I then set the Database logon properties of the ReportDocument and display it via the ReportViewer.
Works like a charm on the DEV box, falls over flat on the Customer box. I have been in communication with the CR Help desk, via email (They are located in INDIA), and they have been less than helpful, suggesting, for example that the problem might be a permissions issue on the ORACLE_HOME folder - if that were the case, the ENTIRE APP (not just the reports) would fail, and the rest of the app is PERFECT. So much for the Business Objects 'Help' (and I do use that term very LOOSELY in this case) desk.
AW
ebolek>> The table (a VIEW in this case) is bound to the report (*.rpt) that is actually being loaded in the statement
rpt.Load(Loc.Replace("XXX"
the setup is this:
I have a report selecter page, which diplays a dropdonlist showing all of the availabel reports. Each report is present as a separate .rpt file app directory. I then pass the report name, via a Session variable to the page( strRPT = Session("Report")) which will display the report (via a ReportViewer control). In the Web.config file is hold the path to the rpt files as
ReportLoaction=C:\inetpub\
and this is then converting into the true path via:
rpt.Load(Loc.Replace("XXX"
and that file (which is bound to a view in the database) is loading into the ReportDocument object rpt.
I then set the Database logon properties of the ReportDocument and display it via the ReportViewer.
Works like a charm on the DEV box, falls over flat on the Customer box. I have been in communication with the CR Help desk, via email (They are located in INDIA), and they have been less than helpful, suggesting, for example that the problem might be a permissions issue on the ORACLE_HOME folder - if that were the case, the ENTIRE APP (not just the reports) would fail, and the rest of the app is PERFECT. So much for the Business Objects 'Help' (and I do use that term very LOOSELY in this case) desk.
AW
>>But if the database friver were the issue, then the rest of the app would die on the Customer box.
We had that very issue and it was because we were connecting to the database through Crystal. The rest of the app used ADO connections and worked like a charm. We had forgotten to load the Crystal database drivers on the user's machines so Crystal died but the rest of the app worked just fine.
What database driver are you using? Crystal has its own drivers for Oracle and you may be using them for the report without knowing it. How are you connecting the report to the database when you develop it?
What user did you originally create the report connection with? That information may be saved with the report and if that user doesn't exist in the PROD database you might have an issue. Crystal can open the database several times and it may be that one of those times it uses the default user rather than the one you are supplying.
mlmcc
We had that very issue and it was because we were connecting to the database through Crystal. The rest of the app used ADO connections and worked like a charm. We had forgotten to load the Crystal database drivers on the user's machines so Crystal died but the rest of the app worked just fine.
What database driver are you using? Crystal has its own drivers for Oracle and you may be using them for the report without knowing it. How are you connecting the report to the database when you develop it?
What user did you originally create the report connection with? That information may be saved with the report and if that user doesn't exist in the PROD database you might have an issue. Crystal can open the database several times and it may be that one of those times it uses the default user rather than the one you are supplying.
mlmcc
ASKER
>>What database driver are you using? Crystal has its own drivers for Oracle and you may be using them for the report without knowing it. How are you connecting the report to the database when you develop it?
Yes to the last part.
>>Crystal can open the database several times and it may be that one of those times it uses the default user rather than the one you are supplying.
If that is the case, then how do I resolve the issue of Crystal being inconsistent? Must I create the same user on DEV that the customer has assigned me on their site?
AW
here is what I have on my Dev box (in the Report Designer):
Database Type: OLE DB (ADO)
Provider: OraOLEDB.Oracle
Data Source: IDIDEAL ' name in tnsnames.ora
User ID IDIDEAL
Locale Identifier 1033
OLE DB Services -5
this is what I see when I view the Database/Location property of the .rpt (in the Report Designer) in VS.NET
In my code, I am then reconnecting, at run-time so the Server name gets set, and the UserName and Password get set at run-time.
As I said, this works fine on DEV Oracle 8i), but falls over dead on Customer box (Oracle 9i). If it is a driver problem, how can I override the driver, as set in the rpt file?
AW
Yes to the last part.
>>Crystal can open the database several times and it may be that one of those times it uses the default user rather than the one you are supplying.
If that is the case, then how do I resolve the issue of Crystal being inconsistent? Must I create the same user on DEV that the customer has assigned me on their site?
AW
here is what I have on my Dev box (in the Report Designer):
Database Type: OLE DB (ADO)
Provider: OraOLEDB.Oracle
Data Source: IDIDEAL ' name in tnsnames.ora
User ID IDIDEAL
Locale Identifier 1033
OLE DB Services -5
this is what I see when I view the Database/Location property of the .rpt (in the Report Designer) in VS.NET
In my code, I am then reconnecting, at run-time so the Server name gets set, and the UserName and Password get set at run-time.
As I said, this works fine on DEV Oracle 8i), but falls over dead on Customer box (Oracle 9i). If it is a driver problem, how can I override the driver, as set in the rpt file?
AW
Overwrite the database driver
1.Click Database in the menu
2. Select set datasource
3. Select the appropriate connection
4. Update the existing connection
1.Click Database in the menu
2. Select set datasource
3. Select the appropriate connection
4. Update the existing connection
ASKER
ebolek, that is on the cevelopment box I assume...The problem is on the Customer site, to which I do not have direct access, so the question is how do I cahnge the Driver, on the customer site, if that is in fact the problem. I have Oracle *i on development, but 9i on the customer site.
mlmcc was suggesting that somehow there wasa a driver issue on the Customer installation, with the Crystal reports.???
I am going to ask the DBA at the customer site to create a User on the database, which the same UserName as I have used on the development box, and see if that resolves the problem.
AW
mlmcc was suggesting that somehow there wasa a driver issue on the Customer installation, with the Crystal reports.???
I am going to ask the DBA at the customer site to create a User on the database, which the same UserName as I have used on the development box, and see if that resolves the problem.
AW
Not in the installation on the customer machine. I am suggesting there may be a difference in the drivers required between Oracle 8i & 9i.
When you created the report what database connection did you choose?
mlmcc
When you created the report what database connection did you choose?
mlmcc
ASKER
I was connected using the OLE DB Provider fro Oracle...OraOLEDB.Oracle as I indicated above.
Why would that lead to a Logon Failed error -- sounds like it is not seeing/using the right UserName/Password combination, rather than a Provider difference, but who really understands what the error message REALLY mean???
If not that provider, then what shouls I be using?
AW
Why would that lead to a Logon Failed error -- sounds like it is not seeing/using the right UserName/Password combination, rather than a Provider difference, but who really understands what the error message REALLY mean???
If not that provider, then what shouls I be using?
AW
It probably is a user id problem. I assume the rest of the app is using the OraOLEDB database driver.
Is the driver different between the 2 machines?
mlmcc
Is the driver different between the 2 machines?
mlmcc
ASKER
mlmcc>> yes, the same driver is used throughout, and the same UserName and Password are used throughout. As far as I am aware, the driver is the same on both boxes. .NET Framework, and the accompanying MDAC 2.8 was installed on a completely 'virgin' machine, under Windows Server 2003, on the Customer site - I have the box all to myself, as their other web apps are all under Apache Tomcat - where .NET does not yet run (though I hear rumblings that the version is in the works).
How does that differ from the dev machine other than the Oracle 8i and 9i difference?
What of the client machine?
mlmcc
What of the client machine?
mlmcc
ASKER
the dev machine is using Windows XP Pro, the client box is Windows Server 2003. And as I keep saying, the ENTIRE app, with the sole exception of this problem with the Reports, runs PERFECTLY on box platforms, so platform differences does NOT seem to be a likely source of the problem.
AW
AW
Try this
Change the parameters to byRef
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, _
ByVal myReportDoc As ReportDocument)
Private Sub SetDBLogonForReport(ByRef myConnectionInfo As ConnectionInfo, _
ByRef myReportDoc As ReportDocument)
Your connection info in the calling routine isn't being updated
mlmcc
Change the parameters to byRef
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, _
ByVal myReportDoc As ReportDocument)
Private Sub SetDBLogonForReport(ByRef myConnectionInfo As ConnectionInfo, _
ByRef myReportDoc As ReportDocument)
Your connection info in the calling routine isn't being updated
mlmcc
ASKER
again, why does this work on the development box? If I change the UserID or password to an invalid value of the dev box, I get the same Logon Error...?? It appears to be an invalid UserName or Password issue with regard to CR, on customer box,but CR Help desk contends that this cannot be the problem.. I have asked the Customer DBA to add a UserName and PWD combination on teh Customer box to match what I have on dev, and then we'll see what happens. But that will need to wait until tomorrow, so I am keeping my fingers crossed.
AW
AW
Crystal keeps some information in the report file. Since you developed the report on the dev machine with a given login, it may be using those permissions.
Let's see if adding the user solves it.
mlmcc
Let's see if adding the user solves it.
mlmcc
ASKER
I am keeping my fingers crossed. Let you know tomorrow
AW
AW
ASKER
mlmcc>> well, I can now report that the latest attempt failed as before. Creating the same UserName and Password combination on the Customer box as I have on development does not change anything - the Bulk of the app runs perfectly, but the reports still fail, as before.
AW
AW
ASKER
mlmcc>> I am going to try to get a copy of 9i installed on my box. We have a copy installed here, but it is on a proprietary box (owned by another project) thus I cannot make use of it. Maybe that will resolve this (I know, hope springs eternal in the breast of man).
AW
AW
I am not familiar with the Oracle line so I am just suggesting things that I have seen with our MS SQL database and Crystal.
I'll keep thinking and looking.
mlmcc
I'll keep thinking and looking.
mlmcc
ASKER
mlmcc>>
just to keep you up to date on the current state of affairs. Yesterday I was able to gain access to an Oracle 9i instance, here, and have switched everything over to that 9i instance. My dev copy runs perfectly, Reports and all. SO I heve packeged upi the relevant DLLs and shipped them off to the DBA at the customer site, and we shall see if there are any improvements. Will keep you informed as things move along. As I said, hope springs eternal...
AW
just to keep you up to date on the current state of affairs. Yesterday I was able to gain access to an Oracle 9i instance, here, and have switched everything over to that 9i instance. My dev copy runs perfectly, Reports and all. SO I heve packeged upi the relevant DLLs and shipped them off to the DBA at the customer site, and we shall see if there are any improvements. Will keep you informed as things move along. As I said, hope springs eternal...
AW
ASKER
mlmcc>>
well, here is the situation. I have gotten 9i setup on my dev system, with the SAME Service name SAME Database name, Same UserName, Same Password as on the Customer site. I have re-built the app (though this should not be necessary), and sent the re-built DLLs to the customer. and (drum roll please......) the SAME Error condition still applies. The only difference that I can perceive is that the Customer is running Windows Server 2003, and I am running XP Pro.
AW
well, here is the situation. I have gotten 9i setup on my dev system, with the SAME Service name SAME Database name, Same UserName, Same Password as on the Customer site. I have re-built the app (though this should not be necessary), and sent the re-built DLLs to the customer. and (drum roll please......) the SAME Error condition still applies. The only difference that I can perceive is that the Customer is running Windows Server 2003, and I am running XP Pro.
AW
That is possible. Are the permissions to Oracle and the directories setup correctly on the client?
I assume it worked correctly on your machine.
mlmcc
I assume it worked correctly on your machine.
mlmcc
ASKER
yes, works just fine ( even the reports) on DEV box, and everything except the reports on the customer box. As far as I can determine, the permissions are the same on bnoth platforms.
There are only three, very simple reports, and I am probably going to abandon the CR approach, and simple do the reports in a Data Grid, and let it go at that. CR is a ROYAL PAIN, and as I have said, there Help Desk isn't worth SQUAT.
AW
There are only three, very simple reports, and I am probably going to abandon the CR approach, and simple do the reports in a Data Grid, and let it go at that. CR is a ROYAL PAIN, and as I have said, there Help Desk isn't worth SQUAT.
AW
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
but as far as I can tell, the web.config file is IDENTICAL - I have uploaded MY filoe to them, and that is what is being used. But, as I said, I am going to create my simple reports via an alternative method, and drop the CR path altogether. With that in mind, I will accept you answer(?) and award you the points, simply for hanging in and offering the assistance that you did, even if it did not lead to the 'answer'.
Glad i could help
mlmcc
mlmcc
ASKER
oh well, what can I say, but Thanks.
AW
AW
I got the answer. I was also facing the same problem and resolved it after trying a lot of ways.
As you mentioned above, you're using OraOLEDB.Oracle provide in crystal reports, the customer needs to install this driver on his machine. You can ask your customer to install the following driver from Oracle 9i installation:
1. Start Oracle Universal Installer
2. Select Oracle 9i Client from available products
3. Select Custom from Installation Types
4. On Available Product Components page, check Show All Components at the bottom. Select Oracle Windows Interfaces 9.2. Under this component, there is a compoenent called Oracle Provider for OLEDB 9.2....this is the component required for your crystal reports.
5. Finish installation.
Hope this works for you also.
Regards,
Jitendra
As you mentioned above, you're using OraOLEDB.Oracle provide in crystal reports, the customer needs to install this driver on his machine. You can ask your customer to install the following driver from Oracle 9i installation:
1. Start Oracle Universal Installer
2. Select Oracle 9i Client from available products
3. Select Custom from Installation Types
4. On Available Product Components page, check Show All Components at the bottom. Select Oracle Windows Interfaces 9.2. Under this component, there is a compoenent called Oracle Provider for OLEDB 9.2....this is the component required for your crystal reports.
5. Finish installation.
Hope this works for you also.
Regards,
Jitendra
Hi,
Here is the solution
no nead to call SetDBLogonForReport
set connection info for the report viewer after setting its source
CrystalReportViewer1.Repor tSource = rpt
CrystalReportViewer1.LogOn Info.Item( 0).Connect ionInfo = myConnectionInfo
it will work
Thanks
Raj
Here is the solution
no nead to call SetDBLogonForReport
set connection info for the report viewer after setting its source
CrystalReportViewer1.Repor
CrystalReportViewer1.LogOn
it will work
Thanks
Raj
ASKER
princeindia>>I have long since changed the entire approach, and scrapped CR for the project, and did the reports in a much more direct fashion, but thanks anyway.
AW
AW
forget CR.. Good choice to go direct.. I've screwed around with that program so much in my life that I could be VP of IT now if I did my reports in excel and spent the extra time doing my MBA instead of figuring this out :)...
Next time I have a bug I am calling them to ask for a refund.
I'm having the same sort of error connecting to mysql 4.1 than you have with Oracle 9i ... I'll just use excel and VB to pump out the report and drilldowns with leveling.. The priority should be business, not debugging well sold software.
From the frustrated guy :)
Sultan
Next time I have a bug I am calling them to ask for a refund.
I'm having the same sort of error connecting to mysql 4.1 than you have with Oracle 9i ... I'll just use excel and VB to pump out the report and drilldowns with leveling.. The priority should be business, not debugging well sold software.
From the frustrated guy :)
Sultan