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