CODEWITHSUNDEEP
encryptioncrystal-reportsadosql-server-2019crystal-reports-2016
Andy Summers
Andy Summers

Reputation: 1

SQL encrypt connection using OLE DB (ADO)

I'm using a SQL Server 2019 with a self-signed certificate.

Crystal Reports 2016 throws this error:

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 I can't get it to work.

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

Visual Studio 2013 (web viewer): 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.

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

I've 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

Upvotes: 0

Views: 111

Answers (1)

SANG
SANG

Reputation: 1

To specify encryption for the connection in your code, you can add the Encrypt property to your ConnectionInfo object or you can use the Encrypt property in the connection string:

   ' Setting the Encryption Property to your ConnectionString'
.ConnectionString = "Provider=MSOLEDBSQL19;Data Source=" & lcServer & ";Initial Catalog=" & .DatabaseName & ";User ID=" & .UserID & ";Password=" & .Password & ";Encrypt=True;"

   ' Setting the Encryption Property to your ConnectionInfo
.LogonProperties.Set("Connection Timeout", "600")
.LogonProperties.Set("Encrypt", "True")

Ensure the connection string is correctly applied in your code. If this still doesn't work, you might want to check if your SQL Server is configured to accept encrypted connections and that the necessary certificates are in place.

Upvotes: 0

Related Questions