SQL Cannot bulk load because the file <Shared file path> could not be opened. Operating system error code 1326

Question

We are trying to run Bulk insert in SSMS by logging local SQL Server accounts, But we are getting below error while running the query.

Error:

Msg 4861, Level 16, State 1
Line 1 Cannot bulk load because the file "\\AppserverIpAddress\SharedPath\Insert.TXT"could not be opened. Operating system error code 1326 (the user name or password is incorrect.).

Query:

BULK INSERT TABLENAME
FROM '\\AppserverIpAddress\SharedPath\Insert.TXT'
WITH 
  (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n'
  );

Observations:

1. The query is running successfully if I am logged in SSMS with Windows authentication (local Windows server administrator account and it has sysadmin privilege)
2. Query is throwing error when logged in with SQL Server user account such as SA (default sysadmin account) or other SQL Server account.
3. SQL Server Service is running under local Windows server administrator account (.\administrator)
4. Shared folder resides on the application server and has full control for account "Everyone".

Can anyone suggest a possible reason for this behavior?

Answer 1

That's the documnted behavior:

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.

BULK INSERT

SQL Server Service is running under local windows server administrator account

That's a bad idea, and you should change that anyway. Switch to a domain account that has access to the share, or switch back to the default virtual account and grant share access to the machine account eg MyDomain\MyServer$.