Crystal Report Source is MS SQL Stored Procedure Causes Login Popup

[Former Member]

My environment is this:  Visual Studio 2010 with CRforVS_13_0_9 installed.  MS SQL Server is source for all report data.  I use three different databases for all my reports.

I have a number of working CR reports that work fine in IDE and at runtime.  However, one report works in IDE report designer Main Report Preview but does not work at runtime.  Instead, it pops up the Database Login dialog box.  This particular report is the only one that I have that uses a Stored Procedure for report source data.  The following code is used for all reports.  Can someone please help me to identify what is causing this anomaly?

     ' _Options contains parameter name and value pairs, if any

     Dim oReport As New ReportDocument()

     Dim FullReportName = "MA_CustomerUsage"

     oReport.Load(FullReportName, OpenReportMethod.OpenReportByDefault)

     DoCRLogin(oReport)

     If _Options.ParameterList IsNot Nothing Then ImplementCRParameters(oReport)

     With CrystalReportViewer

          .SelectionFormula = _SelectionFormula

          .ReportSource = oReport

          .Zoom(zoomPageWidth)

          .ShowParameterPanelButton = False

          .ToolPanelView = ToolPanelViewType.None

     End With

     Private Sub DoCRLogin(ByVal oRpt As ReportDocument)

            Dim oCRDb As Database = oRpt.Database

            Dim oCRTables As Tables = oCRDb.Tables

            Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

            Dim DatabaseName = oCRTables(0).LogOnInfo.ConnectionInfo.DatabaseName

            Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo() _

                With {.DatabaseName = ScanInvenConStrSetting("Initial Catalog=", DatabaseName),

                      .ServerName = ScanInvenConStrSetting("Data Source=", DatabaseName),

                      .IntegratedSecurity = True}

            For Each oCRTable As Table In oCRTables

                oCRConnectionInfo.DatabaseName = oCRTable.LogOnInfo.ConnectionInfo.DatabaseName

                oCRTableLogonInfo = oCRTable.LogOnInfo

                oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo

                oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)

            Next

     End Sub

     Private Sub ImplementCRParameters(ByRef oReport As ReportDocument)

            Dim oparamFields = New ParameterValues

            Dim oFieldDefs = oReport.DataDefinition.ParameterFields

            For Each CRParameter In _Options.ParameterList

                Dim oFieldDef = oFieldDefs("@" & CRParameter.Name)

                Dim oDiscrete As New ParameterDiscreteValue()

                oDiscrete.Value = CRParameter.Value.ToString

                oparamFields.Add(oDiscrete)

                oFieldDef.ApplyCurrentValues(oparamFields)

            Next

     End Sub

     Private Function ScanInvenConStrSetting(ByVal Src As String, ByVal DBName As String) As String

            Dim MyDB As String = String.Empty

            Select Case DBName.ToUpper

                Case "Database1".ToUpper

                    MyDB = Global.My.Settings.Database1ConnectionString

                Case "Database2".ToUpper

                    MyDB = Global.My.Settings.DatabaseConnectionString

                Case "Database3".ToUpper

                    MyDB = Global.My.Settings.Database3ConnectionString

            End Select

            Dim ndx1 As Integer = InStr(MyDB, Src, CompareMethod.Text) + Src.Length

            Dim ndx2 As Integer = InStr(ndx1, MyDB, ";", CompareMethod.Text)

            If ndx2 = 0 Then ndx2 = MyDB.Length

            ScanInvenConStrSetting = Mid(MyDB, ndx1, ndx2 - ndx1)

     End Function

[former_member183750 · Accepted solution]

Check what else is different with the report:

1) Connection type (ODBC vs. OLEDB, vs. Native, etc)

2) Check for subreports

3) Check that the report uses the SQL Native 10 Driver

Enable the report options "Verify on 1st refresh" and "Verify stored procedure on 1st refresh".

Comment out the db logon code as well as the parameter code and let the report prompt. Does that work?

If it does, add the db logon code but leave the param code commented out. Does that work?

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

It appears that the culprit was the Verify Stored Procedure on 1st Refresh.  With that option set, the report is looking good now.  Thank you very much!

[Former Member · Accepted solution]

I may have replied too soon.  After rolling out the 'fix' I discover that I, as the creator of the report, am the only one who does not get the logon popup screen now.  I just made the one change to the report and put it into production.  No program changes were required for me to produce the report successfully.

[former_member183750 · Accepted solution]

If you are the only one able to run the report, then I'd suspect some sort of permissions on the SP that stop other users from accessing the SP. You may want to use another utility and see if the users can access the SP with it.

- Ludek

[Former Member · Accepted solution]

I used Excel to retrieve the data with the SP from SQLServer on another users PC.  It works fine.  So it is not a permissions issue.

[former_member11696 · Accepted solution]

CR fully supports data security.

You have this in your code:

.IntegratedSecurity = True}

If the users trying to view the reports do not have access to the DB they won't be able to view them.

Add the user to the DB access or change the value to false.

Don

[Former Member · Accepted solution]

But we DO use IntegratedSecurity and the Excel test above shows that the user has been properly set up in DB.  We do not have any access to the DB using username and password.

Besides that, the Domain Admin who has access to everything is getting rejected.  Even when I changed the .IntegratedSecurity option to false and the Login popup offers the user to enter credentials, I can select IntegratedSecurity and it works for me but not other users.

[former_member11696 · Accepted solution]

OK, so what DB and client are you using to connect?

[Former Member · Accepted solution]

The database is SQL Server 2005 and the properties of the database setup for the report are as follows:

Database Type:     OLEDB (ADO)

Provider:     SQLNCLI10

Data Source:     SQLSERVER

Initial Catalog:     Finance

User Id:

Password:

Integrated Security:     True

