Change connection with Server authentication

[Former Member]

I am running SP16 with VS 2013, using C#. I also have CR Designer 2013 installed. MS SQL Server 2014, using provider SQLNCLI11, OLE DB connection type.

I have a Windows application that launches a Crystal Report. I used Don Williams' utility to get the correct properties set when changing the connection info. It is necessary to change the connection when the report is moved from the Test environment to a customer's environment. The idea is that the connection info is updated and saved the first time a customer runs the report. After that, the report launches as if it had been built in the customer's environment.

For the last few months, this has been working successfully. As long as the SQLServer is set to use Windows authentication, it doesn't matter if the report was built with Integrated Security, or if a username and password were specified--the report would launch just fine, I could change the connection info, and save the changes to the report.

A couple of weeks ago, we started testing with the SQLServer set to use Server Authentication, and a DB logon box started to pop up, showing the correct (new) server instance and database name grayed out, showing the username as editable, and asking for the password. The password had been set as part of changing the connection info, so this definitely confused me. I tested using reports that needed the connection changed, and reports that were set to my specific connection so no change was needed. And the DB logon box popped up. I tried a report that was built with Windows authentication, and the DB logon box still popped up. This makes me think that the box is coming from the server rather than the report. And why is it disregarding the password that is being passed? Maybe my expectations are incorrect.

Bottom line is that I need to be able to run reports without having the DB logon box pop up and prompt for the password. I need to be able to do this whether the server uses Windows or Server authentication. Right now our reports are built with Windows authentication. If the server is set for Windows auth, no logon box shows. If it's set for Server auth, the logon box shows.

Is there something I'm missing, or is this a capability that isn't available in CRVS?

[DellSC · Accepted solution]

Yes, and no on closing the report..... The foundation of the Crystal SDK is a bunch of COM objects. VS doesn't handle memory management for COM as well as it does for native components. So, you have to either explicitly dispose of some objects in the SDK or use them in a "using" clause.

Also, if you do not close the report several things will happen that can cause problems:

1. The .rpt file is locked on the file system because the viewer won't automatically release it.

2. You will use up the internal licenses - the SDK allows 5 concurrent sessions and up to 100 concurrent "print jobs". A "print job" is an instance of a report or subreport. So, for example, if you have just a main report, you have one print job. If you have a main report with a single subreport that runs in either the report header or footer (once per report), you have two print jobs. If you have a main report with a single subreport in the details section (once for every row of data) you'll have the number of rows +1 print jobs (10 rows = 11 print jobs, 100 rows = 101 print jobs which will cause an error).

I usually use the "FormClosing" method of the viewer form to handle the close and dispose of the report. It might look like this if "rptDoc" is my ReportDocument:

private void frmViewer_FormClosing(object sender, FormClosingEventArgs e)
{
rptDoc.Close();
rptDoc.Dispose();
}

-Dell

[Former Member]

Thank you Dell! I'm a newbie with VS and C#, and this is my first-ever VS project--my background is Java. So I reached into the deep recesses of my brain, thinking on Window events and the managing of them. I then realized I needed to set the FormClosing property of my form in the Design view Properties panel with the name I chose. And just like magic, the method appeared in my code where it needed to be. I was then able to call the Close() and Dispose() methods you listed. And when I tested closed the viewer, the method executed. Thank you for helping me through to the bitter end!

[DellSC]

Glad you were able to get it working!

-Dell

[Former Member]

I think I figured the last part out on my own. Since my form is the viewer, I don't need to call Close. When my form is closed by clicking the red X, the viewer and report are closed and disposed. I've tested my new and improved application, and there are smiles all around. Even the command table seems to be working well. Since I am the only one in my company doing the programming support of Crystal, I don't have anyone to talk things over with. So thanks for being my sounding board, and guiding me to the updated information.

[Former Member]

If this is a double post again, I apologize.

Dell,

Thank you for your comments on the commands. I am not the one writing the reports, so I passed the link to your Best Practices blog post to the report developer. I was able to run a test report with a couple of Command tables in the Parameter Test app, but the report locked up the test app, which was also happening in my app. The data displayed a single screen, but then I was unable to click on anything else, including the red X that normally closes a window. That made me suspect that processing was still occurring, and control had not been returned to the viewer yet. After waiting for 4 minutes, I used Task Manager to kill the process. This report has been working for nearly 6 months, with the server set to Windows authentication. But with Server Authentication, there is a big problem.

Don,

