Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. 

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. 

  
Hi,

I'm trying to upload an Excel file through outsystems, and the file will be stored in a certain folder that will be access by the stored procedure being executed in Outsystems. I already did some configurations in database in terms of granting OSRUNTIME permisssion to execute, still got the same error, the Microsoft.ACE.OLEDB.12.0 already exist as well on Server Objects>Lonked Providers>Providers in Database. I also tried to run the 'select * from openrowset..' and it was able to show data from excel file. I don't know if it's the database has something to do with this or the Outsystems. I already tried to do the ff: suggested from different sites i researched, yet got the same error.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
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




Thanks,


 

 

Hi Diana,

From googling for the error that is also a change needed in the windows registry that may be required: http://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled

But, why do you need to do it at the database level? Doesn't the built-in ExcelToRecordlist do what you need?

Regards,
João Rosado
Hi Joao,

Thanks for your response. It works out fine, I just added the "DisallowAdhocAccess" in registry key for Microsoft.ACE.OLEDB.12.0. We used the ExcelToRecordlist before, but the excel file mostly contains 100k rows that needs to undergo certain process and took a lot of time, which actually returned a 'Request Time-Out', what we did was transforming the logic to a stored proc, which basically do the process triggered in Outsystems, that only took 5-10 secs to process.

Thanks,
Diana