Skip to Content

Connecting to a SQL Database with an encrypted connection

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Oct 29, 2019 at 04:04 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.