“Unexpected error from external database driver (1). (Microsoft JET Database Engine)" after applying October security updates.

项目
10/18/2017

UPDATE: As informed, the security patches with the fix was rolled-out yesterday 11/14. We would request you to test this and verify that the issue is addressed. If not, please let us know. Please find the information below,

Windows 2008

https://support.microsoft.com/en-us/help/4050795/unexpected-error-from-external-database-driver-error-when-you-create-o

Windows 7/2008 R2

-Monthly Roll-up - https://support.microsoft.com/en-us/help/4048957/windows-7-update-kb4048957

-Security-only update - https://support.microsoft.com/en-us/help/4048960/windows-7-update-kb4048960

Windows 2012

-Monthly Roll-up - https://support.microsoft.com/en-us/help/4048959/windows-server-2012-update-kb4048959

-Security-only update - https://support.microsoft.com/en-us/help/4048962/windows-server-2012-update-kb4048962

Windows 8.1 and 2012 R2

-Monthly Roll-up - https://support.microsoft.com/en-us/help/4048958/windows-81-update-kb4048958

-Security-only update - https://support.microsoft.com/en-us/help/4048961/windows-81-update-kb4048961

Windows 10 Fall (“November”) Update, version 1511

-https://support.microsoft.com/en-us/help/4048952

Windows 10 Anniversary Update, version 1607, and Server 2016

-https://support.microsoft.com/en-us/help/4048953

Windows 10 , version 1703

-https://support.microsoft.com/en-us/help/4048954/windows-10-update-kb4048954

We have been seeing a recent influx in cases where the JET provider is no longer able to connect after the October update. This update (released October 10^th, 2017) includes a security update release that inadvertently affects the JET provider. The update was kb4041678 and included in the patch kb4041681. These patches affected the Operating System, which adversely has an issue with the following technologies: Microsoft Windows Search Component, Windows kernel-mode drivers, Microsoft Graphics Component, Internet Explorer, Windows kernel, Windows Wireless Networking, Microsoft JET Database Engine, and the Windows SMB Server. It is important to note that the changes were not to these technologies themselves.

Types of errors witnessed:

  Unexpected error from external database driver (1). (Microsoft JET Database Engine) 

 [Microsoft][Driver ODBC Excel] Reserved error (-5016). 

 [Microsoft][ODBC Excel Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process

WORKAROUNDS & SOLUTION:

Approach 1:

Use Microsoft.ACE.OLEDB.12.0 or Microsoft.ACE.OLEDB.16.0: (Recommended)

The following updates where not intended to cause any issue with Microsoft Jet Database Engine 4.0, at the same time the product group developers were not verifying these updates would be compatible with Microsoft Jet Database Engine 4.0 data provider as it had been deprecated back in 2002:

https://support.microsoft.com/en-us/help/4041678/windows-7-update-kb4041678 https://support.microsoft.com/en-us/help/4041681/windows-7-update-kb4041681

As both articles suggest for the below workaround.

In all current known cases, using the ACE provider works to connect to the excel files in lieu of the JET provider. The following download is the most up to date version for the ACE provider:

Microsoft Access Database Engine 2016 Redistributable https://www.microsoft.com/en-us/download/details.aspx?id=54920

When looking into this issue, the largest thing to note is: The JET provider has been deprecated as of 2002. The last changes were made to this in 2000. See the following article for more details.

Data Access Technologies Road Map https://msdn.microsoft.com/en-us/library/ms810810.aspx

Excerpt:

 “Microsoft Jet Database Engine 4.0: Starting with version 2.6, MDAC no longer contains Jet components. <br>In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC/WDAC releases do not contain Microsoft Jet, the Microsoft Jet OLE DB Provider, the ODBC Desktop Database Drivers, or Jet Data Access Objects (DAO). <br>The Microsoft Jet Database Engine 4.0 components entered a state of functional deprecation and sustained engineering and have not received feature level enhancements since becoming a part of Microsoft Windows in Windows 2000.”

So, in short the JET provider has been working for a good 15 years after deprecation, but this most recent update caused a change which requires an update to how you are connecting to the Excel file. For the SSIS packages, we recommend pointing to the Excel by our Excel connector instead of using OLEDB.

You can locate the Excel Connector by opening up an SSIS package within SQL Server Data Tools. Create or go to an existing Data Flow task. You can see the Excel Source in the “Other Sources” Section:

When using JET, this is done through an OLEDB/ODBC source. You can use the same method for the ACE provider. The ACE provider will work, however it is not supported for use with programs such as SSIS, Management Studio, or other applications. Although this doesn’t tend to cause issues, it is important to note. That notwithstanding, the ACE drives provides the same functionality as the JET provider did. The only limitation I am aware of is the same you were encountering with JET which is that you can't have multiple users connecting to and modifying the Excel file.

Microsoft Access Database Engine 2016 Redistributable

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Excerpt:

 The Access Database Engine 2016 Redistributable is not intended:  
4. To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. <br>Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.

I understand that there are many users that don’t know how many packages might be experiencing this issue. It is possible to look through your dtsx packages using the following method.

If you run a command prompt as an administrator, you can use the find command to look through the packages for the keyword JET. This will state all the files it looks through, but you will see a connection manager result for the files that have this in them. If you have already updated the package from JET to ACE, then this will not show the connection manager and is not affected by this security update.

In a command prompt:

Find /I “Jet” “C:\SampleFolder\SamplePackage.dtsx”

Alternatively, you can search the whole folder at once:

Find /C /I “Jet” “C:\SampleFolder\%.dtsx”

For more information on the Find command:

Find: https://technet.microsoft.com/en-us/library/bb490906.aspx?f=255&MSPPError=-2147217396

Additionally after the installing and upgrading to ACE providers, you may run into the following error message,

 The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine
Or
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

If you are in a scenario where you have both 32-bit and 64-bit processing being performed on the same server that needs to use the ACE data provider, from the Microsoft standpoint, it is recommended to have 2 different servers (1 to process 32-bit mode and the other for 64-bit mode).

But there is an option (workaround) where you can have both the versions installed on the same machine by performing a “/quiet” install of these components from command line. To do so, download the desired AccessDatabaseEngine.exe or AccessDatabaeEngine_x64.exe to your PC, open an administrative command prompt, and provide the installation path and switch /quiet. Ex: C:\Files\AccessDatabaseEngine.exe /quiet. Please note that this approach is not recommended and haven't worked in few instances, so would request you to move to 2 different server approach if possible.

Approach 2:

Uninstall the security patch (Not recommended):

This patch seems to update Excel Jet connector from V4.00.9801.0 to v4.00.9801.1.

One of the workarounds is to uninstall the KB and it may fix the issue. Although, in some instances it may not help to resolve the issue.

Approach 3:

Registry change (Not recommended): [Request you to take a backup of the registry key before making any changes]

Another workaround would be to update the below registry key to point to an old copy of the DLL file:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\Win32]

