June 15, 2006 at 10:02 am
I tried to run bulk insert file from another server, i.e. from a share located on another file server. I am DBA with sa as well as bulkadmin privileges on SQL Server, I have full access on file server so I can easily open the files, read them, change them, I can easily BCP file from that share into SQL Server. SQL Server's domain account has access to that file folder, but when I try to run Bulk Insert from SSMS, I get an error :
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\server_name\file_name.txt" could not be opened. Operating system error code 5(Access is denied.).
If I put same Bulk Insert command into a job and regardless to whom job ownership is assigned whether to sa or myself, it runs perfectly.
Can anybody suggest anything about this ?
Thanks
Mark
June 19, 2006 at 8:00 am
This was removed by the editor as SPAM
June 19, 2006 at 12:29 pm
I don't see a share name here. Did you provide the sharename?
Your post says: "\\server_name\file_name.txt"
Regards,Yelena Varsha
July 14, 2006 at 3:38 pm
I am having exactly the same issue:
INSERT INTO Xml_Docs_Storage
SELECT 'jmi060630170504ff2_8310', xml_doc
FROM (SELECT * FROM OPENROWSET
(BULK '\\servepath\xmldoc.xml',
SINGLE_BLOB) AS xCol) AS R(xCol)
and receive "Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\servepath\xmldoc.xml" could not be opened. Operating system error code 5(Access is denied.).
"
I have full priveledges on both the SQL Server Database as well as full privledges on the network share that I am attempting to retreive files from.
I could solve my problem by moving files from one server to another when I receive them, but I'd prefer not to have to do this.
Anyone have any thought?
July 15, 2006 at 11:32 am
With which security account that the server runs? I mean if the sql server service runs with a different account than yours or the the system account that may not explicit priveledges over the remote server you may receive that kind of error.
July 23, 2006 at 5:19 pm
Good thought Zubeyir, I'll make sure that the account that SQL Server is running on has privledges on both servers and try that.
July 31, 2006 at 7:54 am
you should go to yourserver/security/bulkadmin, then add your login account. Hope this will sort out your problem.
August 15, 2006 at 8:26 am
Security Account Delegation
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. For more information, see Security Considerations for Using Transact-SQL to Bulk Import Data.
When executing the BULK INSERT statement 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 use SQL Server Authentication and specify a SQL Server login, which uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about enabling a user account to be trusted for delegation, see Windows Help.
Fabio DalOsto
April 23, 2007 at 9:47 pm
Try adding "Trust for delegation" to your Windows user account if you got this error in SQL 2005.
July 24, 2007 at 6:15 am
Hello,
I am new to MS SQL Server stuff and I am getting exactly the same error. Someone before me at my work has created a report that bulk insert a temp table and display certain data. Recently, our file server has been moved so I change the name of the server in the stored procedure (report executes the procedure). When I execute the procedure in managment studio it returns the error. However, on report server report is executing fine.
If I use sql login (sa) to execute the procedure it works fine but windows login returns the error. Also, if I run the sql on SQL server machine it works fine.
SQL server is on one server, file is on shared file server and I am running the sql from different computer (my machine).
How can I check if my machine has privileges over the remote server?
Anyhelp would be great.
July 24, 2007 at 6:27 am
Check the account that you are using by looking the lower right corner. In the SQL Management Studio under the security/logins/<login_name>/User Mapping path you may give the permision to access your database. after than you can give insert permission to the table by right clicking on the table.
Cheers
Zubeyir
July 24, 2007 at 11:36 pm
This is what you have to do.
1) Grant "Trusted for Delegation" to your SQL server's service account (NT account)
2) Add an SPN to your SQL Server using Setspn -A command
once you done that you should see the following results when you run Setspn -L <sqlserver service account>
C:\>setspn -L sqlrvice_myserver
Registered ServicePrincipalNames for CN=sqlrvice_myserver,OU=Service Accounts,
DC=domain,DC=name,DC=net:
MSSQLSvc/myserver.domain.name.net:sql_portnumber
MSSQLSvc/myserver:2600
3) Your user account should have direct read access to the file you are loading
4) Connect to SQL server using TCP protocol not named pipe. you can do that by prefixing tcp: to your server name when you connect using Mangement studio.
Example: connect to tcp:ServerName rather than ServerName
Run the following statement on your management studio
select
net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
you should get the result as TCP, KERBEROS. If not your Bulk insert will not work and you have missed to do some steps above.
Remember this is only required if you use an NT account. If you use a SQL login then it uses SQL service accounts credentials to do the bulk insert.
Hope this helps to anyone having bulk insert issues in SQL 2005.
February 10, 2011 at 9:51 am
In SSMS run this to see what service account is set up to do directory listing:
Use Master
xp_cmdshell 'whoami.exe'
Go to yourserver\security\logins - right click on the service account returned above, go to Server Roles and check bulkadmin. It should work if you are a sysadmin on the server.
February 10, 2011 at 1:11 pm
From my past experience on bulk insert, the account under which the sql service is running on source should have full access (or atleast bulk import access) on the db and if inserting into flatfile should have full access to the target shared folder.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply