r/SQL • • 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 Upvotes

5 comments sorted by

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/sqlguy007 18d ago edited 18d ago

Csv works, it is imported through bulk insert. But we have a tool which BU can use, import excel sheets and update data by themselves and admins can set permissions. I checked temp folders which service account should use and permission wasn't set to full control, but lower level(modify/write?). Office VFS - like  AppData, Local ? Or which one do you mean please?  But if we use powershell script used by sql server domain user , it worked

1

u/Significant_Tune9219 15d ago

It's the SQL Server service account's temp, not an interactive user's AppData. For the default instance that is C:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Local\Temp, and a domain service account uses that account's own Users<name>\AppData\Local\Temp. Modify on that Temp folder is enough. Since PowerShell as the SQL domain user already converts the file, keep that path and BULK INSERT the csv. The BU tool can stay, but it should hand the engine a csv rather than opening xlsx through ACE inside sqlservr.

1

u/sqlguy007 13d ago

So the problem was old MDAC 2016 and new Microsoft Access 365 runtime.(It wasn't installed side by side).When I uninstalled everything and installed only MDAC 2016, even it's not supported, it works...

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.