To get an old copy of the DLL, uninstall the patch KB4041681, copy the DLL "msexcl40.dll" from C:\Windows\SysWOW64\msexcl40.dll to a new location say "C:\\msexcl\\msexcl40.dll".

You can now modify the registry key [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\Win32] to point to the new DLL location "C:\\msexcl\\msexcl40.dll" (by default it’d be pointing to C:\Windows\SysWOW64\msexcl40.dll)

Other workarounds discussed online:

There is a public forum discussion where many customers found various ways to work around this issue.

ODBC Excel Driver Stopped Working with "Unexpected error from external database driver (1). (Microsoft JET Database Engine)" https://social.msdn.microsoft.com/Forums/en-US/2feac7ff-3fbd-4d46-afdc-65341762f753/odbc-excel-driver-stopped-working-with-unexpected-error-from-external-database-driver-1?forum=sqldataaccess

Solution:

The best recommended solution is to move to Microsoft ACE OLE DB provider.

Apart from this, Microsoft is working on a resolution and will provide an update in an upcoming release of the security patch. This is expected to be available in another 2-3 weeks or earlier. (Blog is updated with this information). If this is business critical and you are still encountering any related issues, please reach out to the Microsoft Customer Services & Support(CSS) team.

DISCLAIMER:

THE ABOVE INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

Authors:

Chrone Meade - Support Engineer, SQL Server BI Developer team, Microsoft

Jon Herman - Sr Support Engineer, SQL Server BI Developer team, Microsoft

Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Comments

Anonymous
October 18, 2017
Neither SSMS 17.3 nor SSDT (VS 2015) are using the workarounds and are facing the same problems.sp_addlinkedserver docu still lists Jet.OLEDB.4.0 for Excel. https://docs.microsoft.com/de-de/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql
- Anonymous
  October 19, 2017
  Hi Christoph, Thanks for your comments. We will work internally with the concerned team on getting the sp_addlinkedserver (Transact-SQL) document updated. Please note that this article clearly talks about the usage of Microsoft.ACE.OLEDB.12.0 provider to connect to Microsoft Access & Excel versions 2007 and higher with examples. You may install the ACE OLEDB providers and reconfigure your linked servers.
Anonymous
October 19, 2017
Does approach 1 work if you have Office 64Bit installed?
- Anonymous
  October 20, 2017
  Hi Hermann,Yes, the approach 1 should work for you, please be aware that you may need to get both the 32bit & 64bit versions of Access Database Engine installed, using the /quiet switch as discussed in the above blog.
  - Anonymous
    October 20, 2017
    Our or to be more precise our customer’s experiences were quite different. Observing installation orders you may be able to install a 32Bit Access redistributable and Office 64Bit at first. But subsequent Office 64Bit updates lead to serious problems.
    - Anonymous
      October 20, 2017
      As mentioned, it is recommended to have 2 different servers (1 to process 32-bit mode and the other for 64-bit mode). If this is not a possibility, please reach out to Microsoft CSS team for further help.
Anonymous
October 19, 2017
"For the SSIS packages, we recommend pointing to the Excel by our Excel connector instead of using OLEDB."That is exactly how I do it in SSIS and it no longer works either. If you are targeting an excel file in 97-2003 format, it must use OLE DB behind the scenes and thus "unexpected error from external database driver (1)". There appears to be NO method to access legacy excel data without going to the other more aggressive approaches.
- Anonymous
  October 19, 2017
  Manually editing the .dtsx connection string to use ACE does work even with legacy excel data. Using the excel source recommendation does not work.
  - Anonymous
    October 20, 2017
    Thanks for your comments. The excel connection manager would automatically pick up "Microsoft.Jet.OLEDB.4.0" when targeting legacy Microsoft Excel 97-2003 format, but when using the Microsoft Excel 2007-2010 format it will pick up "Microsoft.ACE.OLEDB.12.0".As you had mentioned, you would need to manually edit connection string to use "Microsoft.ACE.OLEDB.12.0" explicitly for legacy Microsoft Excel 97-2003 formats.
    - Anonymous
      October 23, 2017
      Is there any plan for a release that would correct this behavior? (ie. make the built-in excel connection manager type automatically choose the ACE provider for 97-2003 files instead of defaulting to the deprecated Jet provider)
      - Anonymous
        October 24, 2017
        I dont have this information with me. We will check this internally.
