ASP.NET / Crystal error

[former_member1012208]

I have written an application that allows me to add reports and view them in the web browser. Everything works perfect if I use tables. The moment I use sql commands, it gives me many problems. Still if I use one sql command everything is good.

I have the following scenario.

When I use a report with one sql command and one table, I get the following error :

Invalid Argument provided. Failed to open a rowset. Error in File C:\Temp\report1 {F0AAC473-3B29-4D67-ACCE-79F7AA3B0738}.rpt: Invalid argument for database.

When I use more than one SQL command, I get the following error :

Failed to open a rowset. Details: 08003:[Microsoft][ODBC Driver Manager] Connection not open Failed to open a rowset. Error in File C:\Temp\16-Oct-2008114840_2_SQLCommand_medecaretest {B0224037-4CB5-4DD4-B10B-2D26E3AFA274}.rpt: Failed to open a rowset.

When I run the same reports in VS2005 project everything works. But when I deploy it, I have all those above problems. To check more about this, I deployed the application into the development pc itself and I still get the error.

All the above reports use an SQL ODBC connection to get data from another server. All ODBC connections use intergrated security.

I am not sure how to fix this. I googled it, no luck so far. And I am stuck with this for the past 2 weeks. Any help is greatly appreciated.

I am using the following code to display the report.

Dim impersonationContext As System.Security.Principal.WindowsImpersonationContext

Dim currentWindowsIdentity As System.Security.Principal.WindowsIdentity

currentWindowsIdentity = CType(User.Identity, System.Security.Principal.WindowsIdentity)

impersonationContext = currentWindowsIdentity.Impersonate()

Dim myReport As New ReportDocument

Dim reportPath As String = Server.MapPath("Reports\" & "report1.rpt")

myReport.Load(reportPath)

Dim crLogin As New ApplyCRLogin

crLogin._dbName = myReport.DataSourceConnections.Item(0).DatabaseName

'''crLogin._passWord = ""

crLogin._serverName = myReport.DataSourceConnections.Item(0).ServerName

'''crLogin._userID = ""

crLogin._integratedSecurity = myReport.DataSourceConnections.Item(0).IntegratedSecurity

crLogin.ApplyInfo(myReport)

crLogin = Nothing

CrystalReportViewer1.ReportSource = myReport

impersonationContext.Undo()

Following is the applycrlogin class :

Public Class ApplyCRLogin

Public _dbName As String

Public _serverName As String

Public _userID As String

Public _passWord As String

Public _integratedSecurity As Boolean

Public Sub ApplyInfo(ByRef _oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)

Dim oCRDb As CrystalDecisions.CrystalReports.Engine.Database = _oRpt.Database()

Dim oCRTables As CrystalDecisions.CrystalReports.Engine.Tables = oCRDb.Tables()

Dim oCRTable As CrystalDecisions.CrystalReports.Engine.Table

Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo

oCRConnectionInfo.DatabaseName = _dbName

oCRConnectionInfo.ServerName = _serverName

oCRConnectionInfo.UserID = _userID

oCRConnectionInfo.Password = _passWord

oCRConnectionInfo.IntegratedSecurity = _integratedSecurity

For Each oCRTable In oCRTables

oCRTableLogonInfo = oCRTable.LogOnInfo()

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo

oCRTable.TestConnectivity()

oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)

Next

End Sub

End Class

[former_member1012208 · Accepted solution]

I did futher testing on this issue. I logged onto the SQL server pc and added my development pc to the administrators group (which is not a good solution) and wala it works.

I then opened the SQL profiler and checked the sql statements and I could see the sql command that I used coming as the logged in windows user in to sql, but the table sql statement comes as my pc's name. The moment I remove the pc from the administrators groups and run the report, I only see the first sql command. The second one does not come at all. I think this is a permission problem, but I don't know how to fix this.

Any help is greatly appreciated.

[Former Member · Accepted solution]

Hi

Give it a try by providing the read permission to C:\Temp for the userand also to the c:\windows\temp.

