locked
Cannot bulk load because the file operating system error code 5 access is denied. RRS feed

  • Question

  • Please help me out.

    I double check permission with the account, everything looks good but still getting error message.

    Thanks

    Kumar


    KG, MCTS
    Wednesday, July 6, 2011 3:37 PM

Answers

  • Msg 4861, Level 16, State 1, Procedure LKUP_BAS8REN5_TEMP, Line 18
    
    Cannot bulk load because the file "\\VAPP-CPB-SQL\ShardFiles\FileName.txt" could not be opened. Operating system error code 5(Access is denied.).
    
    Msg 4413, Level 16, State 1, Line 3
    
    Could not use view or function 'vTxtFileTemp' because of binding errors.

    I double checked both my login as well as SQL Server Service Account has read/write access to the FILE SERVER location "\\VAPP-CPB-SQL\ShardFiles\"


    This is expected behaviour

    {
    Security Account Delegation (Impersonation)

    If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

    When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

    To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
    }

    Ref
    BULK INSERT http://msdn2.microsoft.com/en-us/library/ms188365.aspx
    Security Account Delegation http://msdn.microsoft.com/en-us/library/Aa905162


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Naomi N Tuesday, July 12, 2011 11:30 PM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 12:33 AM
    Tuesday, July 12, 2011 6:51 AM

