How to use INSERT INTO OPENROWSET to export to Excel without being an admin on the target server

Question

I am trying to grant a user access to export data from SQL Server into an Excel file using OPENROWSET.

The user is getting the following error:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

We can reproduce the issue running the following block of code, which I can run successfully and the user cannot:

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=YES;IMEX=0; Database=\\servername\exportdirectory\exportfile.xlsx', 'Select ExcelColumn from [TabName$]')
    SELECT TOP 1 SQLColumn FROM SQLTable

The only difference I can see between the users is that those who can successfully run this command and get the data into Excel are admins on the Windows server hosting both the SQL instance and the target directory.

The user who is unable to run the code has full control permissions on the target file directory where the excel file resides and has sysadmin permissions on the SQL instance.

Is there any way to allow this user to write to this file without granting full server admin rights on the Windows server itself?

Answer 1

According to MS Documentation the user who is executing the command needs Administrator Bulk Operations. This is a server level permission - bulkadmin. So you have to put any user that is going to do this in this role (at the server level) not necessarily make them a DBA.

https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017: OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. To use the BULK option requires ADMINISTER BULK OPERATIONS permission.

If you cannot do that (put the user in the BulkAdmin role) you may want to use SSIS to create the spreadsheet.

Answer 2

You are experiencing the "double hop problem". You need to enable impersonation so that the server hosting the share will accept impersonated credentials from the SQL Server. Here is an excerpt from the security considerations section of this page: Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)

SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.

This page might help you get started: Kerberos Constrained Delegation Overview