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 "myDomainmyUser"
Box "S" ==> An SQLServer2005 box (Win2003), where "myDomainmyUser" is a
bulkadmin (and a dbcreator)
SQLServer is running under the Local-System acount.

Box "C" ==> An SQL-Client box, where "myDomainmyUser" 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
"myDomainmyUser" 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 "myDomainmyUser".
I run this command:

=========================================================================== > > BULK INSERT myTbl FROM N'\PoohMyUNCShareinput1.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 "\PoohMyUNCShareinput1.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 "myDomainmyUser",
and will manage to read the file.
Well - it doesn't work.
I even tried running the SQL-Server on "S" under the "myDomainmyUser"
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/microsoft.public.dotnet.framework.aspnet.security/2005-12/msg00023.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.
meir@clearforest.com

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 "myDomainmyUser"
Box "S" ==> An SQLServer2005 box (Win2003), where "myDomainmyUser" is a
bulkadmin (and a dbcreator)
SQLServer is running under the Local-System acount.

Box "C" ==> An SQL-Client box, where "myDomainmyUser" 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
"myDomainmyUser" 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 "myDomainmyUser".
I run this command:

=========================================================================== > > BULK INSERT myTbl FROM N'\PoohMyUNCShareinput1.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 "\PoohMyUNCShareinput1.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 "myDomainmyUser",
and will manage to read the file.
Well - it doesn't work.
I even tried running the SQL-Server on "S" under the "myDomainmyUser"
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/microsoft.public.dotnet.framework.aspnet.security/2005-12/msg00023.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.
meir@clearforest.com