User prompted for Database Login with blank database catalog - but only on one machine

[former_member308135]

I have a set of Crystal Reports that has been working for a long time. Recently, a small subset of updated reports are now prompting one of my users for database connection information when the report starts:

There is a default connection in the report but only the server gets picked up, not the catalog.

This behaviour only happens on the one machine, none of my other users are experiencing problems. The function that sets the database connection was copied from one of Don's examples, long ago. We have recently upgraded all users to V22, but only one user is experiencing this strange problem and only with 2 reports - unfortunately the ones they use most often.

I have a standard report form with the Crystal Report Viewer embedded in it. This form uses the same code for all reports: 1 - set parameters, 2 - set database connection for report and all sub-reports.

Does anyone have any suggestions on how to debug this? I have been unable to see any differences between the users connection information (in a domain) on that machine vs any other machines. Needless to say, it works fine in development and is not reproduceable.

The environment is Visual Studio 2010, Visual Basic, SQL Server 2005.

Thanks, Neil

[brucegreer]

I've seen the same issue when I was using SQL Authentication and feeding in the Database and Server name. It would lose the Database name, and then prompt for it and not let you supply it. There is a depreciated SQL driver that Microsoft recommends not using anymore. Go into the set Datasource location and check the provider on your connection shown under properties. If it starts with sqlnclI?? then that is usually the problem. Create a new connection that uses SQLOLEDB and use the update feature to update your connection above. This is recommended by Microsoft. See image.screenshot-2022-12-06-174814.png

One other thing, once you do, some formulas may not work. It seems with this provider, it brings in date (not datetime) fields as strings. So if the formula uses a date field, like {acctreg.transactiondate} >= {?STARTDATE} then wrap the database column in a date wrapper like date({acctreg.transactiondate}) >= {?STARTDATE}. Otherwise you may get a string expected error on the parameter.

[former_member11696 · Accepted solution]

Hi Neil,

The reason it's prompting and no info displayed for the log on info is because CR runtime can't find the DB client.

Add these 3 parameters to the System Environment variables, and create the logging folder:

LOGGING_DIR = c:\logging
LOGGING_ENABLED_ASSERT = 1
LOGGING_ENABLED_RUNTIME = 100

Run the app until the error happens and stop. Have a look in the log and you should see what and possibly why it can't find the DB client.

Check your PATH statement, SQL Server should add the path to the client in it, it may be either not there or possibly in the wrong order if other Clients were installed. You can compare the PATH on working PC's to those 2 that are not working.

Don

[former_member963189]

If you are still having this issue, I found a solution that worked for me. I had the exact same issue as you have described. What I ended up finding was that the difference, and difference maker, was in the SQL drivers installed. For those that worked, SQL Native Client 2012 (11.0) was installed. For those that didn't work, this version of the driver was missing (SQL Native Client 2008 (10.0) was installed).

Hope this helps you or others that find this post.

[former_member308135]

Don, your suggestion for troubleshooting showed what the problem was. The log file showed it was looking for the SQL Client 11. Over several generations of programmers, report connections to the server had variously been set up as ODBC, SQLCient 10 and SQLClient 11. I was unable to get SQL Client 11 to install on the target machines (it flat out said that Windows 10 was not the correct operating system?!? - tried 32 and 64 bit versions). However, going back and reviewing 100+ reports showed the discrepancies in drivers. Setting all reports and sub-reports to the same SQL ODBC connection fixed the problem. This will also make installations easier in future as we don't have to package a separate installer for the SQL Driver. Thanks, much appreciated.

[former_member308135]

Thank-you all for your responses. As previously mentioned, the odd thing is that all the other reports in this application work except for two and only on two workstations - they work on all other systems where the application is installed. There are approximately 40 other reports with sub-reports which use the same mechanism for setting the report and sub-report connections. Billy, I upgraded one of the workstations to the SQL Native Client 2012 (11.0) but this did not change anything.

Don, I will try out your logging suggestion. Perhaps that will help troubleshoot this, thanks. I will let you know what I find.

Thanks, Neil

[former_member280812]

Hi,

Double check the user authorization in the MSSQL.

[former_member308135]

We just retried the experiment of having the user log into a different machine and one report worked but returned no values and the second prompted her similar to the screen above. On the original users machine both reports prompt for database connection info.

Connection method is by ODBC to SQL Server. All machines are patched up to the same levels. All authentication is by domain account. The data connection to the server is:

Note that the Integrated security is false because I am on a development system which is not on the domain. All other users log in with Integrated Security.

The sub-report connections:

Good point about the driver, but all the other 90+ reports seem to work fine on this machine. Only the 2 newest reports created with version 22 seem to have problems on two of the machines. Everybody is running Windows 10. The SQL Drivers are a bit more of a question as the basic SQL data client is supplied by .Net. Both systems are patched to .Net 4.7.1.

Both reports are based on a single view which provides all of the data as a row which is then accessed from a single stored procedure. All of the users in question have a role that has permission to execute the stored procedure.

Both systems have the same Crystal Reports 22 installed.

All reports are viewed through the same report viewer form which applies a new database connection to the report and sub-reports. As per your suggestion above, I went back and checked each of the database tables and views above to ensure that the SharesAdministration role had been granted access, which it had. The shares administration role also has execute access on the stored procedure. The default database for most users is ArmadaProduction so the user is trying to display a report which defaults to their default database with they already have Integrated Security access to.

The puzzling thing is why it works fine on some users systems and not on others when everyone is patched to the same levels, has the same Crystal Reports installed and uses the same security and database roles. Also, it is odd that it displays a login prompt which has no database in it. It is almost as if the report lost the database connection information during load.

It would be nice if there was some sort of detailed log file that we could turn on when this sort of thing happens.

Neil

[ido_millet]

Most likely, the user doesn't have permissions on some table needed by these 2 reports.

If that was not the case:
Connection method?
Driver same across machines?
If ODBC, is it set up the same across machines?
NT authentication or SQL Server authentication?
Connection properties same across reports (in Crystal, Database, Set Data Source Location... expand properties).