Skip to main content

 none
BULK INSERT error when the file location changed to remote share RRS feed

  • Question

  • I am getting following error on BULK INSERT now that the file location was changed to remote share. Before it used to a shared folder in local drive and we never ran into this issue. I have made sure both SQL server and file permissions are in place. I am running this BULK INSERT from my local PC connecting to SQL Server via SSMS.

    Before when I ran this command from SSMS, it was \\SQLServer\FTP location which was a shared folder in local drive in that SQL Server but now I changed the file location to a network share \\Fileshare\FTP and have the above error but both SQL service account(domain account) and me(domain account) have elevated permission on that new location.

    Any help or suggestions!!

    Thanks,

    --Anup


    Tuesday, October 15, 2019 2:41 PM

Answers

All replies

  • Hi

    I think the below thread seems to be helpful to resolve your issue.

    https://stackoverflow.com/questions/3785183/cannot-bulk-load-because-the-file-could-not-be-opened-operating-system-error-co

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 15, 2019 2:47 PM
  • Permissions are in place in both Fileshare and the SQL server. This is something more than just permissions.

    Tuesday, October 15, 2019 7:26 PM
  • Permissions are in place in both Fileshare and the SQL server. This is something more than just permissions.

    If you get an error message about username and password, it certainly has to do with permissions.

    When you run BULK INSERT and you are logged in as a Window user, SQL Server will impersonate your login when accessing the file. Thus, you need to have permission on the file. Futhermore, things must play well in Kerberos. That is, SQL Server must actually be trusted to impersonate you.

    If you are using an SQL login, for instance sa, SQL Server will access the share with its own permissions. But if SQL Server runs managed service accout like NT Service\MSSQLSERVER, the access will be in the name of the machine account DOMAIN\MACHINE$.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 15, 2019 9:12 PM
  • Thanks for your reply.

    Yes, it is related to permissions but we have crossed out the file permissions and SQL server permissions because both the SQL service account and I (both Domain Accounts) have admin privilege in SQL and FULL control on the fileshare folder. I am interested in learning more about the Kerberos authentication because that is what I was suspecting too after some additional research.

    What exactly (the bare minimum) do I need to change on server to make that happen? Could you please provide more insights on that??

    Tuesday, October 15, 2019 9:53 PM
  • Sorry, I don't see your system, so I can't say what the issue is. Nor am I a Windows guy, so I don't know exactly what this error message implies. What I can say from experience is that in situations like this, it helps to double- and triple-check that everything is set up OK. You may have to work with your Windows admin to see if there is anything interesting configured with the share.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 16, 2019 9:10 PM
  • It finally worked....

    I had to configure Kerberos Authentication following the guide from this link https://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied/.

    Of course, I had to make adjustments to suit our environment and had to involve Active Directory Admin for creating SPNs and enabling DELEGATION properties.

    Thanks.

    • Marked as answer by Anup Pudasaini Wednesday, October 23, 2019 8:34 PM
    Wednesday, October 23, 2019 8:34 PM