BULK INSERT from secured share

Hello,
I am trying to use BULK INSERT to import data from a file that resides
on a shared drive. I've read many posts about this, but my situation
has a slight wrinkle (or two). The network people here have granted one
account that has access to this shared drive and I can map to that
drive using that user name and password:
NET USE X: \\PCName\Share password /user:username.
When run this command through a job I can then access the files through
DTS. Of course, for BULK INSERT it uses the SQL Server service account,
which is different from the SQL Server Agent service account, so it
can't access that drive (even as a UNC) because it has been mapped by a
different user. Am I correct so far?
I suspect that if I could use xp_cmdshell that I could map it with that
and the share would be available through BULK INSERT. I'm going to set
up a test on a local box to test this just for my own knowledge, but of
course on the production server xp_cmdshell is forbidden.
Possible solutions that I've come up with are:
1. Convince the network people to grant access to that drive to the SQL
Server service account (wish me LOTS of luck on that one
2. Convince the SQL Server guys that they should allow xp_cmdshell (ok,
that is impossible no matter how much luck you wish me)
3. Copy the files to the local box using a job (I'd like to avoid that
if possible)
Any other ideas?
Oh yeah, this is SQL Server 2000 on a Windows 2003 server. I don't
really have any access to the OS of the server and even my access to
the SQL side of things is pretty limited.
Thanks!
-Tom.You might be able to make use of OPENROWSET to read the text file,
instead of using DTS.|||Tracy McKibben wrote:
> You might be able to make use of OPENROWSET to read the text file,
> instead of using DTS.
Thanks, I'll look into that. Any idea on how I would set up the
connection string? I'm not familiar with doing a "connect as" through
an OLE DB provider for text files.
Thanks,
-Tom.|||Offhand, no, I'd have to look around for some examples - lots of them
on the web.
I don't think you'll need to do any sort of "connect as". You should
be able to specify the UNC path to the text file, and when you run the
code from an Agent job, it will run (and connect) under the context of
the SQL Agent user.