Access is denied error on bulk insert using UNC filename

Hot Topic

How can I turn off the encrypted login feature?
Looping through an excel spreadsheet
List of tables in use by a view
Null values
How to set an action?
How can i insert a default row if it doenst exists already?
Which AS 2005 Edition is running?
Data driven subscriptions and scale-out?
Will a SQL Server embedded be developed?
Sql instance error after upgrading to Express Advanced

SQL Server

Round producing no value
SSMSE CTP Uninstall and Visual Studio 2005
Report performance issue - time in processing
Problem to view OLAP data on web
Replace column value with MAX() query in dataflow before insert
Remote Connections with SQL Developers Edition
impoert and export data
Catch Error message in Variable?
After install MSDE 2000, Sql manager crash
sql2005:Cannot use SAVE TRANSACTION within a distributed transaction

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

Answer this question

Access is denied error on bulk insert using UNC filename

  • soumya30680

    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)

  • Zhouweidi

    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

  • Shabbar Husain

    Thanks

  • KP201

  • adamw2

    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.)
    • Change the permissions on the share on machine1 to allow the new userid on machine2 to have read access. I’d like to limit access to just the new userid on machine2 instead of the group EVERYONE. Do you know if this possible when the machines aren’t in the same domain

    Is that about it Thanks for your help.

    Phil
  • Access is denied error on bulk insert using UNC filename
Answer this question