Bulk insert security

Category: sql server security

Question

AndyMN on Thu, 09 Oct 2014 16:08:28


I have an SSIS package on server A which calls a stored procedure on server B.  The stored procedure runs the bulk insert command to load an XML file.  The service account which SQL Server Services is running under on server A is a sysadmin on Server B, and has permissions to the file.  When I run the package I am getting "Cannot bulk load because the file could not be opened.  Operating system error code 5(Access is denied).  

If I create the server B database on server A, so that the SSIS package and the database where I call the bulk insert command from are both on server A, and run the SSIS package it runs successfully.

When everything is on the same server it works, so whatever account it is using has the necessary permissions.  But that account must not have the necessary permissions on server B.  What other accounts would need permissions on server B?

Thanks for the help.

Replies

Amit Banerjee on Thu, 09 Oct 2014 16:18:39


Do you have the SPNs set correctly as mentioned in the blog post: http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx

Erland Sommarskog on Thu, 09 Oct 2014 21:26:25


If you are logged in with a Windows account, BULK INSERT impersonates that user, and that user's Windows permissions apply. If you are logged in with an SQL login, the permissions of the service account applies.

Keep in mind that, the login on server B is a new login. There could be a login mapping set up. Also, several levels of impersonation may not work out well.