In order to resolve this issue use the following steps:

Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied. You must access this provider through a linked server.

  • Run the Following T-SQL

    USE [master]

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

    GO

     

  • Then  drill down into the registry and make the following change:

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Providers\Microsoft.ACE.OLEDB.12.0

     

  • Switch disallowadhocAccess value to 0


     

  • Rerun the query with the user that was initially denied access and it works!!

    select *

    from openrowset(‘Microsoft.ACE.OLEDB.12.0’,

    ‘Excel 8.0;Database=\\test\all\spreadsheets\YouWant.xlsx’,