title: Crystal Reports - change the provider from OLEDBSQL to MSOLEDBSQL to support TLS 1.2 by code
body: <p>I am trying to change the provider of my Crystal Reports from <code>OLEDBSQL</code> to <code>MSOLEDBSQL</code> as <code>OLEDBSQL</code> does not support <code>TLS 1.2</code></p>
  <p><strong>My Code:</strong></p>
  <pre><code>Private Sub ProcessReport()
      Dim crxRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
      crxRpt.Load(&quot;D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\SummaryReport.rpt&quot;)
  
       ChangeOleDbConnectionInfo(crxRpt, &quot;SERVER&quot;, &quot;DBNAME&quot;, &quot;MSOLEDBSQL&quot;, False, &quot;USERID&quot;, &quot;PASSWORD&quot;)
  End Sub
  
  Friend Shared Sub ChangeOleDbConnectionInfo(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String)
  
      Dim boMainPropertyBag = New NameValuePairs2()
      Dim boInnerPropertyBag = New NameValuePairs2()
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Application Intent&quot;, &quot;READWRITE&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Auto Translate&quot;, &quot;-1&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Connect Timeout&quot;, &quot;15&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Data Source&quot;, server))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;DataTypeCompatibility&quot;, &quot;0&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;General Timeout&quot;, &quot;0&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Initial Catalog&quot;, database))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Integrated Security&quot;, If(integratedSecurity, &quot;True&quot;, &quot;False&quot;)))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Locale Identifier&quot;, &quot;1033&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;MARS Connection&quot;, &quot;0&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;OLE DB Services&quot;, &quot;-5&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Provider&quot;, oledbProvider))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Tag with column collation when possible&quot;, &quot;0&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Trust Server Certificate&quot;, &quot;0&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Use DSN Default Properties&quot;, &quot;False&quot;))
      boInnerPropertyBag.Add(New NameValuePair2(&quot;Use Encryption for Data&quot;, &quot;0&quot;))
  
  
      boMainPropertyBag.Add(New NameValuePair2(&quot;Database DLL&quot;, &quot;crdb_ado.dll&quot;))
      boMainPropertyBag.Add(New NameValuePair2(&quot;QE_DatabaseName&quot;, database))
      boMainPropertyBag.Add(New NameValuePair2(&quot;QE_DatabaseType&quot;, &quot;OLE DB (ADO)&quot;))
      boMainPropertyBag.Add(New NameValuePair2(&quot;QE_LogonProperties&quot;, boInnerPropertyBag))
      boMainPropertyBag.Add(New NameValuePair2(&quot;QE_ServerDescription&quot;, server))
      boMainPropertyBag.Add(New NameValuePair2(&quot;QE_SQLDB&quot;, &quot;True&quot;))
      boMainPropertyBag.Add(New NameValuePair2(&quot;SSO Enabled&quot;, &quot;False&quot;))
  
      Dim conAttributes As New CrystalDecisions.Shared.DbConnectionAttributes()
      conAttributes.Collection = boMainPropertyBag
  
      Dim boConnectionInfo = New ConnectionInfo With {
          .Attributes = conAttributes,
          .DatabaseName = database,
          .ServerName = server
      }
  
      If Not integratedSecurity Then
          boConnectionInfo.UserID = userId
          boConnectionInfo.Password = password
      End If
  
      Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables = reportDocument.Database.Tables
  
      For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables
          Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
          myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()
          myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
      Next
  
      Dim mySections As CrystalDecisions.CrystalReports.Engine.Sections = reportDocument.ReportDefinition.Sections
      For Each mySection As CrystalDecisions.CrystalReports.Engine.Section In mySections
          Dim myReportObjects As CrystalDecisions.CrystalReports.Engine.ReportObjects = mySection.ReportObjects
          For Each myReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In myReportObjects
              If myReportObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                  Dim mySubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject = CType(myReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                  Dim subReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = mySubreportObject.OpenSubreport(mySubreportObject.SubreportName)
                  Dim mytablessub As CrystalDecisions.CrystalReports.Engine.Tables = subReportDocument.Database.Tables
                  For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In mytablessub
                      Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
                      myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()
  
                      myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
                      
                  Next
              End If
          Next
      Next
  
      reportDocument.VerifyDatabase()
  
      reportDocument.SaveAs(&quot;D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\CrystallReportConvertVB\Rpt-changed3.rpt&quot;)
  End Sub
  </code></pre>
  <p><strong>Im using the following Crystal Report Versions:</strong></p>
  <p><a href="https://i.stack.imgur.com/umJ4l.png" rel="noreferrer"><img src="https://i.stack.imgur.com/umJ4l.png" alt="Crystal Reports References " /></a></p>
  <p><strong>I have noted the following</strong></p>
  <ol>
  <li>Calling <code>verifyDatabase()</code> or <code>refresh()</code> on the <code>ReportDocument</code> changes the provider property back into original.</li>
  <li>I have tried the <code>ApplyLogOnInfo()</code> - Found that it only reset the username and password and not the provider. Calling this method also resets the changed provider.</li>
  <li>Now that I've managed to change the Provider by the following code, but It always reset the provider to the original provider.</li>
  </ol>
  <p>How can this be done?
  Are there any workarounds for this?</p>
  <p><strong>Further Update</strong></p>
  <p>I was able to change the provider to MSOLEDBSQL in the main report by using the code suggested by <a href="https://stackoverflow.com/a/62102646/4353760">Frank</a> (With thanks) below. But still, it fails for the subreports and gives the following error.</p>
  <blockquote>
  <p>Not supported within subreports</p>
  </blockquote>
  <p><a href="https://i.stack.imgur.com/mS0Od.png" rel="noreferrer"><img src="https://i.stack.imgur.com/mS0Od.png" alt="rpt.ReportClientDocument.DatabaseController.Database.Tables -&gt; Not supported within sub reports." /></a></p>
  <p><strong>Further Update (2) - on Subreport provider change</strong>
  I was able to change the provider for Crystal Report Subreports by the following code.</p>
  <pre><code>Friend Shared Function ChangeOleDbConnectionInfoSubReports(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal schema As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String) As ReportDocument
  
      Dim boInnerPropertyBag = New PropertyBag()
      boInnerPropertyBag.Add(&quot;Application Intent&quot;, &quot;READWRITE&quot;)
      boInnerPropertyBag.Add(&quot;Auto Translate&quot;, &quot;-1&quot;)
      boInnerPropertyBag.Add(&quot;Connect Timeout&quot;, &quot;15&quot;)
      boInnerPropertyBag.Add(&quot;Data Source&quot;, server)
      boInnerPropertyBag.Add(&quot;DataTypeCompatibility&quot;, &quot;0&quot;)
      boInnerPropertyBag.Add(&quot;General Timeout&quot;, &quot;0&quot;)
      boInnerPropertyBag.Add(&quot;Initial Catalog&quot;, database)
      boInnerPropertyBag.Add(&quot;Integrated Security&quot;, If(integratedSecurity, &quot;True&quot;, &quot;False&quot;))
      boInnerPropertyBag.Add(&quot;Locale Identifier&quot;, &quot;1033&quot;)
      boInnerPropertyBag.Add(&quot;MARS Connection&quot;, &quot;0&quot;)
      boInnerPropertyBag.Add(&quot;OLE DB Services&quot;, &quot;-5&quot;)
      boInnerPropertyBag.Add(&quot;Provider&quot;, oledbProvider)
      boInnerPropertyBag.Add(&quot;Tag with column collation when possible&quot;, &quot;0&quot;)
      boInnerPropertyBag.Add(&quot;Trust Server Certificate&quot;, &quot;0&quot;)
      boInnerPropertyBag.Add(&quot;Use DSN Default Properties&quot;, &quot;False&quot;)
      boInnerPropertyBag.Add(&quot;Use Encryption for Data&quot;, &quot;0&quot;)
      Dim boMainPropertyBag = New PropertyBag()
      boMainPropertyBag.Add(&quot;Database DLL&quot;, &quot;crdb_ado.dll&quot;)
      boMainPropertyBag.Add(&quot;QE_DatabaseName&quot;, database)
      boMainPropertyBag.Add(&quot;QE_DatabaseType&quot;, &quot;OLE DB (ADO)&quot;)
      boMainPropertyBag.Add(&quot;QE_LogonProperties&quot;, boInnerPropertyBag)
      boMainPropertyBag.Add(&quot;QE_ServerDescription&quot;, server)
      boMainPropertyBag.Add(&quot;QE_SQLDB&quot;, &quot;True&quot;)
      boMainPropertyBag.Add(&quot;SSO Enabled&quot;, &quot;False&quot;)
  
      Dim boConnectionInfo = New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo With {
          .Attributes = boMainPropertyBag,
          .Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
      }
  
      If Not integratedSecurity Then
          boConnectionInfo.UserName = userId
          boConnectionInfo.Password = password
      End If
  
      For Each subreport As ReportDocument In reportDocument.Subreports
          For Each table As CrystalDecisions.ReportAppServer.DataDefModel.Table In reportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables
  
              Dim boTable = New CrystalDecisions.ReportAppServer.DataDefModel.Table With {
                  .ConnectionInfo = boConnectionInfo,
                  .Name = table.Name,
                  .QualifiedName = schema &amp; &quot;.&quot; &amp; table.Name,
                  .[Alias] = table.[Alias]
              }
  
              reportDocument.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, boTable)
          Next
      Next
  
      reportDocument.VerifyDatabase()
      Return reportDocument
  
  End Function
  </code></pre>
  <p><strong>Lessons Learnt</strong></p>
  <blockquote>
  <p>Noticed that, I can not update the driver if the installed Crystal
  Report Runtime does not match with the Visual studio references (I have taken the DLLs from some Nuget packages that are not official releases). I
  Couldn't make the code work with Crystal Reports 10.5.3700 that I used
  and I updated the runtime to 13.0.2000 (<a href="https://www.tektutorialshub.com/crystal-reports/how-to-download-and-install-crystal-report-runtime/" rel="noreferrer">As per this article</a>)</p>
  <p><a href="https://i.stack.imgur.com/0OCvO.png" rel="noreferrer"><img src="https://i.stack.imgur.com/0OCvO.png" alt="enter image description here" /></a></p>
  <p>Crystal Reports also have tight rules with the processor architecture.
  Since I'm using a 32bit version, I have set all the settings in Visual
  Studio to compile the project as a 32bit app.</p>
  </blockquote>