Connecting to a SQL Database with encrypted communications.

[Former Member]

Hello. After searching far and wide on google only to be shown many examples that do not relate to my situation. I am using ASP.NET connecting to a SQL Server 2005. My reports work very well, and have no issue connecting to a database that doesn't require encryption.

Example of non-encrypted connectionstring in web.config

<add name="PORTAL" connectionString="Data Source=TSO-IAD-53;Initial Catalog=PORTAL;

Persist Security Info=True;User ID=PORTALWEB;Password=PasswordHere;" providerName="System.Data.SqlClient" />

VB.NET code to hook up crystal reports to the connection string

        Private Shared Sub SetDBLogonForReport(ByRef myReportDocument As ReportDocument, ByVal ConStr As String)


            Dim MyCon As New ConnectionInfo

            With MyCon
                .DatabaseName = GetConnectionStringValue(ConStr, "Initial Catalog")
                .ServerName = GetConnectionStringValue(ConStr, "Data Source")
                .UserID = GetConnectionStringValue(ConStr, "User ID")
                .Password = GetConnectionStringValue(ConStr, "Password")
            End With

            Dim myTables As Tables = myReportDocument.Database.Tables
            Dim myTableLogonInfo As New TableLogOnInfo

            For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
                myTableLogonInfo = myTable.LogOnInfo
                myTableLogonInfo.ConnectionInfo = MyCon
                myTable.ApplyLogOnInfo(myTableLogonInfo)
            Next

        End Sub

This works great, but requiresments have come to use encrypted connections, easy enough for the connectionstring.

Example of new connection string

<add name="PORTAL" connectionString="Data Source=TSO-IAD-53;Initial Catalog=PORTAL;

Persist Security Info=True;User ID=PORTALWEB;Password=PasswordHere;

Encrypt=Yes;TrustServerCertificate=Yes;" providerName="System.Data.SqlClient" />

Once I add the new parameter of Encrypt, The application talks fine, but reports then fail to load giving me a Logon Failed error. I know it is because of the encrypted conversation. I haven't found how to make crystal talk in encrypted mode.

Please help

Edited by: Brandon Meyer on Aug 15, 2008 5:20 PM

[Former Member · Accepted solution]

Which version of Crystal Reports are you using? The regular ReportDocument SDK probably isn't robust enough to handle this, unless you create a connection using that connection string at design-time which you don't change at runtime. You might be able to add those properties using the in-proc RAS SDK, though. This is available with CRXI R2 as of SP2, and CR2008.

[Former Member · Accepted solution]

Sorry for not mentioning it. Yes I am using CR2008

[former_member208657 · Accepted solution]

Start with the Crystal Reports designer and get it to work there first. If you can't do it in the designer then you'll likely need to pull the data on your own and populate a .NET DataSet to push to the ReportDocument with SetDataSource.

[former_member11696]

When you create a new report using the Database Connection Wizard there is an option to use Trusted Authentication, check this on and the info should propogate to your application. You do require configuring the Server to use NT Authentication or SQL authentication will work also.

[Former Member]

Well, that gives me a place to start looking. Problem with going to a dataset for the report is now i have to make a new method for each report to provide the right dataset.