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 the simple statement:

SELECT * FROM OPENROWSET ('MSDASQL',  
                 'DRIVER={MICROSOFT access TEXT DRIVER (*.TXT, *.CSV)};',   
                 'SELECT * FROM E:\INCOMING\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

Erland - I solved this!! It was a registry problem all along.
DisallowAdHocAccess value in the registry didn't have a capital "A" in "AdHoc" (Didn't realize it was case sensitive):

To fix this:

1) I manually deleted the entry
2) Re-initialized the Disallow Ad-Hoc Access in SSMS (which then created a new entry)
2) Manually set the registry value back to "0:

Thanks for all your help nonetheless!

Answer 2

MS SQL Server has Integration Services (SSIS).
SSIS can easily process .csv and Excel files.

Answer 3

Have you tried the effect of

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Although I am not sure that I really recommend enabling this option. I think in the long run, I might prefer from the use of OPENROWSET entirely.

Answer 4

It appears that you have checked "Disallow adhoc access" for the MSDASQL provider. You posted some screenshots above, but they are for the ACE provider, and that is not the one you are using. MSDASQL is ODBC-over-OLE DB.

Answer 5

So the parameter you need to set is DisallowAdHocAccess. And you need to set it for the MSDASQL provider. The one thing I missed in my post is the that you cannot control this parameter through SSMS, even if SSMS exposes it. The problem is that when this setting is missing from the registry, the default is 1. That is, you need to enter it directly in the registry, as you have in the screenshot for the ACE provider. Possibly, you also need to restart SQL Server. I did this when I tested, and I was able to access the provider from a non-privileged account. (It was an SQL login, but nevertheless.)

The parameter AllowInProcess has absolutely nothing to do with permissions. However, my recommendation is to leave it unchecked, as I found that when I use the statement you posted, with a non-existing path, this crashes SQL Server. This did not seem to happen when I tested with my non-priv account, but I got an error message. Possibly, this indicates that AllowInProcess does not apply to non-privileged account.

I should also add that once I had successfully run a SELECT statement with my non-priv account, I was not able to use the provider from session where I was connected as sysadmin, but there were error messages which indicates that having multiple sessions accessing the driver is not wholly supported. So in the end, you may find that you have to look for a different solution with SSIS or similar.