Error 5(Access is denied.) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - SQL Server 2005/Yukon

Error 5(Access is denied.)

Author

Topic

mikenason
Starting Member

13 Posts

Posted - 2006-06-26 : 12:25:32

I am having a problem with OS2003 and SQL2005. I am attempting to do a Bulk Insert from my desktop using SQl Server Mangement Studio connecting to a SQL2005 database server that is looking to a mapped drive on a OS2000 file server.

truncate table OST_DataComm_Log_Data_Load
bulk insert OST_DataComm_Log_Data_Load
from '\\myserver\r-drive\DataComm\Logs\Site2Site.Log'
with
(
datafiletype = 'char',
rowterminator = '\n',
TABLOCK
)

I get the following message:

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\\myserver\r-drive\DataComm\Logs\Site2Site.Log" could not be opened. Operating system error code 5(Access is denied.).

I can login to the Database server directly and the code runs no problem so I know taht my login has permissions, the SQL Service account has permissions and the Database server can see the file server. I can also browse to the folder/file.

HELP!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 05:33:13

The error sounds like it is windows preventing access to the file.

>> I can login to the Database server directly and the code runs no problem
If you are using the same account to do that as you are from your desktop then there should be no difference as far as sql server is concerned.
I would check the login under both circumstances.
The only other difference is a remote versus local login - that shouldn't matter as long as remote logins are allowed.

try a xp_cmdshell to see if you can access the file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikenason
Starting Member

13 Posts

Posted - 2006-06-27 : 17:11:11

Thanks NR

still no joy...I can use Remote Desktop to connect to the SQL Server from my desktop using my network login and the query runs correctly...

The xp_cmdshell DOES work..

exec master.dbo.xp_cmdshell 'dir \\hornet\r-drive\DataComm\Logs\*.Log'

returns a directory listing

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 03:22:33

Yes. You can connect to the network directory with your account. Is this your domain account or the local SQL Server account you are connecting with?

Has the account for which SQL Server is runnning with, have necessary permission to do this?

Peter Larsson
Helsingborg, Sweden

mikenason
Starting Member

13 Posts

Posted - 2006-06-28 : 09:47:28

I am using a network account that has permissions...the SQL Service account is a network admin account. I can login to my desktop with either account and the script does not run. If I log into the SQL server with either account the script does run.

MORE on this issue...If I connect from my desktop using the SQL login of 'sa' the script works.

mikenason
Starting Member

13 Posts

Posted - 2006-07-20 : 10:05:55

Does anybody have any more thoughts on this? I am almost to the point of having to open an issue with MS God forbid!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-21 : 05:49:40

Try
select system_user
select user_name()

when you log in from your desktop and on the server and see if there are any differences.
The only other thing I can think of is the protocol but that shouldn't have this effect.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikenason
Starting Member

13 Posts

Posted - 2006-07-21 : 10:19:26

In both cases; when I connect from my desktop and when I remote into the server and then launch Enterprise Manager, the

select system_user -> returns my network login

select user_name() -> returns 'dbo'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-24 : 04:53:38

being my old dense self...

if you logon to the server using the sql server account, open query analyzer and run the script there, any luck?

--------------------
keeping it simple...

mikenason
Starting Member

13 Posts

Posted - 2006-07-24 : 09:59:11

Jen,

Yes, if I remote into the SQL server from my desktop or login directly on the SQL server everything works. Here is something strange tho..If I run Enterprise Manager from my desktop and login as 'sa', the query works...THAT I do not understand.

dwumpus
Starting Member

1 Post

Posted - 2006-07-24 : 13:23:24

For those interested, when your Bulk Insert task fails in SSIS and gives you an error like:
Cannot bulk load because the file \\myserver\r-drive\myspecialdatafile.txt could not be opened. Operating system error code 5(Access is denied.).

you might be inclined to believe you have a permissions problem. WRONG!

you really have a problem with your format file. There is a column size out of whack or a row terminator in the wrong format. I suggest trying it through query analyzer manually first, work out the bugs in the files and then attempting your bulk insert task.

I hope this saves someone else a day of headaches.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-24 : 23:07:11

if dwumpus suggestion didn't resolve your problem:

try to open the mapped drive from your computer using the account you're running the bcp (windows authenticated?), create a file there
(do not remotely access), this bulk insert --> is it in a job?

--------------------
keeping it simple...

mikenason
Starting Member

13 Posts

Posted - 2006-07-26 : 09:38:50

Jen,

I am not using a format file so that is not the issue. I am running the script in the Enterprise Manager query window. I can open and create files in the folder on the file server no problem. With the exception of the sa account working from my desktop, it almost seems like my credentials are not being passed from the desktop to the SQL Server to the File server.

jagt
Starting Member

1 Post

Posted - 2006-07-27 : 07:44:33

I'm having the same problems in SQL 2005, but if I do login with the user 'sa' the issue resolved.

that's incredible.

All my services are login with administrators accounts:

Search Text SQL Server - domain\userSQL (administrators)

SQL Server - domain\userSQL (administrators)

Agent SQL Server - domain\administrator(administrators)

Explorer SQL Server - domain\administrator(administrators)

what is the difference of login with 'sa' or login with a 'administrator'?
what is the system account that use 'sa'?

thanks

mikenason
Starting Member

13 Posts

Posted - 2006-07-27 : 11:53:47

jagt

Knowing that you are having the same issue at least confirms, maybe only suggests, that I am not crazy. I have NO idea why the 'sa' account works from my desktop but my other admin accounts do not.

I hate to admit that after much fruitless searhing on the internet I am no closer to solving this issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources