Sql-server – Permissions problem with BULK INSERT

bulk-insertpermissionssql-server-2008-r2

We are getting the following error when trying to use BULK INSERT.

Msg 4861, Level 16, State 1, Line 10 Cannot bulk load because the file
"\filer\share\loadfile.txt" could not be opened. Operating system
error code 5(Access is denied.).

When we check the security on the directory everyone is listed as having full access and in fact we have confirmed this by trying to open the file manually as myself, another DBA, one of the devs, and the service account running the instance. We also get the error when attempting to run the BULK INSERT myself, the other DBA, the dev and a sysadmin SQL login I created to run the test.

The instance is SQL Server 2008 R2

The code is: 

   CREATE TABLE #Temp1 (CodeNum VarChar(10), Cost Money)

   BULK INSERT #Temp1
   FROM '\\filer\share\loadfile.txt'
   WITH (
           FIELDTERMINATOR = ',',
           ROWTERMINATOR = ';\n'
           );

   GO

Can anyone give me some direction on where my security issue is?

Best Answer

First, I would check the "effective" permissions of the Service Account used to run the MSSQLSERVER service for that NTFS share. This is mainly to ensure that no "deny"s exist for that account, or any Active Directory group in which that the account exists, in a parent folder that are getting inherited down to the path in question.

Second, I would consider attempting to enable Kerberos Delegation for that service account, as per the Microsoft recommendation for BULK INSERT (see below). Now, I would have expected that granting the service account the appropriate read/execute permissions and using a SQL Server Login would have worked (delegation shouldn't be needed in this case because the logon event is local and the external resource is only a single hop away), BUT since it isn't, some other factor could be getting in the way (perhaps Kerberos and SPNs are required, even when not using Delegation). Either way, it seems like this is a common-enough scenario for Microsoft to have documented it across two MSDN pages.

If you currently are using NTLM instead of Kerberos, you try switching to Kerberos and setting up the necessary SPN first, prior to enabling any account for Delegating, and see if that works.

Security Account Delegation (Impersonation)

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

And if you follow that link at the end to the "Import Bulk Data..." page, you will see the following section:

Security considerations

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service).

To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process. 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.

Related Question