There’s been a resurgence of people pointing out dumps occurring in SQL Server when using linked servers with the ACE drivers. It’s been on the MCM email list, forums, SQL Server Feedback site, everywhere, and it’s basically the same response every time… ACE drivers were not made to be used as linked server drivers in SQL Server. If they happen to work for you, cool, but it’s not supported and if it breaks then you have no paddle.
If you look at the ACE Driver download page there are a few things that are plainly obvious and some that I wish were **more** obvious. Let’s take the more obvious one first, in the middle of the page is a restrictions list (emphasis mine), “The Access Database Engine 2016 Redistributable is not intended“. Right there, the things below this are not the intended use for this product. What’s in that list? Let’s look at (currently) number 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. 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.
All of those are or can be SQL Server. I thought that was pretty obvious but I’m not normal.
If we continue to the section that no one ever reads, “Additional Information”, we’re greeted with the following (emphasis mine).
The Office System Drivers are only supported under certain scenarios, including:
1. Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
2. To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
It’s supported if you SSIS or similar applications as the application which utilizes the driver. The second part of item #2 in the above list proves the further point of the first section that is visible on the page about not intended usage which states it can’t be run as a system account and needs to be a logged in user with a valid registry hive.
There are so many ways SQL Server doesn’t meet the restrictions on use or intended usage scenarios and yet everyone seems genuinely shocked that it can crash SQL Server. Stop using the ACE drivers directly in SQL Server as linked servers. Write a small app, use SSIS, or, you know, keep using it as a linked server but just don’t complain and call it a bug when it doesn’t work.