Bulk insert statement fails in SSMS with Operating system error code 5 (Access is denied.)

Question

This issue was discussed in several places and it seems that general consensus is that if Windows Authentication is used then SSMS will access source file using Windows Domain user credentials.

However, Process Monitor shows otherwise. When I log into SSMS as a Windows Domain user, SSMS still uses SQL Server service account to access file in question, which sits on a network share. SQL service account does not have any access rights to the share, hence statement fails.

My understanding after reading quite a few answers for similar questions is that this should happen only if SQL Server Authentication is used when logging into SSMS, in which case SSMS uses SQL Server service account to access files. Whereas for Windows Authentication SSMS is supposed to use relevant Windows Domain account under which SQL statement is executed.

I wonder why SSMS uses SQL Server service account even when user uses Windows Authentication? Any thoughts/comments on this please?

The statement I'm attempting is:

bulk insert [database].[schema].[table] 
FROM '\\server\share\file_name.csv' with( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) 

This query shows I'm connected to the server via NTLM authentication:

SELECT dec.auth_scheme 
FROM sys.dm_exec_connections dec 
WHERE dec.session_id = @@SPID;

Thank you.

Answer 1

Microsoft's Documentation for BULK INSERT says this about security:

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).

The implication is you need to have Kerberos authentication working in order to allow impersonation. SQL Server needs valid Kerberos SPNs setup for this to work. I wrote a blog post about how to confirm that Kerberos SPNs are correctly setup over at SQL Server Science.

You'll also need to enable Delegation for the SQL Server Service Account. This post I wrote shows how to get that set up, and provides minimally complete and verifiable example code you can use to test the setup.