Connecting to a SQL Database with an encrypted connection

[#1]

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?

[Clas_Hortien]

Hi,

I'm not 100% sure, but maybe the SQL Server option "force encryption" in the SQL Server Configuration manager will do the trick. It tells the SQL Server to only accept and build encrypted connections when a client connects. See this KB article for more details: Link

Regards

Clas

[Former Member]

Hi, thanks for this, it is the intention of our customers that all applications connecting to their SQL Server will be required to connect to SQL with encryption and that is why we are amending our applications to make this change but they cannot switch SQL Server to force encryption until all applications have been changed to make an encrypted connection.

But I think we tested this option in our test environment when we amended our main application to use an encrypted connection as well as our printing engine that uses Crystal Reports and I thought our experience was that if SQL was set to force encryption it would just reject connections that were not encrypted, it would not make connections default to encrypted. So our print engine as currently coded was then just failing to connect because we had not been able to set the encrypted property on the crystal report connection.