Crystal reports and SQL encrypt connection using OLE DB (ADO)

[AndySWS24]

SQL 2019 with a self-cert certificate.

Crystal Reports 2016 SAP Crystal Reports # -

Microsoft OLE DB Driver 19 for SQL Server - ADO Error Code 0x80040e73 Format of the initialization string does not conform to the OLE DB specification (this is the recommended type to use? but cant get it to work)

Microsoft OLE DB Provider for SQL Server - if I add parameters of encrypt=Yes and TrustServerCertificate=Yes then connection ok and works within CR

Visual Studio 2013 (CR web viewer) SAP Crystal Reports, version for Visual Studio # - code to update the reports Database name, password, location - works fine for non-encrypted connection - how to specify to encrypt the connection? It either ignores the added attributes or errors with cannot load the database

(Can anyone confirm if I update the project to VS2022 will it work? Maybe it's a TLS1.2 thing?)

  With ConnInfo

            .ServerName = lcServer

            .DatabaseName = "zzzlive"

            If Not "" & ConfigurationSettings.AppSettings("Database") = "" Then

                .DatabaseName = ConfigurationSettings.AppSettings("Database")

            End If

            .UserID = wjs.getDBUserName

            .Password = wjs.getDBUserPassword

            .LogonProperties.Set("Connection Timeout", "600")

End With

        Dim cntli As TableLogOnInfo

        For Each cntable As CrystalDecisions.CrystalReports.Engine.Table In loReport.Database.Tables

            cntli = cntable.LogOnInfo

            cntli.ConnectionInfo = ConnInfo

            cntable.ApplyLogOnInfo(cntli)

  Next

        For Each cnsection As Section In loReport.ReportDefinition.Sections

            For Each cnrepobject As ReportObject In cnsection.ReportObjects

                If cnrepobject.Kind = ReportObjectKind.SubreportObject Then

                    Dim losubrep As SubreportObject = CType(cnrepobject, SubreportObject)

                    Dim subrepdoc As ReportDocument = losubrep.OpenSubreport(losubrep.SubreportName)

                    For Each cntable As CrystalDecisions.CrystalReports.Engine.Table In subrepdoc.Database.Tables

                        cntli = cntable.LogOnInfo

                        cntli.ConnectionInfo = ConnInfo

                        cntable.ApplyLogOnInfo(cntli)

                    Next

                End If

            Next

        Next

Tried many variations...

Tried setting the login parameters

.LogonProperties.Set("Use Encryption for Data", "True")

or

.LogonProperties.Set("Extended Properties", "TrustServerCertificate=No;Encrypt=Yes")

Tried adding to the attributes collection

.Attributes.Collection.Add(New NameValuePair2("Extended Properties", "TrustServerCertificate=Yes;Encrypt=Yes"))

or 

.Attributes.Collection.Add(New NameValuePair2("Encrypt", "Yes"))

.Attributes.Collection.Add(New NameValuePair2("TrustServerCertificate", "Yes"))

Tried Property bag code

            Dim connectionAttributes As New CrystalReportsDataDefModelLib.PropertyBag

            connectionAttributes.EnsureCapacity(11)

            connectionAttributes.Add("Connect Timeout", "600")

            connectionAttributes.Add("Data Source", lcServer)

            connectionAttributes.Add("General Timeout", "0")

            connectionAttributes.Add("Initial Catalog", .DatabaseName)

            connectionAttributes.Add("Integrated Security", False)

            connectionAttributes.Add("Locale Identifier", "2057")

            connectionAttributes.Add("OLE DB Services", "-5")

            connectionAttributes.Add("Provider", "SQLOLEDB")

            connectionAttributes.Add("Tag with column collation when possible", "0")

            connectionAttributes.Add("Use DSN Default Properties", False)

            connectionAttributes.Add("Use Encryption for Data", "0")

            Dim attributes As New DbConnectionAttributes()

            attributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ADOPLUS))

            attributes.Collection.Add(New NameValuePair2(DbConnectionAttributes.QE_DATABASE_NAME, .DatabaseName))

            attributes.Collection.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))

            attributes.Collection.Add(New NameValuePair2("QE_LogonProperties", connectionAttributes))

            attributes.Collection.Add(New NameValuePair2("QE_ServerDescription", lcServer))

            attributes.Collection.Add(New NameValuePair2("QE_SQLDB", "true"))

            attributes.Collection.Add(New NameValuePair2("SSO Enabled", "false"))

            .AllowCustomConnection = True

            .Attributes = attributes

            .Type = ConnectionInfoType.CRQE

