Skip to Content
0
Oct 29, 2019 at 03:51 PM

Connecting to a SQL Database with an encrypted connection

520 Views

We have some standard .Net code running in Visual Studio 2010 to run Crystal 2016 reports (and older) which call SQL Stored Procedures using 13.0.21 Crystal Runtime, this all works lovely currently. We want to apply encryption to the SQL connection. If in the connection properties within the Crystal Report itself we set the properties on an OLE DB (ADO) connection Use Encryption For Data = -1 and Trust Server Certificate = -1 then when running the report an encrypted SQL connection is created. But our customers have hundreds of reports and we don't want them to have to change each report to do this and some of their reports have an ODBC (RDO) connection within the report which do not seem to support encryption (well at least with those property settings). So we have tried to amend the connection dynamically in .Net.

Our original base code is

Private Sub AddConnectionInfo()

Dim crTables AsTables

Dim tableLogonInfos As New TableLogOnInfos()

Dim connectionInfo As New ConnectionInfo()

Dim tableLogonInfo As New TableLogOnInfo()

connectionInfo.ServerName = _serverName

connectionInfo.DatabaseName = _databaseName

connectionInfo.UserID = _userName

connectionInfo.Password = _password

crTables = Report.Database.Tables

For Each crTable As CrystalDecisions.CrystalReports.Engine.Table In crTables

tableLogonInfo = crTable.LogOnInfo.Clone()

tableLogonInfo.ConnectionInfo = connectionInfo

tableLogonInfo.TableName = CleanTableName(tableLogonInfo.TableName)

crTable.ApplyLogOnInfo(tableLogonInfo)

crTable.Location = tableLogonInfo.TableName

Next crTable

End Sub

We think we have tried all of the Googleable options setting the LogonInfo.ConnectionInfo Attributes and LogonProperties to exactly match that of a model Crystal Report which has been saved with encryption properties in it. The report continues to run ok but it forms a SQL connection based on whatever the saved connection within the report was, so if that was an ODBC (RDO) unencrypted connection it does that.

If we debug the code it appears to us as though after the ApplyLogonInfo call, the properties are set as revised but when the next line of code is executed the relevant properties appear to revert to whatever was saved within the Crystal Report being run.

Can anyone suggest anything we can try to get this to work?