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?

Legg

I eventually solved this using a different path from the solutions below. It was not a double hop issue because the source file and database lived on the same server. Turns out the directory was not considered a trusted path by the ACE OLEDB driver. This required some registry change(s) that took a lot of tweaking to find the right combination of. I never identified exactly which changes did the trick and I no longer have access to the system in question to see the changes that were made to accomplish this. – Legg

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://learn.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.

Felisafelise

Sorry, the question must be unclear. The user in question is already an admin on the SQL server. I do not want to give the user access as an admin on the Windows server hosting SQL and the Excel file. I will edit the question to be more clear on this point. – Legg

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

Levant

Thank you, very helpful. We have actually tried writing to a file on the same physical server that SQL is installed on and have the same issue. Would this still apply in that case? – Legg

You wouldn't have the double hop issue in that case, but the user must have modify permission to the folder that the file is stored in. – Levant