SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Error 5(Access is denied.)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikenason
Starting Member

USA
13 Posts

Posted - 06/26/2006 :  12:25:32  Show Profile

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

United Kingdom
12543 Posts

Posted - 06/27/2006 :  05:33:13  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 06/27/2006 :  17:11:11  Show Profile
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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 06/28/2006 :  03:22:33  Show Profile  Visit SwePeso's Homepage
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

Edited by - SwePeso on 06/29/2006 14:40:56
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 06/28/2006 :  09:47:28  Show Profile
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.
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 07/20/2006 :  10:05:55  Show Profile
Does anybody have any more thoughts on this? I am almost to the point of having to open an issue with MS God forbid!
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/21/2006 :  05:49:40  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 07/21/2006 :  10:19:26  Show Profile
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'
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/24/2006 :  04:53:38  Show Profile  Send jen a Yahoo! Message
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...
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 07/24/2006 :  09:59:11  Show Profile
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.
Go to Top of Page

dwumpus
Starting Member

1 Posts

Posted - 07/24/2006 :  13:23:24  Show Profile
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.
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/24/2006 :  23:07:11  Show Profile  Send jen a Yahoo! Message
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...
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 07/26/2006 :  09:38:50  Show Profile
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.
Go to Top of Page

jagt
Starting Member

1 Posts

Posted - 07/27/2006 :  07:44:33  Show Profile
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
Go to Top of Page

mikenason
Starting Member

USA
13 Posts

Posted - 07/27/2006 :  11:53:47  Show Profile
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000