Cannot Bulk insert - access denied

  • Hi

    Its that most common of probs, can someone see where I might be going wrong. Don't think its a SQL issue.

    2 servers on the same domain.

    SQLServer

    AppServer

    On App Server there is a share called Data.

    In both Scenarios I logged in using my domain account

    1. If I run Bulk Insert on Management Studio  on SQLServer it works.  Even though its pointing to a share on another machine
    2. It fails if I run it on the AppServer in Management Studio. Again logged in as my Domain User
    Cannot bulk load because the file "\\AppServer\Data\my.csv" could not be opened. Operating system error code 5(Access is denied.).
    select SUSER_NAME()

    Returns the same user name in both cases i.e my Domain Account. SQL Server runs as a local system account but that shouldn't be relevant as I'm connected with trusted Authentication.

    CREATE TABLE #t ([name] varchar(10), age int)

    select SUSER_NAME()
    Bulk Insert #t From '\\AppServer\Data\my.csv' WITH (FIELDTERMINATOR =',', FIRSTROW =2, MAXERRORS=0)

    Is it a double hop issue.

    If I cannot resolve it at least understand..

    Thanks

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Hi,

    I think this is the same topic:

    https://docs.microsoft.com/en-us/answers/questions/265195/cannot-bulk-load-because-the-file-could-not-be-ope.html

    Maybe a kerberos problem with a double hop, or the service account got no permission on the file.

    Kind regards,

    Andreas

  • Thank you Andreas I will certainly check this out, the thread also links to

    https://techcommunity.microsoft.com/t5/sql-server-support/sql-bulk-copy-error-8220-operating-system-error-code-5-access-is/ba-p/316231

    Which looks promising..

  • the issue is most definitely that the SQL service is running as a local account, and not a domain account.

    SELECT * FROM sys.[dm_server_services]

    when you run any TSQL that accesses a resource outside of the SQL Instnace and it's databases(think \\incpath, drives on the server itself, etc.)

    the service account running SQL server must have access to the resource.

    it doesn't matter that suser_name() is both sysadmin and domain admin or anything, access to an external resource is via the context of THAT service account.

    this is common when someone puts a file on their own desktop on  the server, for example, too; the service account doesn't have access to C:\Users\Lowell\Desktop or C:\Users\Lowell\Documents either.

    move the file to  a location the service account would certainly have access to(SQLDataRoot,backup directory, etc)

    or grant the computer resource YourSpecificServerName$ (note the dollar sign! to the\\AppServer\Data\ folder with at least read permissions , do the migration, and then remove the permissions

     

    the screenshot below has  a server named Mango as the example:

    • This reply was modified 11 months, 1 week ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply