2548907 – Error: ‘Logon Failed. Unexpected error from external database driver’ when refreshing a report based off MS Excel in Crystal Reports and in SAP BI LaunchPad

Version 11 from 01.11.2017 in EnglishShow Changes

Component:BI-RA-CR

Priority:Normal

Category:Problem

Release Status:Released to Customer

Symptom

• DAO Connection to Excel fails.
• ODBC connection to MS Excel fails.
• Same report used to refresh successfully few days ago.
• When refreshing a report in Crystal Reports based off a MS Excel spreadsheet, it fails with the error:Logon Failed.
  Details: DAO Error Code: 0xccb
  Source: DAO.Workspace
  Description: Unexpected error from external database driver (1)
• In SAP BI LaunchPad, when refreshing a report based off Excel, it prompts for the user name and password.
• On the SAP BI Server or in Crystal Reports Server, the scheduled reports based on and ODBC connection to Excel, or based on Excel data source directly are failing with the same error.

Environment

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP BusinessObjects Enterprise XI 3.1
• SAP BusinessObjects Business Intelligence Platform 4.0
• SAP BusinessObjects Business Intelligence Platform 4.1
• SAP BusinessObjects Business Intelligence Platform 4.2
• SAP Crystal Server 2011
• SAP Crystal Server 2013
• SAP Crystal Server 2016

• MS Windows 7
• MS Windows 8
• MS Windows 10
• MS Windows Server 2012

Reproducing the Issue

• In Crystal Reports:
  1. In Crystal Reports, open a report based off a MS Excel spreadsheet.
  2. When attempting to refresh the report, it fails with the error: “Logon Failed. Unexpected error from external database driver (1)”

• In SAP BI LaunchPad, or Central Management Console (CMC):
  1. View or schedule a report based off MS Excel in SAP BI LaunchPad
  2. It fails with the error:  “Logon Failed. Unexpected error from external database driver (1)”

Cause

• Microsoft Windows Security updates caused the MS Excel connectivity issue for any application using the Jet Engine 4.0
• In other words, it is not a Crystal Reports product defect, but an issue with the Microsoft JET Database Engine cause by a Microsoft Security update.
• For reference, and more details, see the Microsoft blog on the Microsoft JET Database Engine connectivity issue

Resolution

• The best recommended solution provided by Microsoft is to use the latest Microsoft Acess Database Engine OLE DB Provider to connect to MS Excel.
  1. Download the latest Microsoft Access Database Engine 2016 Redistributable from Microsoft.
     • 1.1  Go to the following Microsoft Access Database Engine 2016 Redistributable download link:https://www.microsoft.com/en-us/download/details.aspx?id=54920

• 1. • 1.2  On the Microsoft Access Database Engine 2016 Redistributable page, click “Download”
     • 1.3  In “Choose the download you want” select “AccessDatabaseEngine.exe”, and click “Next” to start the download.
  2. Install the Microsoft Access Database Engine 2016 Redistributable.
  3. Update the report to use the Microsoft Office 16.0 Access Database Engine OLEDB Provider.
     • 3.1  In Crystal Reports designer, open the report based off the MS Excel spreadsheet.
     • 3.2  Under the menu “Database”, select “Set Datasource Location…”
     • 3.3  In “Set Datasource Location” window,  under “Replace with:” expand “Create New Connection”
     • 3.4  Under “Create New Connection”, double click on “OLE DB (ADO)”
     • 3.5  In “OLE DB (ADO)” window, select the OLE DB Provider: “Microsoft Office 16.0 Access Database Engine OLEDB Provider”, and click “Next”
     • 3.6  In the “Connection Information” screen, fill the information for the following options:
       • • Data Source: Select the path to your MS Excel spreadsheet.
         • Office Database Type: Select “Excel”
     • 3.7  Then click “Finish” to create the connection.
     • 3.8  Back the “Set Datasource Location” window, select the OLE DB Connection to the MS Excel spreadsheet you just created
       under “Replace with”, and click the button “Update”
     • 3.9  Once the connection is updated, click “Close”
  4. Save the report.Now, the report will refresh successfully in Crystal Reports designer.
  5. Repeat step 3 to 4, for each report that connect to a MS Excel spreadsheet.
  6. If your report is saved and used on the SAP BusinessObjects Business Intelligence Platform server,  the next step will be to install the Microsoft Office 16.0 Access Database Engine OLEDB Provider, on the SAP BI Server.
• Note: The Microsoft Jet Engine 4.0 is deprecated and it is no recommended to be use to connect to MS Excel. Despite this fact, Microsoft is working on a resolution for the Microsoft JET Engine connectivity, and plan to provide it in a future security patch. Note that the recommendation is still to move to Microsoft Access Database Engine OLE DB Provider, because the MS Jet Engine is deprecated. For more information and details, see the Microsoft blog on the Microsoft JET Database Engine connectivity issue
• Alternative options: 
  Microsoft also provided the following options. Those options are not recommended, but can be used at your own risk:

• 1. Uninstall the Microsoft Security Patch ( Not recommended )
  2. Modify the Jet Engine Registry ( Not recommended )
  3. Uninstall the security patch (Not recommended): List of problematic windows udpates.

• • • MS Windows 7: KB4041681
    • MS Windows 8: KB 4041693
    • MS Windows 10: KB4041676
    • MS Windows Server 2012: KB 4041693

1. 1. 1. Go to MS Windows Control Panel, and select “Programs and Features”
      2. Select View Installed Updates.
      3. Find relevant windows updates.
      4. Right-click and select Uninstall.
      5. Restart the machine.

Modify the Jet Engine Registry (Not recommended):

For information on how to modify the Microsoft Registry Key for the MS Jet Engine, see the Microsoft Blog on
the Microsoft JET Database Engine connectivity issue