|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
bulk insert from remote machine failsWhen I start a bulk insert from a network share locally on the server I have
no problem. When I start the bulk insert (is inside a stored proc) from a remote computer I get the following error message: Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "\\otherserver\share\file.txt" could not be opened. Operating system error code 5(error not found). I can't find any error in the eventvwr or SQL logs not on the SQL, the remote server or my local machine. Anyone any idea? Hi,
Ensure that the SQL Server service startup account is a domain account and that account must have read access to the remote share and folder which contains the file. Thanks Hari SQL Server MVP Show quote "Zekske" <Zek***@discussions.microsoft.com> wrote in message news:E88D10EA-50CB-4B67-9301-22514B142270@microsoft.com... > When I start a bulk insert from a network share locally on the server I > have > no problem. > When I start the bulk insert (is inside a stored proc) from a remote > computer I get the following error message: > Msg 4861, Level 16, State 1, Line 1 > > Cannot bulk load because the file "\\otherserver\share\file.txt" could not > be opened. Operating system error code 5(error not found). > > I can't find any error in the eventvwr or SQL logs not on the SQL, the > remote server or my local machine. > > Anyone any idea? > It is a domain account and it has the necessery rights because when I start
the stored proc on the server locally it connects to the share and it does the bulk import. When I'm behind my laptop and connect to the server with the same credentials and I start the proc with the same statement I'm getting the errormessage... Besides I also have the rights to open the file but that shouldn't even be necessary Show quote "Hari Prasad" wrote: > Hi, > > Ensure that the SQL Server service startup account is a domain account and > that account must have read access to > the remote share and folder which contains the file. > > Thanks > Hari > SQL Server MVP > > "Zekske" <Zek***@discussions.microsoft.com> wrote in message > news:E88D10EA-50CB-4B67-9301-22514B142270@microsoft.com... > > When I start a bulk insert from a network share locally on the server I > > have > > no problem. > > When I start the bulk insert (is inside a stored proc) from a remote > > computer I get the following error message: > > Msg 4861, Level 16, State 1, Line 1 > > > > Cannot bulk load because the file "\\otherserver\share\file.txt" could not > > be opened. Operating system error code 5(error not found). > > > > I can't find any error in the eventvwr or SQL logs not on the SQL, the > > remote server or my local machine. > > > > Anyone any idea? > > > > > It most likely is an access issue. To test for this try using the
xp_cmdshell command from the local machine and try to dir that directory: EXEC master.dbo.xp_cmdshell 'dir <path>' and see if it is allowed to see the contents of your directory... Sorry but had a little holiday but the problem still exists.
I can't use XP_cmdshell because we don't allow it but did some testing with different scenario's. Bottom line is when I do a bulk insert from a computer that's not the SQL-server and the file is located on a share on another computer I can't do the bulk insert when using windows authentication. When using SQL-server authentication I'm able to do the import. I get a system error code 5... The strange thing is when I start the same bulk insert with the same credentials but from the SQL-server itself there is no problem at all... So something is going wrong with my security credentials when doing a bulk insert with windows authentication from a remote client. Show quote "UnglueD" wrote: > It most likely is an access issue. To test for this try using the > xp_cmdshell command from the local machine and try to dir that > directory: > > EXEC master.dbo.xp_cmdshell 'dir <path>' > > and see if it is allowed to see the contents of your directory... > > Zekske (Zek***@discussions.microsoft.com) writes:
> I can't use XP_cmdshell because we don't allow it but did some testing This may be more a Windows networking issue than an SQL Server issue.> with different scenario's. > > Bottom line is when I do a bulk insert from a computer that's not the > SQL-server and the file is located on a share on another computer I > can't do the bulk insert when using windows authentication. When using > SQL-server authentication I'm able to do the import. I get a system > error code 5... The strange thing is when I start the same bulk insert > with the same credentials but from the SQL-server itself there is no > problem at all... > > So something is going wrong with my security credentials when doing a bulk > insert with windows authentication from a remote client. As I understand it, there are delegation in more than one level here. But then again, I am absolutely not going to claim that I know Windows networking. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I know but when I take a look with filemon I can see the file is beeing
opened with the account I'm logged in with. But still I have the strange message that I'm unable to open the file. Auditing the file and folder gives nothing in return... Show quote "Erland Sommarskog" wrote: > Zekske (Zek***@discussions.microsoft.com) writes: > > I can't use XP_cmdshell because we don't allow it but did some testing > > with different scenario's. > > > > Bottom line is when I do a bulk insert from a computer that's not the > > SQL-server and the file is located on a share on another computer I > > can't do the bulk insert when using windows authentication. When using > > SQL-server authentication I'm able to do the import. I get a system > > error code 5... The strange thing is when I start the same bulk insert > > with the same credentials but from the SQL-server itself there is no > > problem at all... > > > > So something is going wrong with my security credentials when doing a bulk > > insert with windows authentication from a remote client. > > This may be more a Windows networking issue than an SQL Server issue. > As I understand it, there are delegation in more than one level here. > But then again, I am absolutely not going to claim that I know Windows > networking. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >  |
|||||||||||||||||||||||
Other interesting topics