This is most well known error when we are trying to access excel from SQL Server. To fix this issue, we need to follow below steps.
- Install Microsoft Access Database Engine 2010 Redistributable based on 32 bit or 64 bit version of Windows from below link.
- Registry changes. Navigate to <HKLM>\Software\Microsoft\Microsoft SQL Server\Providers\Microsoft.Jet.OLEDB.4.0 and Create a DWORD named DisallowAdhocAccess with the value 0. Just to check if you are in the right place there will be another DWORD entry under the name of AllowInProcess, which should be set to 1 already.
- Restart agent and server services
- Enable OLEDB Driver in SQL Server
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;