SQL Server linked server to .xlsx Excel files

A question came up recently concerning whether it was still possible to connect to .xlsx Excel files as  SQL Server linked servers.

Short answer: Yes, it's possible as recently as SQL 2019 using ACE.OLEDB.12.0 provider. 

Medium answer: Yes, but linked servers from SQL Server to Access and Excel are only supported when using the deprecated 32-bit Microsoft.JET.OLEDB.4.0 OLE DB provider. 

Long answer: Yes, but this shouldn't be part of any automated ETL process, please please engineer around using Excel file interchange in any automated data ingestion processes. That said... 

There's lots of content on the internet about linked servers dependent on old .xls files, I won't recreate that here. That said, .xlsx files (using the latest Office 365) and the Microsoft Access Database Engine 2016 Redistributable drivers (ACE12) do also work. They just aren't supported by Microsoft. Linked servers with Access and Excel are only supported when using the 32-bit Microsoft.Jet.OLEDB.4.0 provider (even though this is deprecated).

Here's the proof:

Sample code:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'BOOK112', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2K19\MSSQL\DATA\Book1.xlsx', @provstr=N'Excel 12.0;IMEX=1;HDR=YES;'
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;   
GO
    
    

Note a few things in my scenario above:

1. This requires the two system-wide OLEDB settings above ('DynamicParameters' and 'AllowInProcess') to be enabled.
2. I put the Excel file in C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2K19\MSSQL\DATA\ just to rule out local permissions issues. You can place it anywhere. Of course, granting permissions to the SQL Server service account - whatever you have the service it configured to run as - to the Excel file is required.
3. This uses the Microsoft Office 12.0 Access Database Engine OLE DB Provider. I couldn't get this to work with the Microsoft Office 16.0 Access Database Engine OLE DB Provider, but I didn't try too hard. 

This gets tricky because Excel can easily be locked by other processes. If you have the sheet open in Excel, SQL Server won't be able to query it from the linked server. Kill processes that could be holding a lock on the Excel file, including Microsoft Excel itself. When that fails and the file is still locked, I also used the sysinternals Process Explorer to kill a dllhost.exe that still had a handle on the file, as pictured below. Killing that process in Process Explorer cleared up the problem:

Again, because of these file locking issues - SQL may be blocked by any other user, any process local to the server - using Excel files in an automated ETL/ELT system is just asking for trouble. Super not recommended. There are myriad better ways to ingest data rather than using Excel, including bypassing file locking and driver issues by exporting Excel files to .csv, or preferably going to data source directly and bypassing Excel as a medium. Getting data directly from the source system via direct database connection is highly preferable. If the data is hand-edited or hand-entered, consider SharePoint Online or PowerApps for the data entry instead of offline Excel files. Also, before you rule out some old/legacy database provider, consider PolyBase using generic ODBC for specific platform drivers or even generic ODBC drivers to pull data from a wide variety of data sources. 

All that said, the ACE driver is "not intended... 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." This includes linked servers from SQL Server, meaning that this is not the intended use of the Access Database Engine. So again, the solution stated in this blog post is not supported by Microsoft. I'll update if that changes.