Access is denied error on bulk insert using UNC filename

I want to do a bulk insert of a file located on a different machine then the SQL Server database.

machine1 and machine2 are running Windows Server 2003 Standard Edition. SQL Server v8.0 is running on machine2.Neither machine1 nor machine2 are in any domain.(These are servers at a hosting company.)

I use a UNC filename to specify the file to load.It looks something like this:

\\machine1.someplace.com\reportdata\report200602.txt

I get this error message when I attempt the bulk insert using SQL Query Analyzer:

Server: Msg 4861, Level 16, State 1, Line 1

Could not bulk insert because file '\\machine1.someplace.com\reportdata\report200602.txt' could not be opened. Operating system error code 5(Access is denied.).

The share reportdata on machine1 has READ permissions for EVERYONE. What do I need to do enable allow the database machine (machine2) to access the files on machine1?

Thank you in advance for you help.

Phil

You could try to give the account that SQL Server is using explicit permissions on the remote share. This assumes that the SQL Server service is using a 'normal' windows account, if it's running under the Local System account, you can't access remote resources like this at all.

/Kenneth

|||

I checked on machine2 and the service MSSQLSERVER is running as the Local System account. So it seems that I have a couple things to do:

I should create a new user id for running the MSSQLSERVER service.(Should I also change the user id for MSSQLServerAdHelper and SQLSERVERAGENT?MSSQLServerAdHelper is set to Manual startup and isn’t running.)

|||Be aware that in SQL 2005 BULK INSERT no longer impersonates the SQL Server Service account to read the remote file, but impersonates the user that is connected to SQL service. (unless the connection is via a SQL Server Username rather than windows authentication)|||

You can take a look at the links below for more details on how to configure service accounts.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp

http://msdn2.microsoft.com/en-us/library/ms143504.aspx

|||

Thanks

Access Denied from Subscriber.

I get the following message from SQL Server Agent:
The schema script
'\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
The process could not read file
'\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
due to OS error 5.
(Source: BRIANT\HORIZON (Agent); Error number: 0)
------
Access is denied.
(Source: (OS); Error number: 5)
------
SQL Server on the Server and MSDE 2000 on the workstation are running
under a domain account.
The workstation has full access to the
\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promis e\20060113160017\
folder.
Any help would be greatly appreciated.
Ensure that the sql server agent on the subscriber has read rights to the
share \\LUKE\Replication and read and list folder rights to the physical
folder that maps to this drive and all child objects.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"klineb" <briankline@.hotmail.com> wrote in message
news:1137200691.533291.11060@.g14g2000cwa.googlegro ups.com...
>I get the following message from SQL Server Agent:
> The schema script
> '\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> The process could not read file
> '\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
> due to OS error 5.
> (Source: BRIANT\HORIZON (Agent); Error number: 0)
> ------
> Access is denied.
> (Source: (OS); Error number: 5)
> ------
> SQL Server on the Server and MSDE 2000 on the workstation are running
> under a domain account.
> The workstation has full access to the
> \\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promis e\20060113160017\
> folder.
> Any help would be greatly appreciated.
>
|||Hilary,
Both the sever and workstation are using the same Domain Account. The
Domain account is a member of the Administrators group on each machine.
I have verified that the account has permission on all drives on the
system.
When I set each workstation to use the domain account, I made the
changes through EM on the server.
Is there anything that I am missing?
Thanks in advance.
Brian