Anonymous
October 19, 2017
The comment has been removed
- Anonymous
  October 20, 2017
  Please get the DLL file "msexcl40.dll" from one of the machines where the patches had not been applied.
  - Anonymous
    November 03, 2017
    It would have been really useful if you had provided that DLL directly on that page, thanks and regards.
Anonymous
October 19, 2017
Hi,I came across the same issue, but when I try the approach 1 by installing the Access Database Engine 64 bit version I get below error"You cannot install the 64 bit version of Microsoft Access Database Engine because you have 32 bit Office Products installed ..." and when I try to install the 32 bit version I get the error "You cannot install the 32 bit version of Microsoft Access Database Engine because you have 64 bit Office Products installed ...". What kind of error messages are being shown? they are opposite to each other irrespective of the version installed it just throws an error.
- Anonymous
  October 19, 2017
  The comment has been removed
- Anonymous
  October 20, 2017
  The comment has been removed
  - Anonymous
    October 21, 2017
    Hi Krishnakumar,What is the different between /passive and /quiet switch?Thanks for your comments.
    - Anonymous
      October 22, 2017
      Hi Robert,Thanks for checking. From what I see,1. /passive switch runs the installer in an unattended mode, but shows the progress bar. 2. /quiet switch runs the installer in a complete silent mode which requires no user interaction. In our case, /passive switch would still throw the version mismatch error message as compared to /quiet switch. HTH!
Anonymous
October 19, 2017
Very disappointing reading this "So, in short the JET provider has been working for a good 15 years after deprecation"So the best you guys can do is wreck it?'m been using Access for 23 years, yes 23! and found it a wonderful product, all thru many versions of Windows.I have databases running for 20 years and now you guys say"Apart from this, Microsoft is working on a resolution and will provide an update in an upcoming release of the security patch. This is expected to be available in another 2-3 weeks or earlier."You should know this has a lot of consequences in the real world of real business apps.The NUMBER ONE selling point of apps built with Access is that it is extremely stable which meansin computer terms that apps can be deployed without managing every update or constantly upgrading to new versions.What do I say to customers that have been using my apps for 10 years or more?I just spent a couple of hours trying Approach 1 and still no export! 2010,2016 Redistributable and no go.Approach 2 does work in my testing.When is the fix coming?
- Anonymous
  October 20, 2017
  Hi Andor, If this is business critical, please reach out to the Microsoft CSS team for further help.
  - Anonymous
    October 20, 2017
    Wow, good answer! "It's not my job"Reaching out to Microsoft CSS team means buy yet another version of Office.I'm not interested in testing yet another version Office for Microsoft.
    - Anonymous
      October 22, 2017
      Reaching out to Microsoft CSS (Customer Services & Support) team, means creating a Microsoft Support ticket with relevant Microsoft team and doesn't mean buying another version of Office.
- Anonymous
  October 20, 2017
  @andor, clearly you do not understand what "deprecated" means. It means "stop using this, it's going to disappear one day". The fact that it has worked for so long is irrelevant. Microsoft owes you nothing.
  - Anonymous
    October 23, 2017
    Hey Gilles, do you work for Microsoft?
- Anonymous
  October 20, 2017
  The comment has been removed
  - Anonymous
    October 22, 2017
    ODBC driver --> Microsoft Excel Driver(.xls) uses ODBCJT32.DLL and ODBC driver --> Microsoft Excel Driver (.xls, *.xlsx, *.xlsm, *.xlsb) uses ACEODBC.DLL. Please make sure that the 32bit ACE providers are installed on the machine. Also if you are still facing any issue, we would request you to connect with the Microsoft Customer Services & Support team for further assistance.
Anonymous
October 22, 2017
Will a patch for this be available for Windows 10 1511?
- Anonymous
  October 22, 2017
  From the information available, this fix once it is available will be applicable across multiple OS'es.
Anonymous
October 23, 2017
1)after selecting the .xls file, if i click on NEXT BUTTON, I'm getting this Exception "Unexpected error from external database driver (1)." 2)If Extension is .xlsx I'm Using @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + ";Extended Properties="Excel 12.0;HDR=YES;IMEX=1;"";If Extension is other than .xlsx(.xls) I'm Using@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";Can U pls help me something.It is Very Urgent for me
- Anonymous
  October 23, 2017
  If you are using the legacy .xls format, please overwrite your connection string to use Microsoft.ACE.OLEDB.12.0. Validate that you have the required 32bit or 64bit version of Microsoft.ACE.OLEDB.12.0 installed.Alternatively, we can reach out to the Microsoft Support team for further help on this.
  - Anonymous
    October 26, 2017
    Hi Krishnakumar, As advised, I am trying to change the Connection String in SSRS from using “Microsoft.Jet.OLEDB.4.0” to “Microsoft.ACE.OLEDB.12.0”. However as I start changing the Connection String in the Connection Manager Properties window, Visual Studio freezes. This has happened multiple times now. Any idea what is causing this and how to resolve it.ThanksManoj
    - Anonymous
      October 26, 2017
      In that case, try to uninstall the entire Microsoft Access Database Engine 2016 Redistributable and install the Microsoft Access Database Engine 2010 Redistributable and use 'Microsoft.ACE.OLEDB.12.0' provider rather than 'Microsoft.ACE.OLEDB.16.0' provider. This has helped me in few instances.
