Solved: Disable Windows Authentication on Data Source in R...

vwpcs · a month ago

We have a customized version of crystal web viewer that seems to run on CRRuntime_64bit_13_0_21 for .net framework. We have a problem where the web viewer cannot handle Integrated Security or Trusted_Connection. We have customers that encounter logon errors when they try to use report that they created in Crystal Designer 2016 with those options enabled. We're able to resolve this by updating the data source in Crystal Designer 2016 using Set Datasource Location. We're looking for a way to update a larger number of rpt files quickly.

We've tried many different ways using powershell and .net. We can update the server name, database name, user, and ODBC Type. However, we've been unsuccessful trying to disable the options for win auth. Most recently, I installed the .net sdk for VS 2023. I copied the sample code for c# and adapted it for use in powershell. Sample code found on topic 'To Log on to a Secure SQL Server Database with the ReportDocument object model'. The updated code is shown below.

The updated code completes without errors and does save a new rpt file. However, the options for Integrated Security and Trusted Connection still show as enabled when viewing Properties in Set Datasource Location screen in Crystal Designer 2016.

Do you think this is the best approach for solving this problem (updating the settings directly) ? We do have standard System DSNs on our servers used to connect to our app database. I wonder if we should be trying to connect to those instead.

I found settings in $report.DataSourceConnections.logonproperties that don't seem to change. Seems weird to me though because we are able to set other options (server, db, user) there.

I also found another post on this site saying exporting the report document to a rpt file may work, but i tried that and still have the same problem. A few other posts tend to send me to a KBA that does describe my symptoms, logon info not saving to rpt file. That says use the RAS SDK. I don't have a RAS that I know of, just Crystal Designer and Runtime. I'm not a dev for the custom web viewer so I'm not able to change that code and makes me think I can't change these settings at runtime. I assume I need to update these rpt files.

# Loading Crystal Reports assemblies
[reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared') | fl
[reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine') | fl

# Create a new report document
$report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument

# Load the report
$report.Load("G:\OrigReport.rpt")

    $connectionInfo = New-Object CrystalDecisions.Shared.ConnectionInfo;
    $connectionInfo.DatabaseName = 'MyDB';
    $connectionInfo.UserID = 'Myuser';
    $connectionInfo.Password = 'Mypass';
    $connectionInfo.IntegratedSecurity = $false;

    $tables = $report.Database.Tables;
    foreach ($table in $tables)
    {
        $newLogonInfo = $table.LogOnInfo;
        $newLogonInfo.ConnectionInfo = $connectionInfo;
        $table.ApplyLogOnInfo($newLogonInfo);
    }


# Save the report if needed
$report.SaveAs("G:\ModifiedReport2.rpt")

$report.close()
$report.dispose()

We also tried using SetConnection

# Load the report
$report.Load('G:\OrigReport.rpt')

# Access the DataSourceConnections
$connections = $report.DataSourceConnections

# Modify connection properties for SQL authentication
foreach ($connection in $connections) {
    
    # Access and modify the LogonProperties
    $logonProperties = $connection.LogonProperties
    $logonProperties.Set("Integrated Security", $false)
    $logonProperties.Set("Trusted_Connection", $false)

    $Connection.SetLogonProperties($logonProperties)

    # Set the connection properties
    $connection.SetConnection("MyServer", "MYDB", "YourSQLUserID", "YourSQLPassword")

    $logonProperties = $connection.LogonProperties
}

We also tried export the report

# export report as rpt
$exportOptions = $report.ExportOptions
$exportOptions.ExportFormatType = [CrystalDecisions.Shared.ExportFormatType]::CrystalReport
$exportOptions.ExportDestinationType = [CrystalDecisions.Shared.ExportDestinationType]::DiskFile

# Set the destination options for disk file
$diskFileDestinationOptions = New-Object CrystalDecisions.Shared.DiskFileDestinationOptions
$diskFileDestinationOptions.DiskFileName = 'G:\ModifiedReport.rpt'
$exportOptions.DestinationOptions = $diskFileDestinationOptions

$exportOptions.FormatOptions = $null
$report.Export()

DonWilliams

To explain why this is happening is as follows....

Using the basic SetLogon info in code uses what's saved in the RPT file, all you are actually doing is setting the user name and password, the rest is loaded from the Report when designed.

In short the only way to removed the Trusted Authentication is to manually open each report in the Designer and using Set Location under the Database menu to a new DSN, use the ones you have on the Server, that DSN must be on the PC where you updated the Reports, then Verify the Reports in the Designer and save them to a new location. Then use those in the WEB app.

To actually update the connection info you need to use ReplaceConnection() which is available using RAS. RAS does exist for your use with nothing special to install, it does require code changes though so you will need to get a Developer involved to make the change.

Also, SP 21 is quite old and you should upgrade the runtime to SP 37 which is the latest release, but again it will require a developer to recompile the app to use the updated SP.

To get the latest go here:

https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html

On that page there is a sample app called:

how-to-parameters-in-crystal-reports-for-visual-studio-net

It's a desk top app but the same code can be used in a WEB app. If you search for ReplaceConnection you'll see how it's done. In short here's the work flow:

Open the RPT file using the Engine

Convert the Report to a RAS Report Object

Use ReplaceConnection to update the Connection info and set the Trusted to False.

Save the report.

If you don't have many reports you may want to update each report manually using the Designer.

Also, if you should ask the Developer or maker of the WEB app to give you an updated version that uses the latest SP 37 runtime and at the same time allow you to update the DB connection info using RAS.

Have fun

Don

Disable Windows Authentication on Data Source in RPT File Via .Net

Know the answer?

Need more details?