SQL Server OPENROWSET without SYSADMIN role

Question

We extensively use the OPENROWSET function to import .CSV and Excel files into our SQL Server 2012 environment, using MSDASQL or ACE:

SELECT * 
FROM OPENROWSET ('MSDASQL',
                 'DRIVER={MICROSOFT access TEXT DRIVER (*.TXT, *.CSV)};', 
                 'SELECT * FROM E:\INCOMING\REPORT_EXTRACT.CSV') 

Or using ACE:

SELECT * FROM     OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','TEXT;DATABASE=E:\INCOMING\;HDR=YES;', 'SELECT     * FROM [REPORT_EXTRACT.CSV]');

We're experiencing the classic error message:

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

The ONLY workaround to enable this, is granting said user(s) with the SYSADMIN role - obviously not ideal.

The location of the file(s) is on the server itself, for which the users accessing DO have the necessary permissions to access that file folder. The SQL Server service is running under the local system account with permission to interact with the desktop.

• 

We've tried the following workarounds/fixes to no avail:

1 Executed the following code:

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

*2 The DisAllowAdHocAccess registry fix:

This is the current state:

3 Adding the ADMINISTER BULK OPERATIONS permission to the said users

We have dozens of expressions and files within our procedures that use OPENROWSET, therefore BULK INSERT, building SSIS packages, leverage Excel files as linked servers are NOT feasible options.

Any suggestions?

Answer 1

Wow - After YEARS of troubleshooting, I finally figured out what it was!

The registry entry value that controls the Allowing of Ad-hoc access, relies on the DisallowAdHocAccess being in that EXACT sentence case.

In our case, the "A" in AdHoc was not capitalized:

To fix this, I:

1. Deleted the registry value as described
2. Re-enabled the Disallow AdHoc Access option (Which re-created the registry value)

3. Manually set the Registry DisallowAdHocAccess value to "0"

Problem solved!