Anonymous
October 23, 2017
I would presume the fix for this would not include one that would make the ACE provider suitable for use within SSIS packages, would it? :) That's the real fix for us; pulling this KB patch fixed our problem precisely as described. We'd really like to rid ourselves of the terrible, awful, very bad JET dependency, but as part of an SSIS package, migrating to the ACE solution it isn't an option. Any suggestions?
- Anonymous
  October 24, 2017
  My suggestion, please move to ACE providers as JET provider was deprecated 15+years back as we dont have any known supported way to connect to Office document.
Anonymous
October 23, 2017
HelloWindows Server 2008This solution works for meFirst changing the old version from dll and then i change the driver Microsoft.ACE.OLEDB.12.0 in the .dtsConfig Thanks!
- Anonymous
  October 24, 2017
  You are welcome! Thanks for letting us know.
Anonymous
October 23, 2017
The comment has been removed
- Anonymous
  October 24, 2017
  Good to know this. Happy to help!
Anonymous
October 24, 2017
We have 3200 dtsx files, 500 are using the ACE driver, 200 are using the JET driver. The following powershell script changes all the JET to ACE. Use in Test first. Path may be changed (120=sql 2014, 130=sql2016, 140=sql2017?) $p="C:\Program Files\Microsoft SQL Server\120\DTS\Packages"$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False$configFiles = Get-ChildItem $p *.dtsx -rec$counter =0foreach ($file in $configFiles){ # Write-Output $file.FullName $c = Get-Content $file.PSPath $c = [Io.File]::ReadAllText($file.FullName) $oldlength = $c.Length $c = $c -replace 'Microsoft.Jet.OLEDB.4.0','Microsoft.ACE.OLEDB.12.0' $c = $c -replace 'EXCEL 8.0;','EXCEL 12.0 XML;' $newlength = $c.Length # Write-Output ( " $oldlength : $newlength " + $file.FullName ) if ($oldlength -ne $newlength) { Write-Output ( "Fixed : " + $file.FullName ) $counter = $counter +1 [IO.File]::WriteAllText($file.FullName, ($c -join "rn"),$Utf8NoBomEncoding) } else { # Write-Output ( "Skipped : " + $file.FullName ) } }Write-Output "Number of files fixed : $counter"
- Anonymous
  October 24, 2017
  Thanks for sharing this Constantijn!
- Anonymous
  November 01, 2017
  This script worked great for us!I removed the $c = $c -replace ‘EXCEL 8.0;’,’EXCEL 12.0 XML;’ portion though, since using version 12 made the files unreadable, and I had to install the Microsoft Access Database engine on all servers in the cluster, but it worked!Thanks!
Anonymous
October 24, 2017
Hello sir, I am facing same issue "Unexpected error from external database driver (1)" and try to replace code withProvider=Microsoft.ACE.OLEDB.12.0; and uploaded on godaddy serverstill on server error is showing " The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." How can i install on godaddy server
- Anonymous
  October 24, 2017
  Please make sure that the correct version (32bit or 64bit) of the ACE OLE DB provider is installed on the machine where the SSIS packages are executed. Not really sure on how you can get this on a GoDaddy server, please reach out to the relevant team to address this.
  - Anonymous
    November 02, 2017
    The comment has been removed
Anonymous
October 24, 2017
For me, it worked following these steps:ps.: We have a Windows Server 2008R21 - Download and install this: https://www.microsoft.com/en-us/download/details.aspx?id=237342 - Open the .dtsx file and change your Excel connection. On the properties dialog, Click on the three dots in the ConnectionString property and change it to Microsoft Excel 2007. This will automatically change your connection string to:Provider=Microsoft.ACE.OLEDB.12.0;Data Source={YOURPATH};Extended Properties="Excel 12.0 XML;HDR=YES";3 - Some of our .dtsx files point to a config file (usually has a .dtsConfig extension). I also changed these guys to point to the right provider (pretty much copied and pasted the connection string I got from visual studio)After this, I ran the SQL Job again and it worked fine.
- Anonymous
  October 24, 2017
  Thanks for sharing this Michael.
Anonymous
October 26, 2017
Since this update I can't import information from Excel into Atril Déjà Vu X3 (translation software; CAT-tool) anymore. I need this for my work as a translator. Atril is now working on DVX4 which will hopefully solve this problem, but of course the new version of DV will not be free. I hope Microsoft can change something in order to solve this problem earlier.
- Anonymous
  October 26, 2017
  Please reach out to the Microsoft Customer Services & Support team, if this is critical.
Anonymous
October 26, 2017
I have a legacy third party application that is encountering this issue. However, we do not have access to the source code of this application and we need to correct this as soon as possible to send reports to our client. Is there a work around that does not involve updating the affected application? Has it been confirmed that Microsoft will release a fix to this error without the end users updating legacy applications?Additionally, security updates are managed by our parent company and we do not have the proper administrative rights to uninstall the update or delay its installation. Any help or information would be greatly appreciated.
- Anonymous
  October 26, 2017
  Unfortunately, based your description, it looks like you have only 1 option as a workaround, which is to use Approach 3: Registry Changes.Since both upgrading, uninstalling of the patch is not an option for you and you dont have any control on shipment of the fix once it is available.
Anonymous
October 27, 2017
The comment has been removed
- Anonymous
  November 01, 2017
  Hi Avoriv,Can you please let us know the exact error message that you are receiving? Need more information for offering further help here. Also as an alternative, can you please try to uninstall the entire Microsoft Access Database Engine 2016 Redistributable and install the Microsoft Access Database Engine 2010 Redistributable and check the behaviour. Also, please note that Access 97 remains unsupported refer https://support.microsoft.com/en-us/lifecycle/search/915.
  - Anonymous
    November 01, 2017
    Hi, Krishnakumar Rukmangathan, I do not use Microsoft Access 97.Can I import/export Access 97 format file?Can I import/export Access 2002 - 2003 format file?
    - Anonymous
      November 02, 2017
      Hi Krishnakumar Rukmangathan, I am not use Microsoft Access 97. Can I import/export Access 97 format file? Can I import/export Access 2002 – 2003 format file?
Anonymous
October 27, 2017
We are looking at changing our systems to use the Microsoft ACE OLE DB provider but this is going to take time to roll out. We have advised our clients not to apply the security patches for now which is far from ideal and changing the registry is not an option for us. Can you advise when the fix is going to be available? This is a critical issue for us so if you can't tell me when the fix will be out can you provide the contact details for the best team to speak to please?
- Anonymous
  November 01, 2017
  Please reach out to Microsoft Customer Support & Services team and open a support ticket. The support ticket would routed to the right team and they would be able to help you on this
  - Anonymous
    November 01, 2017
    Hi,I've tried this twice now and both times I've been directed to a website of phone numbers and no ticket has been raised. Just now I was told the engineers were working on it but I couldn't get confirmation of when the patch would be out. I don't understand why this has to be logged as a separate support ticket and why I have to try and figure out which team to contact? Surely if the fix is being worked on then you know which team this is and they can give us an ETA for the fix?
    - Anonymous
      November 02, 2017
      Article is updated with the expected release fix date.
Anonymous
October 28, 2017
Hi, we have windows server 2008 R2 (32 bit). we have changed connection string also > Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strfilepath + "; Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";and installed "Microsoft Access Database Engine 2010 Redistributable", but still I am getting same error. please suggest.
- Anonymous
  October 30, 2017
  Hi all, Hi Microsoft guys,any news about the upcoming release of the patch?
- Anonymous
  November 01, 2017
  Hi Anurag,Are you receiving the error Error 1: "Unexpected error from external database driver (1). (Microsoft JET Database Engine)" (or) Error 2: "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine". Can you please let us know. If you are getting the Error 1: Then you aren't editing the right connection string. Please validate all the other connection strings used in your application.
Anonymous
October 30, 2017
I am using Provider as System.Data.SqlClient still I am getting error as "Unexpected error from external database driver (1)." when trying to import excel file to sql server 2008 Database. Can you please suggest ??
- Anonymous
  November 01, 2017
  Hi Harindra, System.Data.SqlClient can only be used to connect to SQL Server, not to Excel / Access file. Please check your application for a specific connection string that is used to connect to Excel or Access and modify that.
Anonymous
October 30, 2017
Hi Krishnakumar,Thanks for the workaround. This was helpful in solving the issue temporarily. But this is critical issue for us as we are using JET engine in our windows desktop application which is widely installed and used by users. Changing the connection, generating the new installer file,rolling out the new version and asking the users to uninstall the previous versions is risky and causes huge customer impact. Could you please update the details when the fix for this security patch will be available if you are aware of it. POC to contact Microsoft CSS team . Details of Incident or VSO Work item being created for this issue if any?
- Anonymous
  November 01, 2017
  Thanks for comment Noorjahan. Once the support ticket is lodged, the case would be routed to right team to provide you the help.
Anonymous
October 30, 2017
Our application deployed in IIS machine 64 bit OS and Microsoft Access Database Engine 2010 is already installed. Excel upload was working before the Security Update for Microsoft Windows (KB 4041681,KB4041678,KB4040685) was installed. Now uploading file shows error :"file you try to upload is not a valid file". Changed excel connection string in C# to Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'" as in approach 1 and Tried different types of excel files 97-2003 (.xls),.xlsx but noluck. We are not supposed to uninstall the security update. Please provide the resolution. It's been critical issue.
Anonymous
October 30, 2017
The comment has been removed
Anonymous
October 30, 2017
What about "SQL Server 2014 Import and Export Data". Should I always convert the excel file I use to load to a DB to .mdb because of this?
Anonymous
October 30, 2017
The SSMS Import Wizard appears to use the Jet driver as I am now getting this error through SSMS when trying to import a spreadsheet. When will a fix be available?
- Anonymous
  November 01, 2017
  Whenever you are connecting to Legacy "Microsoft Excel 97-2003", (which is outside the support as per https://support.microsoft.com/en-us/lifecycle/search?alpha=Excel ) the Import Export Wizard (I/E wizard) picks up "Microsoft.Jet.OLEDB.4.0" but if you have already installed the Microsoft Access Database Engine and you are connecting to "Microsoft Excel 2007-2010", it uses the Microsoft.ACE.OLEDB.12.0 by default. So I/E wizard works as expected for supported versions of Excel or Access files.
Anonymous
October 31, 2017
Thanks very much, this was very handy! I would counsel users to ensure that they make the change in connection setting from: 'Microsoft.Jet.OLEDB.4.0;Datasource=....' to 'Microsoft.ACE.OLEDB.16.0;Datasource=...'.
- Anonymous
  November 01, 2017
  You are welcome Victor. This action item is mentioned in this blog as well.
Anonymous
October 31, 2017
Hi,We are having this issue when using the Import / Export wizard in SSMS 2017. What is the correct fix for that?If the JET code is deprecated for 15 years why does the wizard still use it?Regards,Paul
- Anonymous
  November 01, 2017
  Whenever you are connecting to Legacy “Microsoft Excel 97-2003”, (which is outside the support as per https://support.microsoft.com/en-us/lifecycle/search?alpha=Excel ) the Import Export Wizard (I/E wizard) picks up “Microsoft.Jet.OLEDB.4.0” but if you have already installed the Microsoft Access Database Engine and you are connecting to “Microsoft Excel 2007-2010”, it uses the Microsoft.ACE.OLEDB.12.0 by default. So I/E wizard works as expected for supported versions of Excel or Access files.
Anonymous
October 31, 2017
Any update on when the fix is coming for this? Waiting for this desperately!
- Anonymous
  November 02, 2017
  I just received a message from support : " Je viens d’être informé qu’entre cet après-midi et demain matin (au plus tard car les mises à jour sont lancées depuis les États-Unis), cette nouvelle mise à jour sera lancée pour que tous les utilisateurs puissent l’installer. "Which means, in English, that the fix should start beeing deployed today and tomorrow :)
  - Anonymous
    November 02, 2017
    The article is updated with the information of the expected release fix.
Anonymous
November 01, 2017
Will this be fixed with some updates?Some of us don't want to take the risk and modify files in productions environments.
- Anonymous
  November 02, 2017
  Article is updated with the expected release fix date.
Anonymous
November 01, 2017
In my case, I am working on a client-side application. I have to assume that users could have Office installed as either 32-bit or 64-bit. Additionally, my application is 64-bit, but I have to assume that users could have 32-bit applications using the 32-bit version of Access Database Engine.So, I am testing the install of both the 32-bit and 64-bit versions of Access Database Engine on the same machine. I am using the "/quiet" switch, and the installations are successful. However, only the last installed Access Database Engine really works. In my case, I installed the 32-bit version first and 64-bit version last. So, if I build my C# test .exe for x64, it works well. But if I build it for x86, then in the call to OleDbConnection.Open(), I get an exception: An unhandled exception of type 'System.Runtime.InteropServices.SEHException' occurred in System.Data.dll. The results flip if I install the 32-bit version of Access Database Engine last. In that case, my x86 test .exe is successful, but the x64 test gets the exception.Any advice to solve the SEHException? Although mentioned in this article as an option, is installing both using "/quiet" actually supported?
- Anonymous
  November 01, 2017
  Installing both the 32bit & 64bit Access Database Engine is not supported. It was mentioned in the article just as a workaround if you cannot have 2 different servers for 32bit & 64bit Office processing. Also your requirement doesn't seem to be realistic, running your test.exe on both the 64bit & 32bit on a same machine. We understand that your clients may have either of Office suite installed (x86 or x64), so you may need to just target your test.exe for one bitness and for clients who aren't having the same bitness of provider installed, they need install the same using the switch.
  - Anonymous
    November 02, 2017
    Ok, so installing both using "/quiet" is mentioned as a workaround, but not actually supported by Microsoft, and from my testing, does not actually work. So should you edit the article to remove mention of this as a workaround and say that you may only have one installed on the machine and may do so using the "/quiet" switch?Also, my understanding then is that if my application will require the x64 provider, then from Microsoft's standpoint any application that requires the x86 provider must run on a different machine. That is the only supported configuration, correct?
    - Anonymous
      November 02, 2017
      Firstly, this approach has worked for us internally. But we had modified the article accordingly. Yes, is recommended to have 2 different servers of x86 & x64 processing.
      - Anonymous
        November 03, 2017
        If I have a 32-bit application and the 64-bit Office 2010 is installed, the 32-bit versions of Access Database Engine 2010 doesn't work but the Access Database Engine 2007 works.
Anonymous
November 01, 2017
I'm going to change to use ACE instead of JET as suggested in approach 1, but I 'm not sure about how the Microsoft Access Database Engine 2016 work. Do all clients PC need to install Microsoft Access Database Engine 2016 or only the the dev machine which use to build the app?
- Anonymous
  November 01, 2017
  Yes, all your client machines that runs the applications which initiate the Excel or Access connection request needs Access Database Engine 2016 to be installed.
  - Anonymous
    November 01, 2017
    The comment has been removed
    - Anonymous
      November 01, 2017
      The comment has been removed
Anonymous
November 02, 2017
Hello,As the kb4041681 is being deployed the bug it comes with is causing/will cause critical issues for our application and our users. We are being very cagey about changing the provider because we are not sure of all the underlying impacts it could have. When can we expect a fix from you side ?
- Anonymous
  November 02, 2017
  Sorry, just saw the fix is expected to be released in 2-3 weeks max. That's a long time though.
  - Anonymous
    November 02, 2017
    Article is modified with the expected release fix date. Please refer that.
    - Anonymous
      November 03, 2017
      We applied the registry change approach and it work, thanks. We really hope that until the fix is released there will be no background process that change the registry back to the newest DLL.
      - Anonymous
        November 03, 2017
        Thanks for your comments. Happy to help!Hopefully, there shouldn't be any.
Anonymous
November 03, 2017
Excel export problem SOLVED: KB4052234 fixes it and I have tested it.
- Anonymous
  November 03, 2017
  No other changes are necessary.
  - Anonymous
    November 03, 2017
    Microsoft stopped to release KB4052234, KB4052233 and Windows 10 updateshttps://www.ghacks.net/2017/11/03/microsoft-releases-kb4052234-kb4052233-and-windows-10-updates/
    - Anonymous
      November 03, 2017
      Solution will also be available in the November 14, 2017
      - Anonymous
        November 03, 2017
        Bug fixed in msexcl.dll (Version: 4.0.9801.2, Date Modified: 10/17/2017)
      - Anonymous
        November 04, 2017
        I tested new msexcl40.dll. It works in Win10 v1709.
    - Anonymous
      November 04, 2017
      The Extended Support End Date of Microsoft Access 97 Standard Edition is Not Applicable.The Extended Support End Date of Microsoft Access 2007 is Not Applicable.The Extended Support End Date of Microsoft Office Excel 2007 is Not Applicable.The Extended Support End Date of Microsoft Office Excel 2003 is 4/8/2014.Microsoft JET Database Engine is deprecated.Microsoft fixed bug causes applications based on the Microsoft JET Database Engine (Microsoft Access 2007 and older or non-Microsoft applications) to fail when creating or opening Microsoft Excel .xls files, but cannot fix bug causes applications based on the Microsoft ACE Database Engine 2013/2016 to fail when opening Microsoft Access 97 .mdb files (that works with ACE 2007/2010). Why?
    - Anonymous
      November 04, 2017
      Over the years, many applications have used Microsoft Access 97 files to store data. When the JET Engine stops working, Microsoft will laugh as it is now, with XLS files.
- Anonymous
  November 04, 2017
  It was too fast. Usually, it takes 2-3 months.
- Anonymous
  November 06, 2017
  Unfortunately Micrsosoft removed the KB4052234 again, it cannot be found in the update catalog anymore?Most of the above mentioned KBs have been reverted again.
  - Anonymous
    November 06, 2017
    The download should be available by Nov 14th or before.
- Anonymous
  November 06, 2017
  This KB4052234 is a Windows 10 update. Doesn't seem to be one for Windows 7 - 64 bit.Tried the Reg fix, but it didn't work for me. MS Access appears to want to re-install and then nothing. Still getting the Run-time error 3275. My computer is part of a group policy, so even though I can un-install the update, it gets re-installed the next day. Very time consuming. Need to faster fix as my IT department is not able to help with the policies.
  - Anonymous
    November 06, 2017
    Tried the Reg fix again but this time used the Win32 old at this location and is working now.[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\Win32old]Did not have to uninstall the update. I suppose I will have to modify the registry back to the new dll once Microsoft comes up with a Windows 7 fix. But for now, pointing to the old dll is working.Thanks to all for helping...
    - Anonymous
      November 06, 2017
      Thanks for adding this comment. Happy to help!We would recommend you to bring the buggy dll, before applying any fixes (once it is available)
- Anonymous
  November 06, 2017
  The update is not available in any the below links. Where did you download it from ?https://www.catalog.update.microsoft.com/Search.aspx?q=KB4052234https://support.microsoft.com/en-us/help/4052234
  - Anonymous
    November 06, 2017
    The download had been removed internally, this should be out by Nov 14th
Anonymous
November 06, 2017
Looks like the Windows Server 2008 R2 fix link doesn't work.
- Anonymous
  November 06, 2017
  The download should be available by Nov 14th or before.
Anonymous
November 06, 2017
Hi, I see from the note above that the issue has been resolved, but when I go to the link for Windows 7/2008 R2 - https://support.microsoft.com/en-us/help/4052234/windows-7-update-kb4052234 the page does not show. Can you please provide a functional link?
- Anonymous
  November 06, 2017
  The download should be available by Nov 14th or before.
Anonymous
November 06, 2017
Update only released for old windows 10 builds? No fix for Windows 10 build 1709?
- Anonymous
  November 06, 2017
  The download will be available by Nov 14th
  - Anonymous
    November 13, 2017
    Should...
Anonymous
November 06, 2017
The KB4052234 fix is no longer available (?)
- Anonymous
  November 06, 2017
  The download had been removed internally, this should be out by Nov 14th. Blog is updated with this information
  - Anonymous
    November 07, 2017
    Thank you!
Anonymous
November 06, 2017
Hi,Links to download security patches are broken, unable to download/find KB4052234. Please FIX. Thanks.Windows 7/2008 R2 - https://support.microsoft.com/en-us/help/4052234/windows-7-update-kb4052234Windows 2012 - https://support.microsoft.com/en-us/help/4052235/windows-server-2012-update-kb4052235Windows 8.1 and 2012 R2 - https://support.microsoft.com/en-us/help/4052233/windows-81-update-kb4052233
- Anonymous
  November 06, 2017
  The download had been removed internally, this should be out by Nov 14th. Blog is updated with this information
  - Anonymous
    November 14, 2017
    When is the fix coming? let me know
    - Anonymous
      November 14, 2017
      Blog is updated with the links that was released on Nov 14th. Please refer that.
Anonymous
November 06, 2017
Hi,Above link for kb4052234 does NOT work. Can you please provide the updated link to download the security patch?Thanks.
- Anonymous
  November 06, 2017
  The download had been removed internally, this should be out by Nov 14th. Blog is updated with this information
  - Anonymous
    November 14, 2017
    Any new on the update that should come out today?Thanks in advance
    - Anonymous
      November 14, 2017
      Yes, check the blog for updated links
  - Anonymous
    November 14, 2017
    Hi Krishna,Do you have any update on the fix availability for Windows 2008 R2 version? Please let me know.
    - Anonymous
      November 14, 2017
      The comment has been removed
Anonymous
November 13, 2017
Hi,I have a .net 1.1 Framework program, that uses jet 4.0 Access database engine. When I want to delete then last record from datatable, de dataset.haschanges method gets false, so I can't delete the last record of recordset. When I installed an erlier version of program from backup (2017.02), it Works perfectly, but when I rebuild the old version, it doesn't work also. Has anyone any idea?Thanks a lot.
- Anonymous
  November 13, 2017
  I have found the workaround.
  - Anonymous
    November 13, 2017
    The problem is solved.
Anonymous
November 13, 2017
KB4041693 seems to apply here too. I'm using SQL Server 2012 (11.0.5058) on Windows Server 2012 R2 Standard. Our import from Excel to SQL table failed on our regular process, for the first time, after an 11/4/2017 Windows Update with KB4041693. This KB mentions the JET Driver.
Anonymous
November 13, 2017
It is now the 14th here in Sydney Australia, I presume we have to wait till the 14th, US time.My requirement is for using Visual Studio 2015 with SQL Server 2016 and SSIS 2016 to integrate Excel files.The problem is that SSIS integrations don't work with Excel 2016 so you are forced to use the Excel 97-2003 ConnectorSo I disagree with the comment that the Jet Provider is deprecated considering the Excel 97-2003 connector is conditioned to use the JET Provider.
- Anonymous
  November 14, 2017
  Blog is updated with the links that was released on Nov 14th. Please refer that. Using the ACE OLE DB provider you should be able to connect to the Excel 2016. If not, please let us know the error message that you are receiving.
Anonymous
November 14, 2017
Can someone confirm if the fix for this issue is available as its Nov 14th today.
- Anonymous
  November 14, 2017
  Yes, check the blog for updated links
Anonymous
November 14, 2017
I need help. Here I'm Talking to an MSFT guy but not getting anywhere. please help.https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8c2a2b02-93e8-4184-9087-450d45dc246a/ssis-package-wont-run-on-server-but-it-will-in-ssdt?forum=sqlintegrationservices
- Anonymous
  November 14, 2017
  This issue that you had discussed is different from the one addressed in this blog. Please continue working with the MSFT engineer to get this addressed. I would probably use Process monitor trace reproducing the issue and check if the account under which the SSIS package is executed, does have the permission to the xls path and always use UNC (Universal Naming convention) rather than the local path. Creating a proxy account for the working account could be tried as well.
  - Anonymous
    November 17, 2017
    Ok thanks
Anonymous
November 14, 2017
Looks like the new KBs to address these issues are out:https://support.microsoft.com/en-us/help/4048957/windows-7-update-kb4048957https://support.microsoft.com/en-us/help/4048954
- Anonymous
  November 14, 2017
  Thanks, the blog is updated with this information.
Anonymous
November 14, 2017
Is it November 14th yet? Bwahahaha.
- Anonymous
  November 14, 2017
  Please refer the update added onto this blog. The fix was release on Nov 14th
Anonymous
November 14, 2017
This is one of the solution found after through investigationSolutions to "Unexpected error from external database driver (1)"Steps:First search for Installed Updates " windows-7-update-kb4041681" in control panel . Uninstall this update from your OSFind previous version of msexcl40.dll.(The previous msexcl40.dll can be identified by date of creation).you can search under c:\windowsCreate a directory lets say C:\xyz and copy the previous msexcl40.dll to this directory . It suggested to be the application directory, but since in the next step you will modify registry to point to this older version, it can probably go anywhere.So here it does not matter type of directory.Update registry key HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\win32 to point to the location from step 2.i.e C:\xyz\msexcl40.dll
- Anonymous
  November 14, 2017
  Thanks. This is documented under the workaround 2 & 3 in this blog.
Anonymous
November 15, 2017
This morning quite a number of updates (W10), all installed successfully, yet the problem is still not solved with the DB engine.
- Anonymous
  November 28, 2017
  Check the updates provided in this blog specifically
Anonymous
November 15, 2017
Hello,Is there a 32-bit version for security update KB4048960?Thank you
- Anonymous
  November 21, 2017
  WSUS is listing KB4050795 as the replacment for KB4042007, which was the Oct 2008 32bit.https://support.microsoft.com/en-gb/help/4050795/unexpected-error-from-external-database-driver-error-when-you-create-o
Anonymous
November 17, 2017
Do we need to reinstall the old update if we uninstalled it? Or does the new fix include everything?
Anonymous
November 21, 2017
tanks
Anonymous
November 21, 2017
After installing the 2012 R2 patches. there is still problem with the Import of Excel not while exporting. Hence the issue still exists. Please suggest a solution on this
- Anonymous
  November 28, 2017
  The patch seems to have resolved the issue. Can you please recheck if it is properly applied.
Anonymous
November 22, 2017
The comment has been removed
- Anonymous
  November 28, 2017
  @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + MyPath + “;Extended Properties=\”Excel 12.0;\”” --> The extended properties doesn't look to be right here, remove the ""'s. Check the syntax, you may use a UDL file to get the exact connection string to use. Typical connection string would look like, Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\MyExcel.xlsx";Extended Properties=Excel 12.0;
Anonymous
December 01, 2017
The comment has been removed
- Anonymous
  December 19, 2017
  @Martin OKindly share if you were able to fix the issue. I was about to try installing KB4048961. But after seeing your message I am not sure if I should do that.
- Anonymous
  December 20, 2017
  @Martin OWere you able to resolve the issue please update us.
Anonymous
December 04, 2017
https://www.microsoft.com/en-us/download/details.aspx?id=54920 is a broken link. Where can I get the ACE driver?
- Anonymous
  December 06, 2017
  Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920Microsoft Access Database Engine 2010 Redistributable: https://www.microsoft.com/en-in/download/details.aspx?id=13255These links seems to work.
Anonymous
October 24, 2018
I had a large Excel file (79k rows, 50mb) linked to Access database and Access has started throwing this error from last few days (After October 12, 2018). A temporary workaround I figured out is to open the linked Excel file in Excel and then try to open the file in Access. This way I am able to run analytical queries in Access and update temporary tables for further use.

通过

“Unexpected error from external database driver (1). (Microsoft JET Database Engine)" after applying October security updates.

Comments

其他资源