Access the filesystem with SQL server 2005

Hi,
We've got this strange situation. I want to perform a bulk insert in
SQLserver 2005 from a file on the file system. This works OK when I use a
SQLuser. When I do the same logged in with windows account (with
administrator privileges) I get an error. I thought that when I use a
windows account, this account is used to access the file system. When you
use a SQL account then the user of the sqlserver service is used to access
the filesystem. Am I wrong or how does this work ?
TIAYes, the windows account will need access to the file on the file system as
well as be a user with enough privledges on the database to perform the
insert. Are you using BCP or are you using SSIS to do the transfer?
--
Rob Walters
Program Manager - SQL Server
"Mark Brouwers" wrote:

> Hi,
> We've got this strange situation. I want to perform a bulk insert in
> SQLserver 2005 from a file on the file system. This works OK when I use a
> SQLuser. When I do the same logged in with windows account (with
> administrator privileges) I get an error. I thought that when I use a
> windows account, this account is used to access the file system. When you
> use a SQL account then the user of the sqlserver service is used to access
> the filesystem. Am I wrong or how does this work ?
> TIA
>
>|||Thanks for the reply,
I just use a query window within the management studio. I run the sql
statement as a SQLuser and it works. I change the connection properties to
my windows account (with administrator privileges) and is stops working.
Mark
"Rob Walters [MSFT]" <RobWaltersMSFT@.discussions.microsoft.com> wrote in
message news:4BBBCCE0-EFBD-4FD4-B046-B9C5371DA9E8@.microsoft.com...[vbcol=seagreen]
> Yes, the windows account will need access to the file on the file system
> as
> well as be a user with enough privledges on the database to perform the
> insert. Are you using BCP or are you using SSIS to do the transfer?
> --
> Rob Walters
> Program Manager - SQL Server
>
> "Mark Brouwers" wrote:
>

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