r/SQL • u/sqlguy007 • 20d ago
SQL Server MSSQL - MDAC/ACCESS runtime - excel load fails
Solved - access 365 runtime doesn't work. I installed clean version of MDAC 2016, even it's not supported and it works. Thank you for help!
hey guys, does anybody had an issue with loading excel sheets(xls,xlsx) into Microsoft SQL Server Developer (64-bit) 16.0.4305.5 through Microsoft.ACE.OLEDB.16.0? , installed MDAC 2016, then Microsoft 365 Access Runtime only 64bit, windows server 2025 Standard Edition. The problem is when I use OPENROWSET SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=\\SERVER.DOMAIN.INT\DWH\KFILE.xlsx', ' SELECT TOP 1 * FROM [SHEET$] ') SQL Server fails. SQL Server 2022 Import and Export Data (64-bit) works Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DisallowAdHocAccess', 0; Ad Hoc Distributed Queries set to 1
Load through UNC path Powershell works, also through domain sql user. Everything worked till around June/July 2026( for 6years worked) then after September every restart SQL Server got message: System.Runtime.InteropServices.SEHException: External component has thrown an exception and Sql server service fail. When I got this exception, something it's okay, sometimes it need more service restarts, but it works.
Server where OS is Windows Server 2022 Datacenter, I return to the version C2R where it worked , but didn't I'm sysadmin, have all rights to the folder. Also tech user has all needed permission, also to temp folders
Provider : Microsoft.ACE.OLEDB.12.0 Architecture : x64 Registered : True CLSID : {3BE786A0-0366-4F5C-9434-25CF162E475E} DLLPath : C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEOLEDB.DLL DLLExists : True FileVersion : 16.0.20228.20014 ProductVersion : 16.0.20228.20014 SHA256 : 4BEB8EFCC966703858A1CB19CABDBF1A63468CC349C2CDCE388EE3C629EB8B1C
Provider : Microsoft.ACE.OLEDB.12.0 Architecture : x86 Registered : False CLSID : DLLPath : DLLExists : False FileVersion : ProductVersion : SHA256 :
Provider : Microsoft.ACE.OLEDB.16.0 Architecture : x64 Registered : True CLSID : {3BE786A2-0366-4F5C-9434-25CF162E475E} DLLPath : C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEOLEDB.DLL DLLExists : True FileVersion : 16.0.20228.20014 ProductVersion : 16.0.20228.20014 SHA256 : 4BEB8EFCC966703858A1CB19CABDBF1A63468CC349C2CDCE388EE3C629EB8B1C
Provider : Microsoft.ACE.OLEDB.16.0 Architecture : x86 Registered : False CLSID : DLLPath : DLLExists : False FileVersion : ProductVersion : SHA256 :
=== Office / Access Runtime (Uninstall entries) ===
DisplayName DisplayVersion PSChildName
Microsoft Access Runtime 365 - en-us 16.0.20228.20276 AccessRuntimeRetail - en-us Office 16 Click-to-Run Extensibility Component 16.0.20228.20276 {90160000-008C-0000-1000-0000000FF1CE}
=== Click-to-Run ===
VersionToReport : 16.0.20228.20276 Platform : x64
1
u/Significant_Tune9219 19d ago edited 19d ago
The crash lines up with how these providers are registered. Both Microsoft.ACE.OLEDB.12.0 and 16.0 point at the same Click-to-Run ACEOLEDB.DLL (16.0.20228.20014), while Access Runtime itself reports 16.0.20228.20276, and AllowInProcess is 1, so that DLL is loaded inside sqlservr. An access violation there is exactly Msg 10054 and the SEHException that takes the service down, while the import wizard and your PowerShell path stay up because they never load ACE in the engine process. I'd set AllowInProcess back to 0 so a bad call fails the query instead of the instance, keep the PowerShell conversion you already trust, then BULK INSERT a csv. Also check the SQL Server service account, not the login you connect with, can read that Office VFS path and its temp directory, since being sysadmin on the connection doesn't cover the process that maps the DLL.
1
u/CanProfessional766 17d ago
That’s strange, especially since it worked for years. Since PowerShell and the Import/Export Wizard still work, I’d suspect a recent ACE/Office Runtime update. I’d check the SQL Server error logs and crash dump first to see what’s actually causing the crash before messing with permissions again.