SQL Server – BULK INSERT Access Denied Error Resolution

bulk-insertsql serversql-server-2008-r2sql-server-2016

I'm trying to run a BULK INSERT but I'm getting an access denied error. So far I've tried the following:

  • I can open the file from my machine
  • I'm logged into the SQL Server (tried v2008 R2 & v2016) using windows auth
  • I have sysadmin permissions
  • I can type the file using xp_cmdshell (uses the SQL service account but it eliminates the idea that it's a firewall)
  • Path is a network URL not a local drive

The command I'm using is this:

BULK INSERT ken_temp
FROM '\\network_path\temp\kdf\text_file.txt'
WITH
   (
    FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'
   ,ROWS_PER_BATCH = 50000
   ,MAXERRORS = 10
   )

Any ideas?

Best Answer

The problem is that:

  1. You are logging in using Windows Authentication: this means that SQL Server will attempt to impersonate your Windows / Domain account for the file system access.

  2. You have logged in directly to a work station, and then connected remotely to SQL Server: this means that you connected to the server running SQL Server indirectly, by passing your authentication token from your workstation to the server running SQL Server.

  3. You have not enabled delegation for SQL Server: this means that you are stuck with the default behavior of not being allowed to pass that token beyond 1 step from the direct login.

  4. You are trying to read a file that exists on a remote resource: this means that it is more than 1 step beyond where you directly logged in.

This is why you can access the file from your workstation just fine. And it is why using xp_cmdshell also works since the service account is logged in directly to the server running SQL Server, and the remote resource is just 1 step beyond.

I suspect that if you were to remote desktop directly into the server running SQL Server, run SSMS on that server, and then execute the BULK INSERT, that it would succeed. Or, it should also work if you set up a SQL Server Login, grant the Login the "bulk admin" permission, then EXECUTE AS Login = 'JustReadTheDangFileAlready';, and then do the BULK INSERT. When executing BULK INSERT / OPENROWSET (BULK...) as a SQL Server Login, there is no Windows account to impersonate, so the file system access is done using the service account, which will be just like xp_cmdshell.

You have the following choices:

  1. Enable Delegation for SQL Server (can't remember if it's for the service or the account, in which case it might be your Windows account that needs it, but there are guides on how to set this up, which includes setting up the SPNs). I believe this is the preferred / recommended choice. This allows the process to forward the authentication tokens to resources further-removed from the direct login.

  2. Use a SQL Server Login for this process. I can't believe that I'm suggesting this, but if you had a SQL Server Login that was in the "bulk admin" fixed server role OR had the correct "bulk admin" permission, then you could create the associated User for that Login (in an appropriate DB), and place the BULK INSERT in a stored procedure that was created using WITH EXECUTE AS 'JustReadTheDangFileAlready'. Then you could execute that proc which should mask that the request was coming from a Windows account.

  3. Open up the permissions on that remote file (and maybe also the folder?). You could give "read" permission to "Everyone" (and maybe also "read" or some other permission on the folder).

  4. Delete this question and use this "error" as justification for needing an expensive SAN that will be connected via HBA card and appear as a local drive ?. Just tell the CFO: "It's really the only way. The internet said so."

Related Question