Thank you for the KBA number. I had downloaded that test app very early this year, and it was very useful in helping me do the parameter handling as I was developing my app. And I discovered that the test app had been updated since then, so I was glad to see newer code. I needed to replicate 5 functions--OpenReport, SetParams, ReplaceConnection, ViewReport, and CloseReport. As I compared the test code to my app, I saw that I had nearly identical processing, once I removed all the GUI management of the test app. I did find 3 discrepancies. Two of them were the names of properties that went into the property bag for the new connection info. I was using the names "Server" and "Database" as specified in the RAS API. In the test app, they were changed to "Data Source" and "Initial Catalog", which are stated in the Attributes property description, and are listed in CR Designer when expanding the connection properties of a report in Database > Set Datasource Location. Since I was able to run my test report in the Parameter Test app, I changed my code to use the names in the test app.

The other difference between the two programs is how the report is closed. In the test app, there is a "Close Rpt" button. In my app, there is no button. The viewer launches, and then the customer clicks the red X in the upper right corner to close the viewer. As any good programmer knows, if you open something, you should close it. And I was closing my report nearly the next line after setting the viewer to the report object, which was doing the launching. On a whim, I decided to comment the reportDocument.Close() line, and my app started working the way I expected.

So my question is, with a Windows Forms app, is it necessary for me to manually close the report? My app launches the viewer and then moves on, and is typically no longer executing at the time the customer closes the report. If I need to make sure to close the report, how would I hook into any kind of event or class that might already be doing cleanup? As I said, there are no buttons to click. The viewer is open and displaying an active report until the customer closes it by clicking the red X. Or am I missing something that should be obvious?

,

Dell,

Thank you for your comments on the commands. I am not the one writing the reports, so I passed the link to your Best Practices blog post to the report developer. I was able to run a test report with a couple of Command tables in the Parameter Test app, but the report locked up the test app, which was also happening in my app. The data displayed a single screen, but then I was unable to click on anything else, including the red X that normally closes a window. That made me suspect that processing was still occurring, and control had not been returned to the viewer yet. After waiting for 4 minutes, I used Task Manager to kill the process. This report has been working for nearly 6 months, with the server set to Windows authentication. But with Server Authentication, there is a big problem.

Don,

Thank you for the KBA number. I had downloaded that test app very early this year, and it was very useful in helping me do the parameter handling as I was developing my app. And I discovered that the test app had been updated since then, so I was glad to see newer code. I needed to replicate 5 functions--OpenReport, SetParams, ReplaceConnection, ViewReport, and CloseReport. As I compared the test code to my app, I saw that I had nearly identical processing, once I removed all the GUI management of the test app. I did find 3 discrepancies. Two of them were the names of properties that went into the property bag for the new connection info. I was using the names "Server" and "Database" as specified in the RAS API. In the test app, they were changed to "Data Source" and "Initial Catalog", which are stated in the Attributes property description, and are listed in CR Designer when expanding the connection properties of a report in Database > Set Datasource Location. Since I was able to run my test report in the Parameter Test app, I changed my code to use the names in the test app.

The other difference between the two programs is how the report is closed. In the test app, there is a "Close Rpt" button. In my app, there is no button. The viewer launches, and then the customer clicks the red X in the upper right corner to close the viewer. As any good programmer knows, if you open something, you should close it. And I was closing my report nearly the next line after setting the viewer to the report object, which was doing the launching. On a whim, I decided to comment the reportDocument.Close() line, and my app started working the way I expected.

So my question is, with a Windows Forms app, is it necessary for me to manually close the report? My app launches the viewer and then moves on, and is typically no longer executing at the time the customer closes the report. If I need to make sure to close the report, how would I hook into any kind of event or class that might already be doing cleanup? As I said, there are no buttons to click. The viewer is open and displaying an active report until the customer closes it by clicking the red X. Or am I missing something that should be obvious?

[former_member11696]

Hi Geri,

Search for KBA 2281780, that is a Parameter Test app and has database log on properties that should work.

When using SQL Auth then Replace connection is not the best API to use.

The app has a UI that you can enter in the connection info or change the Viewer property values for the connection info so you don't have to enter it each time.

See if that works for you

Also, what SP are you using?

PS - I did not write that one but it is a gooder, it doesn't generate code for subreports though so you must do that also.

[DellSC]

I think the issue may stem from the fact that the report is saved with the "Integrated Security" checkbox checked. So, the runtime is expecting to use it and then shows the login screen when the auto-login doesn't work.

Are you explicitly setting ConnectionInfo.IntegratedSecurity = false in your code when you set the logon info? Also, do you have any subreports in this report? If so, are you setting the connection info for the tables in the subreport?

