none
ASP.NET page failing to execute stored procedure with distributed queries (openrowset)

    Question

  • Hello,

    I have a stored procedure that execute several queries to import and export data using openrowset. The procedure works ok on SSMS, but fails when executed by a web page. The difference is the executing user.

    On SSMS logged in as Administrator (same as SQL Service account) works ok. On the page we connect with a user with less rights, but enough to select, delete, update, execute procedures, etc.

    If the page connects to SQL as Administrator all works ok, but it opens a security problem.

    The error getting on the page is:

    Err -1
    Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

    As said if the procedure is executed as Administrator the error does not happen. So the question how can I give access to the alternate user to "Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' "?

    The DB was reconfigured with below settings:

    exec sp_configure 'show advanced options', 1;
    RECONFIGURE;
    exec sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE;
    GO

    Thanks,

    Carlos

    Friday, September 06, 2013 2:06 AM

Answers

All replies