Bulk Insert Through Network

Question

Can someone help me with these?

BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
    FIELDTERMINATOR = ' ',
    rowterminator = '\n',
    tablock
)

The error shows, couldn't open:

Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.)

The path is on another computer on the network.

Answer 1

I'm going to make a leap of faith and assume you connect to SQL Server using Windows Authentication. In such a case you have a Kerberos double hop: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote \\COMPUTER01. Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To fix the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to 'enable unconstrained delegation' is just a typo as unconstrained delegation is just plain evil.

In the case I jumped the gun and you are logging into SQL Server using SQL Authentication you will need to create a credential for your SQL login and use that to access network resources. See CREATE CREDENTIAL.

Answer 2

I would run the script under a SQL Login (with bulkadmin permission) and let the permission on the network folder authenticate against the SQL Service Account or SQL Server Agent account.

Answer 3

Since we had this problem for ~5 different machines, the pain of the accepted answer was not worth the gain. Instead, we went for an obvious but less-efficient solution...

Just copy the file over to a local designated drive like \\MachineName\Upload before each bulk insert. This location is assumed to exist on each of the 5 servers.

Since most of our bulk inserts are called via a C# API that we control, a BulkInsertHelper() class was created to use as a proxy. It is called by passing the original network location and proceeds by first testing a small test bulk insert using it. If that fails it then it falls back to the \\MachineName\Upload directory and retests.

If that succeeds, then it knows to copy the file before calling actual bulk insert.

Here is a simple way to check that bulk insert will have access to a file:

alter procedure [Common].[usp_FileExists] (
    @pPathName nvarchar(300),
    @poExists bit out,
    @pDebug bit = 0
)    
with execute as 'LoginThatIsInBulkAdminServerRole'
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:   Return true if the file exists.  Alternative to the xp_fileexists which may requires special permissions 
               for some users.
                  
    Modified    By              Description
    ----------  --------------  ---------------------------------------------------------------------------------------
    2020.04.08  crokusek        Initial version from udf_FileExists with added bulk insert test as a backup
  ---------------------------------------------------------------------------------------------------------------------*/ 
begin try
  
    declare
        @fileExists int;
        
    exec xp_fileexist @pPathName, @fileExists output;  -- may require syadmin priv so usually fails.
    
    set @poExists = iif(coalesce(@fileExists, 0) = 1, 1, 0);
    
    if (@poExists = 1)
        return;
        
    -- else retest using a bulk insert as the above returns false negatives on 
    -- network drives that aren't physically the local machine and missing instance permission.
            
    begin try
        declare 
            @sql varchar(1000) = '
                declare @a bit = (
                    select 1 
                    from openrowset(bulk ''' + @pPathName + ''', single_blob) f
                );
            ';
            
        execute (@sql)        
        set @poExists = 1;  -- assume if no error that it worked
    end try    
    begin catch
        declare
            @errorMessage nvarchar(max) = error_message();
            
        if (@pDebug = 1)
            print 'Caught bulk test error: ' + @errorMessage;
        
        set @poExists = 0;
    end catch
    
    return @poExists;
end try
begin catch
    declare        
        @CatchingUsp  varchar(100) = object_name(@@procid);    
  
    if (xact_state() = -1)
        rollback;
    
/* Adjust as needed
    exec Common.usp_Log
        @pMethod = @CatchingUsp;
        
    exec Common.usp_RethrowError
        @pCatchingMethod = @CatchingUsp;  */
end catch
GO

Answer 4

Sql Server tried to open file, file server told it "Access denied" because the account SQL Server runs under has no permission to open the file. Change security settings on the file accordingly and it will work.