5

We have a user who get this error message every time bulk load using network location it throws error below.

"Cannot bulk load because the file could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105)."

  • If users logs on the remote server and runs same query it works fine
  • FQDN is specified for the location of the file ( \servername\sharename\filename.txt)

  • When file is copied on the server ( C:\ drive) , query runs fine via SSMS

  • We are using SQL server 2008 R2

I have checked all NTFS permissions for the shared folder. I have gone through numerous posts suggesting solution but no luck.

  • Is the bulk load operation within a SSIS package, a bcp routine, or something else? – John Eisbrener Sep 14 '17 at 12:22
  • Did you see this question? stackoverflow.com/questions/19491812/… – John Eisbrener Sep 14 '17 at 12:33
  • this is a bcp routine, rading data from the txt file and upload to a table on SLQ server – Jatin Patel Sep 15 '17 at 16:00
  • I know nothing about sql server but that OS error 3 is probably "Path not found". You might want to double check the paths (quotes...) – gab Sep 19 '17 at 18:16
2

winerror.h lists Operating System Error Code 3 as ERROR_PATH_NOT_FOUND. This likely indicates that \\servername\sharename\filename.txt either doesn't exist or the SQL Server Service Account does not have access to the file via the UNC.

Use SQL Server Configuration Manager to determine the name of the SQL Server Service Account:

enter image description here

Check the Fileshare Security for the UNC to ensure that account has access to the share. Check the Filesystem Security for the folder underlying the UNC to ensure the account has access to the file.

T-SQL BULK INSERT requires an understanding of the security architecture. Taken from that page:

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.

| improve this answer | |
0

Given that:

  1. The error occurs when using a file on the network

  2. The error does not occur when using a local file (local to the server running SQL Server)

  3. The error does not occur when using a file on the network IF the user logs onto the remote server

I would say:

  1. Your user is connecting with a Windows Login

    If the user was connecting with a SQL Server Login, then the behavior would be the same whether they logged into SQL Server from a remote server or from directly on the server via Remote Desktop. SQL Server Logins do not exist outside of SQL Server (i.e. in Windows / Active Directory) and so have no security context that could be impersonated. In that case, the BULK INSERT / OPENROWSET(BULK... process will use the existing security context of the service account that is running the SQL Server process.

    When connecting with a Windows Login, there is a security context that can be impersonated and so the bulk operation attempts to do just that. The problem here is that forwarded security tokens cannot, by default, be re-forwarded. This is why the user gets the error when connecting from their desktop (they logged into their desktop to get the security token, and then logged into SQL Server remotely, using a forwarded security token). And it is why the user, when they log in directly to the remote server via Remote Desktop, they get a full security token from that server and connect directly to SQL Server with it, and hence can use it to forward to a networked service, such as a shared folder.

  2. You either need to:

    1. open up the permissions on the share for Everyone or Domain User or whatever

    2. Go to Active Directory and configure that Windows Login (for that user) for Delegation

    3. (maybe) set up a SQL Server User that can be impersonated via the EXECUTE AS clause of a CREATE PROCEDURE statement and create a Stored Procedure to do the bulk operation. I have not tried this so am not entirely sure if it would do the trick, but the idea is to switch the security context to that of a SQL Server account such that the bulk operation won't both with impersonation and will just use the current security context of the SQL Server service account. Worth a shot, perhaps.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.