Use DSN Default Properties:     False

Locale Identifier:     1033

Connect Timeout:     15

General Timeout:     0

OLE DB Services:     -5

Current Language:

Auto Translate:     -1

Initial File Name

Use Encryption for Data:     0

Replication server name connect option:

Tag with column collation when possible:     0

MARS Connection:     0

Failover Partner:

Old Password:

DataTypeCompatibility:     0

Trust Server Certificate:     0

Server SPN:

Failover Partner SPN:

[Former Member · Accepted solution]

Does this mean anything to you?  This report still cannot be run by users.  They still get the logon screen every time.

[former_member11696 · Accepted solution]

Hi Daryl,

All looks good to me...

Search for this kbase and enable crlogger to see if it tells you more info:

1603398 - How to use crlogger to trace Crystal Reports?

Don

[Former Member · Accepted solution]

I followed the instruction for adding the environment variables required for crlogger.dll to Win 8.1 using the KB article 1603398.  The dll was already installed on my machine but at this location:

C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86

crlogger.dll property details are:

     File Description: Crystal Reports logger

     Type: Application extension

     File version: 13.0.8.1216

     Product name: SBOP Crystal Reports

     Size: 246 KB

     Date modified: 12/12/2013 2:14 AM

     Language: English (United States)

     Original filename: crlogger.dll

When I ran the report on my machine it was successful as before but there was nothing new in the log folder identified by the LOGGING_DIR environment variable.

It appears that something important is still missing.  I'd really like to get to the bottom of this as soon as possible too.  Any help would be greatly appreciated.

[former_member11696 · Accepted solution]

Did you create the logging folder the same location as specified in the Environment Variable?

Try looking in your \user\AppData\temp folder or search your PC for *.log files

If that fails then re-boot, most times the variable will simply work but sometimes the OS requires a re-boot. Also, make sure you are logged in under a local Admin account, could be the user does not have rights to read those registry keys.

Add the keys under the admin account also.

Don

[Former Member · Accepted solution]

This is crazy!    Just by adding the crlogger environment variables and rebooting the report now works!  How do I get the report to work without these?  Would info from the runtime log file help?  The assert log file had nothing in it. 

[former_member11696 · Accepted solution]

Hi Daryl,

Likely just the reboot fixed the issue... Just a coincident you added the crlogger varaibles at the same time.

Next test is remove the variables and test again

Don

[Former Member · Accepted solution]

Another user did not respond so well.  Environment variables were entered, PC rebooted, report run, same error, no log files.  This user had crlogger.dll only in x64 folder not in x86.

[former_member11696 · Accepted solution]

OK that makes no sense at all. CRlogger.dll should be in both folders.

I would try a re-install or Repair and YOU MUST be logged in as a local PC administrator.

Did you install using the VS installer or just use the redist MSI? For DEV PC's you must use the VS installer. for runtime you must natch the x86 or x64 MSI for the platform you have the app set for.

Don

[Former Member · Accepted solution]

Installed x86 msi on failing users PC, ran report, got error and log files.  Excerpt from runtime.log says:

ADO Error Code: 0x800a0e7a Source: ADODB.Properties Description: Provider cannot be found. It may not be properly installed.

adoConnection->Open failed: dataLinkFile=,provider=SQLNCLI10,dataSource=SQLSERVER,userID=,password=

initialCatalog=DB,jetSystemDatabase=,jetDatabaseType=Access,jetDatabasePassword=,useIntegratedSecurity=1,includeInitialCatalogProp=1,includeSecurityProps=1,includeJetSecurityProps=0

Not sure if install was done as local admin or not.  Will have to check.  When you said to re-install or repair, what product were you referring to? 

[former_member11696 · Accepted solution]

Does that user have the MS SQL Server 2008 Client installed under that Admin Account?

Wasn't clear about where you generated the log from.

Should be good.

[Former Member · Accepted solution]

Here is info from the successful login from runtime.log on developer PC (even with error it succeeded):

adoConnection->Open succeeded: dataLinkFile=,provider=SQLNCLI,dataSource=SQLSERVER,userID=,password=

initialCatalog=Finance,jetSystemDatabase=,jetDatabaseType=Access,jetDatabasePassword=,useIntegratedSecurity=1,includeInitialCatalogProp=1,includeSecurityProps=1,includeJetSecurityProps=0

Finishing DbServerHandle::logon

ADO Error Code: 0x800a0cb3 Source: ADODB.Connection Description: Object or provider is not capable of performing requested operation.

Query Targets: SQLNCLI.1, NativeSQLServer

Successfully built query:   "Finance"."dbo"."MA_UsageInfo4Period";1 2000001, 201404

Query Targets: SQLNCLI.1, NativeSQLServer

Successfully built query:   "Finance"."dbo"."MA_UsageInfo4Period";1 2000001, 201404

Beginning DbExecuteQuery

Beginning DbServerHandle::openRowset

adoRecordset->Open succeeded without async option: "Finance"."dbo"."MA_UsageInfo4Period";1 2000001, 201404

Finishing DbServerHandle::openRowset

[Former Member · Accepted solution]

Strange that other CR reports work fine on all user machines.  Only this one that used Stored Procedure is failing.

[former_member11696 · Accepted solution]

On your PC the log says you are  using the provider=SQLNCLI, other log says it's trying to find the provider=SQLNCLI10

So their MDAC is not installed or out of date or something is wrong.

MS does NOT fully support the MDAC driver connecting to SQL Server 2008, you can search MS's site for more info.

So install the Native 10 client on your DEV PC and update the reprot to use it or fix the users PC so it use the native client.

[Former Member · Accepted solution]

Installing SQLNCLI_X64.MSI appears to have fixed the problem on user machines.  Thank you so much for the support!  I'm certainly glad to have this one behind me.