I simply cannot manage to BulkInsert into SQL2005 from a UNC share. HELP!

I simply cannot manage to BulkInsert into SQL2005 from a UNC share.

Here is my scenario: I have 3 boxes, all belonging to the same domain
"myDomain" (domain controller is Win2000)
I also have a domain-user "myDomain\myUser"
Box "S" ==> An SQLServer2005 box (Win2003), where "myDomain\myUser" is a
bulkadmin (and a dbcreator)
SQLServer is running under the Local-System acount.

Box "C" ==> An SQL-Client box, where "myDomain\myUser" is logged-on.
This box is using Windows-Authentication to connect to the
SQL2005 on "S"

Box "U" ==> A simple File-Sharing box (Win2003), holding a UNC-share where
"myDomain\myUser" has read permissions.
Let's assume the UNC-share already contains a bulk-insert
input-file in the correct format.


So,
I am running on "C" either an ADO.NET client-program, or "ServerManagement
Studio",
used to connect to SQL2005 and run a BulkInsert command.
The user that runs these programs is always "myDomain\myUser".
I run this command:

> > BULK INSERT myTbl FROM N'\\Pooh\MyUNCShare\input1.txt' WITH (
DATAFILETYPE='widechar', BATCHSIZE0, KEEPIDENTITY, FIELDTERMINATOR > N'|')
> >
and this is what I get
> > Server: Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\\Pooh\MyUNCShare\input1.txt" could not
be opened. Operating system error code 5(Access is denied.).
> >
Now - my thoughts are:
"C" is connecting to "S" using Windows-Authentication, and therefore when
"S" accesses "U" it will impersonate as "myDomain\myUser",
and will manage to read the file.
Well - it doesn't work.
I even tried running the SQL-Server on "S" under the "myDomain\myUser"
account, but this didn't help.


Situation with SQL2000:
-
Well, at first I was gonna say it works perfectly with SQL2000 (hey - we
have a product like that, which actually sells...)
but I tried it under the same conditions: guess what - DOES NOT WORK!
At this point I was thinking "wtf", but a few more hours and I had the
answer:
If you give the UNC-share permission to SQL2005MachineName$ - everything
works fine!
(This is what we do in our product, and therefore it works)
(Another way is to allow the UNC-share to "everyone", which probably
includes SQL2005MachineName$ )

So, I quickly rushed to my SQL2005 env to try this - only to pull-my-hair
even harder:
IT DOES NOT WORK! Period!


So, my question: WHY ?
Any help is highly appreciated.

I also found this article on the internet:
http://www.derkeiler.com/Newsgroups...00023.html

BUT: Do I really need to mess-around with Active-Directory settings and
kerberos stuff, just to get this simple thing to work ?
Hope not, cause if so then MS is shooting its own foot...

Meir S.

I simply cannot manage to BulkInsert into SQL2005 from a UNC share.

Here is my scenario: I have 3 boxes, all belonging to the same domain
"myDomain" (domain controller is Win2000)
I also have a domain-user "myDomain\myUser"
Box "S" ==> An SQLServer2005 box (Win2003), where "myDomain\myUser" is a
bulkadmin (and a dbcreator)
SQLServer is running under the Local-System acount.

Box "C" ==> An SQL-Client box, where "myDomain\myUser" is logged-on.
This box is using Windows-Authentication to connect to the
SQL2005 on "S"

Box "U" ==> A simple File-Sharing box (Win2003), holding a UNC-share where
"myDomain\myUser" has read permissions.
Let's assume the UNC-share already contains a bulk-insert
input-file in the correct format.


So,
I am running on "C" either an ADO.NET client-program, or "ServerManagement
Studio",
used to connect to SQL2005 and run a BulkInsert command.
The user that runs these programs is always "myDomain\myUser".
I run this command:

> > BULK INSERT myTbl FROM N'\\Pooh\MyUNCShare\input1.txt' WITH (
DATAFILETYPE='widechar', BATCHSIZE0, KEEPIDENTITY, FIELDTERMINATOR > N'|')
> >
and this is what I get
> > Server: Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\\Pooh\MyUNCShare\input1.txt" could not
be opened. Operating system error code 5(Access is denied.).
> >
Now - my thoughts are:
"C" is connecting to "S" using Windows-Authentication, and therefore when
"S" accesses "U" it will impersonate as "myDomain\myUser",
and will manage to read the file.
Well - it doesn't work.
I even tried running the SQL-Server on "S" under the "myDomain\myUser"
account, but this didn't help.


Situation with SQL2000:
-
Well, at first I was gonna say it works perfectly with SQL2000 (hey - we
have a product like that, which actually sells...)
but I tried it under the same conditions: guess what - DOES NOT WORK!
At this point I was thinking "wtf", but a few more hours and I had the
answer:
If you give the UNC-share permission to SQL2005MachineName$ - everything
works fine!
(This is what we do in our product, and therefore it works)
(Another way is to allow the UNC-share to "everyone", which probably
includes SQL2005MachineName$ )

So, I quickly rushed to my SQL2005 env to try this - only to
pull-my-hair
even harder:
IT DOES NOT WORK! Period!


So, my question: WHY ?
Any help is highly appreciated.

I also found this article on the internet:
http://www.derkeiler.com/Newsgroups...00023.html

BUT: Do I really need to mess-around with Active-Directory settings and
kerberos stuff, just to get this simple thing to work ?
Hope not, cause if so then MS is shooting its own foot...

Meir S.