To check permission issues use this utility [procmon|http://technet.microsoft.com/en-us/sysinternals/bb896644.aspx].

Hope this helps!!

Regards

Amit

[former_member11696 · Accepted solution]

If it's not a directory permission issue then it's a DB table permission issue. Make sure the tables your report is accessing has read permission for the user logged in. In this case it's likely the local system account that is running your app. You may want to set up NT Authentication on SQL Server. Crystal respects all DB security, if the user running the report does not have access it will through an error. You can add code to test connectivity and capture the error and pop up a message indicating "You are not authorized to run this report".

Open the report in the designer and set DB location and check on use Trusted Authentication, it should then propogate the users NT account info to the DB server for access.

If they are generic reports and no security of specific tables you could create a new user account called "Crystal" and give the account access to all tables, Views, SP's etc. Then run your application under that account or set the log on info in code.

Thank you

Don

[former_member1012208 · Accepted solution]

Thanks for the replies. Much appreciated. I tried giving full access to the Temp folder for all users, still no luck.

The strange thing is, if I create a report with 10 linked tables, everything works. The moment I add an SQL command I get the error message. When I checked the sql server through SQL profiler, I found out that moment I run the report with sql command, the report stops. I could see some sql statements coming through and it stops in the middle. I think the moment you use a sql command sql does not let the current user to run it. Since these commands are not save in the SQL server, I don't know how to allow them to run against the sql server database. If I have only one sql command again the report works, the moment I add another sql command or another table, it stops working. I tried using OLE DB still the same result.

I could make it work by creating an SQL user and using that logon information, but our reports must run using the NT logon user. Cannot use any sql users. I have given access to the nt user in SQL server, when I run the report through crystal reports everything works. Even if I run the web site through my code (VS.2005) still it works. Problem happens only in the deployed web site.

Hope I am clear to you all.

[former_member1012208 · Accepted solution]

More Updates :

I am doing further testing and I just found out the moment I insert an sql command, sql server looses the nt user information. It tries to execute the rest of the report using my pc's account and I see the following error.

Error: Login failed for user ntsco\W3376$

W3376 being the pc that holds the web site. I am logged onto the pc as reportuser.

I then added the deployment pc (w3376) into the administrators group in the SQL server and wala, the report runs. But this is not the solution that I am looking for.

When I remove the sql command and run the report, everything is fine.

I then added the myreport.verifyreport , then the system displays only the table information. It removes all the fields in the SQL command. I am so frustrated by this. It seems like the system is loosing the nt logon information.

Please help.

Edited by: Don Perera on Oct 20, 2008 9:52 PM

[former_member11696 · Accepted solution]

Hi Don,

I believe the casue is because You can't use more than on Command object, it's considered 2 separate data sources which will work but not recommended.

If you are using Command Objects then add all your SQL into one SQL Statement and let CR do the filtering if more is required.

Also I noticed you should change the order of the test connectivity to after you apply the log on info and:

For Each oCRTable In oCRTables

oCRTableLogonInfo = oCRTable.LogOnInfo()

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo

oCRTable.TestConnectivity()

oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)

Should be:

For Each oCRTable In oCRTables

oCRTableLogonInfo = oCRTable.LogOnInfo()

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo

oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)

move the test to outside of the loop, it's only required once.

oCRTable.TestConnectivity()

If it fails then you know your log on info is incorrect.

And the reason you see no fields is by default when you do a logon/setlocation CR expects the fields to be exactly the same as when the report was created. If they are not, by default we simply delete any fields we can't map. In this case it looks like we can map any of them.

Thank you

Don

[former_member1012208 · Accepted solution]

Thanks for the reply Don. Much appreciated.

I tried your code, still no luck. You are correct when you said "can't use more than on Command object". But in my test report I don't use more than one sql command. I use a table and an sql command. Also when I run this in vs2005, it works without any issues. I tried many reports with 2 or more sql commands and they too work. Only the deployed web site is giving me the problem.

[former_member1012208 · Accepted solution]

What I found out was when I run the report in my web page. It first authenticates with the correct nt user id and executes one sql statement. The moment it finds the sql command, it drops the nt user and tries to connect to the server using my pc's account ntscp\W2376$

W2376 is the name of my pc. System does connect with the correct user for the very first sql, and then it disconnects. If I use only tables in my reports system connects with the proper nt user and executes the report without any problems. I believe the culprit is the SQL command. When using sql command some how the web site drps the nt user in the middle of running.

This does not happen when I run the report within VS2005.

I did few more changes to my dsn, I created an sql user and used that in the dsn and changed the code accordingly and deployed the web site. Everything works this way. But I must have the intergrated security enabled for some reports.

Can any one help?

[former_member11696]

When creating the report and check on Trusted Authentication in the Database wizard

[former_member1012208]

Did that, still no luck.

[former_member11696]

Hi Don,

I actually tracked this issue recently for OLE DB and it's be fixed and should be included in a patch shortly. It has been scheduled for FP 4 which should be out mid march. You may have to convert all your reports to use MS OLE DB provider. I'll add a note to the track to get R&D to test using ODBC also.

The problem is the Trusted connection is not getting migrated to each connection, stops after the first connection.

The internal tracking number is: ADAPT01171567

To get you by for now here's a link and info to download a beta build of the dll, it has not been fully regression tested so test it first and it should not be shipped without warning. It also has a dependency on Service Pack 3.1.

EXTERNAL DOWNLOAD SITE :

ftp://ftp1.businessobjects.com/outgoing/CHF/beta/XI3.1_LAFix0.0.22.zip

ADDITIONAL INFORMATION

Special Instructions :

1. Stop all related services.

2. Rename and backup crdb_ado.dll in C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86

3. Copy and place the new crdb_ado.dll into the location.

4. Restart all related services.

New Behavior : The issue is resolved.

Limitations : N/A

Component(s): LAFix released on top of XI 3.1

crdb_ado.dll

Thank you

Don

Edited by: Don Williams on Jan 21, 2009 10:13 AM