Bulk Load Operating System Error Code 5
Contents |
SnehadeepOctober 29, 201013 0 0 0 While there are various forms of bulk copy this blog specifically deals with copying data from a file into SQL Server. It deals about the cannot bulk load because the file operating system error code 3 specific error “Operating system error code 5(Access is denied.)” which might crop up under
Operating System Error Code 5 Access Is Denied. . Bulk Insert
certain circumstances when doing a bulk copy. For a while now I worked with a lot of DBAs and Developers
Operating System Error Code 5 Access Is Denied. Sql Server
bewildered with the problem and most of them complaining about the lack of good documentation about it and hours spent diagnosing in the wrong direction. If you are looking for details about bulk
Operating System Error Code 5(access Is Denied.) Sql Server 2012
copy visit http://msdn.microsoft.com/en-us/library/ms130809(SQL.90).aspx You run the following query BEGIN BULK INSERT ENVPOT_R FROM ‘\\advdev64\BulkTest\test_bulk_insert.txt' WITH ( FIELDTERMINATOR = ‘;', ROWTERMINATOR = ‘\n') END And end up getting the following error Msg 4861, Level 16, State 1, Line 1 Cannot operating system error code 5(failed to retrieve text for this error. reason: 15105) bulk load because the file "\\advdev64\BulkTest\test_bulk_insert.txt" could not be opened. Operating system error code 5(Access is denied.). The usual troubleshooting that DBAs do is to chase the “Access Denied” error from a file/folder access perspective. Some of them are as follows. a) Added “full” access to “everyone” (just temporary to test this) on the BulkTest folder and still getting the same error. b) Added “full” access to the SQL server service account on the BulkTest folder and still get the same error. c) The user is made a member of the bulkadmin fixed server role. Many DBAs come with prior experience on SQL Server 2000 where the following was true. Once a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of the bulkadmin fixed server role, the user could import the file by using
★★★★★★★★★★★★★★★ Jay[MSFT]February 16, 20091 0 0 0 PROBLEM DECRIPTION While executing a BULK INSERT command from a remote connection the following error is sql backup operating system error code 5 access is denied reported: Msg 4861, Level 16, State 1, Line 6 Cannot bulk load bulk insert administrators server role because the file "\\sqlbox\share\myText.txt" could not be opened. Operating system error code 5(Access is denied.). Checklist to follow cannot bulk load because the file does not exist to resolve this issue 1. Add the login you are using to the Bulk Insert Administrators Server Role. [Refer: http://msdn.microsoft.com/en-us/library/ms189934(SQL.90).aspx] 2. Make sure that you are using TCP/Kerberos (for delegation https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/10/29/sql-bulk-copy-error-operating-system-error-code-5access-is-denied/ to work Kerberos must be used) - a possible workaround is to use SQL authentication instead: select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid 3. Check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit https://blogs.msdn.microsoft.com/jay_akhawri/2009/02/16/resolving-operating-system-error-code-5-with-bulk-insert-a-different-perspective/ or can be downloaded here. After installation on your workstation run the following from command prompt: C:\Program Files\Support Tools> setspn -L serviceaccount There should be at least two SPNs listed, because the following two SPNs for the service account must be present for delegation to properly function: ServiceClass/Host:Port, where ServiceClass is the appropriate service class, Host is the name of the host computer, and Port is the port the service is running on. ServiceClass/FQDN, where FQDN is the fully qualified domain name of the host computer. 4. If there is no SPN for the MSSQLSvc, create one. All we do is to use SETSPN to add the service by executing the following: Setspn -A MSSQLSvc/Sqlbox.def.wxy.com:1433 Domain\sqlSvrSvcAccount Setspn -A MSSQLSvc/Sqlbox:1433 Domain\sqlSvrSvcAccount 5. Make sure you don't have duplicate SPNs. Refer http://support.microsoft.com/kb/321044. 6. Now that we created/verified that the SPN for the SQL Service, we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under. In the Delegation tab select "Trust this user for delegat
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the http://dba.stackexchange.com/questions/44524/bulk-insert-through-network company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish https://bytefactor.wordpress.com/2012/05/17/cannot-bulk-load-because-the-file-operating-system-error-code-5-access-is-denied/ to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are operating system voted up and rise to the top Bulk Insert Through Network up vote 7 down vote favorite 3 Can someone help me with these? BULK INSERT DATABESE01.dbo.TABLE01 FROM '\\COMPUTER01\FOLDER01\TextFile.txt' WITH ( FIELDTERMINATOR = ' ', rowterminator = '\n', tablock ) The error shows, couldn't open: Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.) The path is on another computer on the network. sql-server bulk-insert share|improve operating system error this question edited Dec 3 '14 at 6:02 RolandoMySQLDBA 107k15138274 asked Jun 13 '13 at 8:18 Imperial Jonas 38114 migrated from stackoverflow.com Jun 14 '13 at 11:20 This question came from our site for professional and enthusiast programmers. 7 Does the account that SQL Server is running under have permission to access '\\COMPUTER01\FOLDER01\TextFile.txt'? Note that the account will generally be different from your account. –Damien_The_Unbeliever Jun 13 '13 at 8:55 3 Can you also add the exact error you get? –ypercubeᵀᴹ Jun 14 '13 at 11:22 in addition to @Damien_The_Unbeliever: "To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename" –JoseTeixeira Jun 14 '13 at 11:52 @ypercube - Could not bulk insert because file '\\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.). thank you sir –Imperial Jonas Jun 15 '13 at 2:43 I have the same issue using the same domain and cross domain (Operating system error code 5(Access is denied.)). Interestingly, if I use XP_CMDSHELL to test if the file exists or i can even move the file, but the bulk insert throws access denied. The sql service account and my windows account both have permissions to the folder. –user53311 Dec 3 '14 at 2:00 | show 1 mo
code 5 access isdenied Posted by rajinikanths on May 17, 2012 in SQL bulk insert v_table from ‘C:\….' with (FIELDTERMINATOR=…) works bulk insert v_table from ‘\\