Outside of this issue, from a report efficiency standpoint, it's not recommended that you combine tables, with stored procedures and/or commands. When you're using just tables, Crystal can push the joins down to the database. Stored procs and commands also process completely in the database. However, when you join any combination of these other than just tables, Crystal will pull ALL of the data into memory and process the joins and any criteria in the Select Expert in memory. If you're just pulling small amounts of data, this may not be a big deal. However, for large amounts of data this can cause significant slowness in the report. The only time this might work well would be if the report itself only uses data from the Stored Proc and then the tables and command are used solely to populate data for dynamic prompts - in this case there would be no joins to be processed in memory. If you are joining these together, your best bet for efficiency is going to be to create a single command to get all of the data. For more info about how to work with commands, see this: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.

-Dell

[Former Member]

Dell,

Thank you for your prompt response. Unfortunately, the tutorial code only works if I don't have to change the server or database name information. The documentation says it's supposed to work, but I get an error stating I have "Incorrect log on information".

I have successfully used the CodeBuilder utility that Don Williams wrote, to get past that point of processing. The CR report window opens, but when the SQL Server is required to use Server auth, the DB logon window prompts for a password, even when I have it hard-coded in the attributes property bag. This is for an unmanaged, non-embedded report. One thing I've seen in my testing is that the CodeBuilder utility code is necessary when changing the database name, as the table.QualifiedName must specifically be set to use the new database name. Otherwise, the old database name is left in place and causes the report to fail.

I have a report that has 2 tables, a stored procedure, and a command table. I open it in Designer and set the Datasource Location to the same access as my server instance and database--requiring a username and password. It seems like there should be no issue in opening the report. My application executes the tutorial method ConfigureCrystalReports, which calls another tutorial method SetDBLogonForReport. And the DB logon window still pops up asking for a password, even though the SQLServer, the report, and my source code specify the same information for the server name, the database name, the user name, and the password.

I'm really at a loss in understanding why Crystal continues to ask for the password. For what it's worth, if I click the Integrated Security box, the report displays. And if I type in the password, the report displays. But I have to figure out how to get the report to display without the logon prompt, when the Server requires Server authentication. Any additional ideas for me to try?

,

Dell,

Thank you for your prompt response. Unfortunately, the tutorial code only works if I don't have to change the server or database name information. The documentation says it's supposed to work, but I get an error stating I have "Incorrect log on information".

I have successfully used the CodeBuilder utility that Don Williams wrote, to get past that point of processing. The CR report window opens, but when the SQL Server is required to use Server auth, the DB logon window prompts for a password, even when I have it hard-coded in the attributes property bag. This is for an unmanaged, non-embedded report. One thing I've seen in my testing is that the CodeBuilder utility code is necessary when changing the database name, as the table.QualifiedName must specifically be set to use the new database name. Otherwise, the old database name is left in place and causes the report to fail.

I have a report that has 2 tables, a stored procedure, and a command table. I open it in Designer and set the Datasource Location to the same access as my server instance and database--requiring a username and password. It seems like there should be no issue in opening the report. My application executes the tutorial method ConfigureCrystalReports, which calls another tutorial method SetDBLogonForReport. And the DB logon window still pops up asking for a password, even though the SQLServer, the report, and my source code specify the same information for the server name, the database name, the user name, and the password.

I'm really at a loss in understanding why Crystal continues to ask for the password. For what it's worth, if I click the Integrated Security box, the report displays. And if I type in the password, the report displays. But I have to figure out how to get the report to display without the logon prompt, when the Server requires Server authentication. Any additional ideas for me to try?

[DellSC]

You can do this in CRVS. However, you have to handle Windows authentication differently than Server authentication.

When you use Windows Authentication in SQL Server, you'll essentially have SSO to the database so you don't have to set any credentials when you run the report. The connection to the database will automatically pick up the context of the current Windows user and use that to authenticate. This process uses "Integrated Security".

When you use SQL Server Authentication, you MUST pass the user ID and Password to the database to log in. Crystal will save the database information and possibly the user ID in the report, but it will NOT save the password. So, you'll have to come up with some way to save the user ID and password information for your application to use and then specifically set the database logon for each table in the report AND in any subreports every time the report is run.

You can find sample code for both of these processes by going to https://blogs.sap.com/2016/12/06/sap-crystal-reports-developer-version-for-microsoft-visual-studio-u..., scrolling to the bottom of the page, and downloading the .NET SDK Developer Guide. There is a Tutuorials section that includes sample code for both types of authentication.

-Dell