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;