none
BULK INSERT "AccessDenied" on remote text file. RRS feed

  • Question

  • OK, Ive read many posts on this problem but have seen no resolution.

    Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

    Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

    When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

    Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

     

    Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

     

    Thanks

    Monday, February 5, 2007 3:33 AM

All replies

  • What account is the sql server service running under?  And when your devs connect, are they using windows accounts or SQL logins?  Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx?  See the "Security Considerations" section to understand how it works.

    Monday, February 5, 2007 4:53 AM
  • I am seeing the same thing.  I am convinced it is a bug also.

    It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally.  I too have tried everything recommended and have had no luck.


    Monday, February 5, 2007 2:33 PM
    Moderator
  • According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs.  You guys saying you tried impersonation/delegation but it didn't work, is this correct?  If so, post your repro below and we can take a look at it.
    Monday, February 5, 2007 4:00 PM
  • The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

    "BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

    That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

    Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

    Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

    WHY IS SQL USING "ANONYMOUS"?

    Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

     

    Monday, February 5, 2007 4:29 PM
  • Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

    Now, what is a "repro" and is delegation the same thing as imersonation?

     

    Thank you your help.

    Monday, February 5, 2007 4:32 PM
  • Sorry, repro is short for "reproduce".  Basically give me an example that I can try to duplicate on my machines here.  If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing. 

    Monday, February 5, 2007 5:18 PM
  • Delegation is a Windows things.  If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation.  If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security".   I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

    Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario.  I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied. 

    The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

     

    Monday, February 5, 2007 5:31 PM
  • OK, here's our setup...

    Active directory Account: SQL05svc (Administrator to all servers in question.)
    SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
    File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
    Client WS: Client01 (Windows XP - Fully Patched)

    The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

    Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

    From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

    BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
    WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
    ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
    ROWTERMINATOR ='\"\n\"',
    DATAFILETYPE = 'char',
    CODEPAGE='1252')
    GO

    It runs fine. One hop.
    Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

    From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

    BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
    WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
    ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
    ROWTERMINATOR ='\"\n\"',
    DATAFILETYPE = 'char',
    CODEPAGE='1252')
    GO

    This is a two hop scenario.
    And we get the following error...

    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

    Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

    It's a pretty vanilla setup.

    Please let me know your findings.

    Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
    We also have a SPN for the instance of SQL in the Domain for SQL05svc.

    Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!


    Thanks again for trying to help us.

     

     

     

     

     

    Monday, February 5, 2007 6:52 PM
  • I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

    Does anyone have this working?

    Monday, February 5, 2007 7:43 PM
  • I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

    The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

    Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error.  Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.

    Monday, February 5, 2007 9:10 PM
    Moderator
  • Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

    Thanks for your reply though.

    Monday, February 5, 2007 9:29 PM
  • In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

    We have sql 2000 running on it : it doesn' function.

    I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

    Tuesday, February 13, 2007 3:09 PM
  • Issue is resolved in SP2 beta.
    Friday, February 16, 2007 11:43 AM
  • Issue WAS resolved in SP2 CTP (Beta).

    BUT it is not resolved in SP2 final release (the same problem as before).

     

    Monday, February 26, 2007 10:58 AM
  • Any resolution to this problem yet?
    Friday, March 9, 2007 4:53 PM
  • I just want to second that. I've been trying to resolve this issue for a long time.

    Friday, March 9, 2007 5:27 PM
  • Another note on this, for what it's worth:
    When I get this error, if I log off of my workstation, then back on, the bulk insert works fine. It seems to be dropping the credentials or something after some while.

    ( We've got the latest SP2 installed )

    Friday, March 9, 2007 6:11 PM
  • I saw a hotfix to SP2 which said it fixed this problem.  However, when I search now it is gone.

    I would suspect it caused more problems, since it was in SP2 beta and taken out, and now posted in a hotfix and taken out.

    At least it seems MS knows it is a problem and seems to be working on it. I would keep looking for a fix soon.

    Monday, March 12, 2007 2:01 PM
  • It would be nice if someone from MS would comment on this bug!! This is causing me all kinds of problems.
    Monday, March 12, 2007 8:31 PM
  • We have the same problem, can't for them to fix it.
    Friday, March 16, 2007 11:05 AM
  • Hi

    Changes needed in Active Directory.

    You must change on the SQL Host PC --> tab Delegation --> trusted for delegation put to true.

    You must change the Account where the SQL Service is running under --> tab Delegation --> trused for delegation put to true.

    Reboot SQL server

    After this all Bulk Upload problems are resolved.

    Best regards

    Friday, March 16, 2007 11:13 AM
  • Have done that 30 times, it does not help. SQL is always passing "Anonymous" login.

    Unless "Everyone" has read rights to the file/directory, Bulk Insert does not work.  This is not an option for my situation because of the information stored in the files.


    Friday, March 16, 2007 2:19 PM
    Moderator
  • Hi,

    There is a difference between share and folder.

    Put Everyone full access on Share level.

    Only give control to the users that need it on folder level on the box where the share is on.

    This way only the users that you give rights on folder level can access the data on the share.

    Then it works.

    Regards

    Friday, March 16, 2007 2:58 PM
  • Hi,

    Disregard my last post.

    Putting on the delegation on the SQL box and rebooting it fixed the problem on our site.

    We have tested it in 3 environments but we don't use a cluster.

    The diff between 2000 and 2005 is that in 2005 sql passes the user that is running the process to access the share and in 2000 sql passed the user info of the user defined on the SQL service.

    But once we put the delegation marker on the SQL server entry in AD we didn't receive anonymous anymore while accessing the share but received the correct (or expected credentials).

    Regards

    Friday, March 16, 2007 3:22 PM
  • Hi all,

    We've been having this problem for the last few days also since upgrading to SQL2005 SP2 and our fix was to make the userid that the SQL service runs under as 'trusted for delegation' and making the UNC path point to the real server name.  ie don't point it to a tcp dns alias name.

    Hopefully this may help.

    Cheers

    Monday, March 19, 2007 12:46 AM
  • Problem fixed! Actually quite a while ago.....

    The issue was we needed to configure an SPN for the instance of our SQL Instance and configure it for Kerberos Authentication.

    Thursday, November 1, 2007 8:09 PM
  • Hi all,

    I have been running into the same situation. Following is my setup:

    1. Computer A - Windows XP client

    2. Computer B - MS SQL Server 2005 (Developer Edition)

    3. Computer C - UNC File Server

     

    Now the problem:

    1. Scenario A:

    a. I log onto Computer A with my user a/c (windows authentication).

    b. Connect to Computer B through SSMS using windows authentication.

    c. Issue a BULK INSERT command through Query Analyzer, to load a File (\\Computer c\Path\FileName.csv) into a table in SQL on Computer B

     

    I get the following arror:

    Cannot bulk load because the file "\\Computer c\Path\FileName.csv" could not be opened. Operating system error code 5(Access is denied.).

     

    2. Scenario B:

    a. I log onto Computer A with my user a/c (windows authentication).

    b. Connect to Computer B through SSMS using windows authentication.

    c. Kickoff a job that calls an SSIS package which issues the same BULK INSERT command.

     

    The job executes successfully and the data is loaded.

     

    3. Scenario C:

    a. I log onto Computer B using remote desktop, with a windows user a/c which is specially setup to run the SQL Jobs steps (The Proxy a/c defined in the "Run as").

    b. Connect to the local instance to SQL Server using SSMS

    c. Issue the same BULK INSERT command through Query Analyzer, to load a File (\\Computer c\Path\FileName.csv) into a table

     

    The data is successfully loaded.



    I am looking at Delegation / Impersonation after going over this post, but would appreciate if someone could guide me as to the core of the issue and the steps to fix it.

     

    Thanks!

     

     

    Thursday, July 8, 2010 10:43 PM
  •  (Mustafa S. Ali) writes:

    I have been running into the same situation. Following is my setup:

    For the future: rather than adding new questions to old threads,
    start a new thread.

    As for your question, I looked in Books Online under the topic for BULK
    INSERT, and there is a section "Security Account Delegation (Impersonation)"
    that seems to address your question. Did you look at that section?


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

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Friday, July 9, 2010 8:10 PM