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?

  • 1
    It's an issue with permissions on a service account and probably not your own, while you are in SQL Server, it is going to use the SQL Server Service account to access the file or a proxy account or an account running the job. Make sure that account has access, since you have xp_cmdshell enabled, you could use this to print out the account that is attempting to access the file to double check who is doing what. – Shaulinator May 29 at 20:55
  • Also maybe a bug? – Shaulinator May 29 at 20:59
  • @Shaulinator Per the documentation on BULK INSERT that's only true if you are using SQL Server authentication. Also I checked and the service account does have access (that was part of the reason for the xp_cmdshell run of type). Good suggestions though :) – Kenneth Fisher May 30 at 15:11
up vote 4 down vote accepted

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."

  • This is perfect. Thanks! I've been trying to figure out why it was working for them on an older machine but not on the upgraded one. I'll check into this asap. – Kenneth Fisher May 30 at 15:13

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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