also tried this variation

            Dim connectionAttributes As New DbConnectionAttributes

            connectionAttributes.Collection.Add(New NameValuePair2("Connect Timeout", "600"))

            connectionAttributes.Collection.Add(New NameValuePair2("Data Source", lcServer))

            connectionAttributes.Collection.Add(New NameValuePair2("General Timeout", "0"))

            connectionAttributes.Collection.Add(New NameValuePair2("Initial Catalog", .DatabaseName))

            connectionAttributes.Collection.Add(New NameValuePair2("Integrated Security", False))

            connectionAttributes.Collection.Add(New NameValuePair2("Locale Identifier", "2057"))

            connectionAttributes.Collection.Add(New NameValuePair2("OLE DB Services", "-5"))

            connectionAttributes.Collection.Add(New NameValuePair2("Provider", "SQLOLEDB"))

            connectionAttributes.Collection.Add(New NameValuePair2("Tag with column collation when possible", "0"))

            connectionAttributes.Collection.Add(New NameValuePair2("Use DSN Default Properties", False))

            connectionAttributes.Collection.Add(New NameValuePair2("Use Encryption for Data", "0"))

            .Attributes = connectionAttributes

[DonWilliams · Accepted solution]

Hello,

A few things to consider when using CR and TLS 1.2.

First, CR assumes the connection to SQL server is working under TLS 1.2 encryption, it's the Client that is handing the security, not CR, CR uses the Corba layer to communicate to the Client, how the Client talks to the Server is of no concern. The error message returned is from the Client, so clear that first.

Check this KBA for more details on setting up MS SQL Server, note it is old so some or all of the info may be out dated:

https://userapps.support.sap.com/sap/support/knowledge/en/2269180

Also note that the reports MUST be updated, you cannot use TLS 1.2 with older MS Clients, Set Location in the Report Designer. MS changed the name of the client dll and the only way for this to work is to first update and then validate connection in CR Designer first. Also I like to verify the Client is configured by testing the connection using an ODBC DSN, if that works then OLD DB connection will work also.

Second, check the PAM for CR for VS and CR Designer, currently OLE DB 19 is not supported:

https://www.sap.com/documents/2016/06/f871031e-757c-0010-82c7-eda71af511fa.html

ODBC typically works better but this changes from MS Client version to version, sometimes ODBC works, sometimes OLE DB works....

Your options are to down grade to MS SQL 18 Client first, or test using CR Designer first and save the report if it works.

I'm retired now and I don't recall why R&D has not added MS 19 Client support, likely some bug in the client that R&D is not willing to write code to workaround. History lesson: As of CR 9 R&D rewrote the database Client parts (CRQE.DLL) to remove all of the "bugs" in the various clients, it became to much to maintain so the result is if there's a bug in client it's up to the makers to fix them.

FYI - search for a KBA on how to enable CRLogger to trace CR's database logs

[AndySWS24 · Accepted solution]

Hi Don, thank you for all the information. I've now tried the v18 Driver too. I've found that I can't optionally encrypt the connection, either from CR itself or from the code, it either ignores the encrypt parameter or errors. However surprisingly to me, I've discovered if SQL is set to force the encryption that it automatically still connects and works as it is, without even having to explicitly specify to encrypt the connection or specify to trust the self-signed certificate - so I think I can just force encryption and not have to make any report or code changes.

[DonWilliams · Accepted solution]

Great, as I explained once the connection is configurred in the Client CR will just work without having to configure anything else.