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?