All replies

  • Which account? Did you check the access rights for the service account for SQL Server? Where is the file? On a network share?

    Also check that the file is not open by another application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 6, 2011 10:21 PM
  • This error happened to me earlier which was caused by insufficient user priviledges. Please provide the user account and error message (SQL Server or job history).
    Wednesday, July 6, 2011 11:11 PM
  • i agree you need to check the "sql server service account" permission

    for more information please consult this

    http://msdn.microsoft.com/en-us/library/ms143504.aspx

    service account is the account used by sql server to run sql process

    while your account is the one you use to login

     

    it is  possible that even if you are an admin but

    and the service account is not you might not be able to do the job

    sql server is deployed secure by default so you need to configure

    the service account manually


    http://joeydj.com
    Thursday, July 7, 2011 1:17 AM
  • Are you using a SQL Account or a Windows Account?  I assume you are using SQL Server 2008 R2, but permissions needs to be set for different versions, such as SQL 2000.

    Basically, if you are using a sql account, the credentials are reliant on the sQL Server account, if you are using windows authentication, the user should have permissions to the file.

    You should check out this link (Security Considerations), http://msdn.microsoft.com/en-us/library/ms175915.aspx

    If a user uses a SQL Server login, the security profile of the SQL Server process account is used. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

    For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.


    http://jahaines.blogspot.com/
    Thursday, July 7, 2011 2:43 AM
  • If you have checked the SQL Service permissions and have found them to be okie then just do a simple test - try moving the file from which you want to pick the data to C:\ or D:\ directly and not in any system generated folder or a secured folder and then try importing the data.

    Hope, this helps !


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Thursday, July 7, 2011 6:08 AM
  • Hello Erland,

    Here is my scenario:-

    I'm trying to access txt file directly WITHOUT storing into actual table, so I have created view, here is the script:-

    ALTER VIEW [dbo].[vTxtFileTemp] 
    AS
    
    WITH SET0 AS
    (
    	SELECT
    		REPLACE(REPLACE(REPLACE(bulkcolumn, CHAR(13) + CHAR(10), '|'), CHAR(9), ','),'''','') AS bulkcolumn 
    	FROM (SELECT bulkcolumn FROM OPENROWSET(BULK N'C:\FileName.txt', SINGLE_CLOB) AS Document) a
    )
    
    ,SET1 AS
    (
    	SELECT
    		NULLIF(SUBSTRING(bulkcolumn, 1, CHARINDEX('|', bulkcolumn, 1)),'') AS column1,
    		CHARINDEX('|', bulkcolumn, 1) AS column2
    	FROM
    		SET0
    
    	UNION ALL
    
    	SELECT
    		NULLIF(SUBSTRING(bulkcolumn, column2+1, CHARINDEX('|', bulkcolumn, 1)),'') AS column1,
    			CHARINDEX('|', bulkcolumn, column2+1) AS columns2
    	FROM
    		SET0 a
    	CROSS JOIN
    		SET1 b
    	WHERE
    		b.column1 <> ''
    			AND b.column1 IS NOT NULL
    )
    
    ,SET2 AS
    (
    	SELECT
    		SUBSTRING(column1, 1, CHARINDEX(',', column1)-1) AS aBCD09,
    		SUBSTRING(column1, CHARINDEX(',', column1)+1, CHARINDEX('|', column1)-12) AS BCD09EFG7
    	FROM SET1
    )
    
    SELECT
    	aBCD09, BCD09EFG7
    FROM
    	SET2 
    WHERE
    	aBCD09 <> 'aBCD09'
    		AND BCD09EFG7 <> 'BCD09EFG7'
    


    Now I'm hitting query as:-

    SELECT	*
    FROM
    	vTxtFileTemp
    

    Everything is working perfectly as expected. Here is my txt file:-
    https://skydrive.live.com/#!/?cid=15e0e13bd0545504&sc=documents&uc=1&id=15E0E13BD0545504%21137 (FileName.txt)

     

    If you see the code txt is stored on the C drive of the Server, which won't be the case, we have separated FILE SERVER for storing the file, so I modified the above script as below:-

    WITH SET0 AS
    (
    	SELECT
    		REPLACE(REPLACE(REPLACE(bulkcolumn, CHAR(13) + CHAR(10), '|'), CHAR(9), ','),'''','') AS bulkcolumn 
    	FROM (SELECT bulkcolumn FROM OPENROWSET(BULK N'\\VAPP-CPB-SQL\ShardFiles\FileName.txt', SINGLE_CLOB) AS Document) a
    )
    

    If I execute above script from my client machine it is giving me error as :-

    Cannot bulk load because the file "\\VAPP-CPB-SQL\ShardFiles\FileName.txt" could not be opened. Operating system error code 5(Access is denied.).

    Above notes I have written by executing from my local client machine.


    Now I remotely logged into the the SQL Server BOX with SQL Server Service Account and executed script as (see I'm using my actual FILE SERVER instead of SQL Server Box C Drive)

     

    ALTER VIEW [dbo].[vTxtFileTemp] 
    AS
    
    WITH SET0 AS
    (
    	SELECT
    		REPLACE(REPLACE(REPLACE(bulkcolumn, CHAR(13) + CHAR(10), '|'), CHAR(9), ','),'''','') AS bulkcolumn 
    	FROM (SELECT bulkcolumn FROM OPENROWSET(BULK N'\\VAPP-CPB-SQL\ShardFiles\FileName.txt', SINGLE_CLOB) AS Document) a
    )
    
    ,SET1 AS
    (
    	SELECT
    		NULLIF(SUBSTRING(bulkcolumn, 1, CHARINDEX('|', bulkcolumn, 1)),'') AS column1,
    		CHARINDEX('|', bulkcolumn, 1) AS column2
    	FROM
    		SET0
    
    	UNION ALL
    
    	SELECT
    		NULLIF(SUBSTRING(bulkcolumn, column2+1, CHARINDEX('|', bulkcolumn, 1)),'') AS column1,
    			CHARINDEX('|', bulkcolumn, column2+1) AS columns2
    	FROM
    		SET0 a
    	CROSS JOIN
    		SET1 b
    	WHERE
    		b.column1 <> ''
    			AND b.column1 IS NOT NULL
    )
    
    ,SET2 AS
    (
    	SELECT
    		SUBSTRING(column1, 1, CHARINDEX(',', column1)-1) AS aBCD09,
    		SUBSTRING(column1, CHARINDEX(',', column1)+1, CHARINDEX('|', column1)-12) AS BCD09EFG7
    	FROM SET1
    )
    
    SELECT
    	aBCD09, BCD09EFG7
    FROM
    	SET2 
    WHERE
    	aBCD09 <> 'aBCD09'
    		AND BCD09EFG7 <> 'BCD09EFG7'

    I'm able to execute above script sucessfully but when I running below code from my local machine, "SELECT * FROM vTxtFileTemp", I'm getting error as :-

    Msg 4861, Level 16, State 1, Procedure LKUP_BAS8REN5_TEMP, Line 18
    Cannot bulk load because the file "\\VAPP-CPB-SQL\ShardFiles\FileName.txt" could not be opened. Operating system error code 5(Access is denied.).
    Msg 4413, Level 16, State 1, Line 3
    Could not use view or function 'vTxtFileTemp' because of binding errors.

    I double checked both my login as well as SQL Server Service Account has read/write access to the FILE SERVER location "\\VAPP-CPB-SQL\ShardFiles\"


    Please help me out. What I'm doing wrong and where I'm missing parts? :(

    Thanks in advance

    Kumar


    KG, MCTS
    Thursday, July 7, 2011 2:23 PM
  • > I double checked both my login as well as SQL Server Service Account has read/write access to the FILE SERVER location "\\VAPP-CPB-SQL\ShardFiles\ <file://vapp-cpb-sql/ShardFiles/>"

    But have you checked the access rights on the file itself?

    And what is the service account for SQL Server?

    I'm afraid it's difficult to assist with permissions problems in Windows on a distance without access to the machines to check everything.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 7, 2011 10:14 PM
  • An alternate is SSIS Import/Export wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard

    If recurring job, you can save the package and set it up as a job with SQL Server Agent with or without schedule.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Edited by Kalman Toth Monday, October 1, 2012 12:33 AM
    Tuesday, July 12, 2011 6:38 AM
  • Msg 4861, Level 16, State 1, Procedure LKUP_BAS8REN5_TEMP, Line 18
    
    Cannot bulk load because the file "\\VAPP-CPB-SQL\ShardFiles\FileName.txt" could not be opened. Operating system error code 5(Access is denied.).
    
    Msg 4413, Level 16, State 1, Line 3
    
    Could not use view or function 'vTxtFileTemp' because of binding errors.

    I double checked both my login as well as SQL Server Service Account has read/write access to the FILE SERVER location "\\VAPP-CPB-SQL\ShardFiles\"


    This is expected behaviour

    {
    Security Account Delegation (Impersonation)

    If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

    When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

    To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
    }

    Ref
    BULK INSERT http://msdn2.microsoft.com/en-us/library/ms188365.aspx
    Security Account Delegation http://msdn.microsoft.com/en-us/library/Aa905162


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Naomi N Tuesday, July 12, 2011 11:30 PM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 12:33 AM
    Tuesday, July 12, 2011 6:51 AM