Solved

DTS Error "Access is Denied" in Bulk Insert using a UNC

Posted on 2004-09-01
17
619 Views
Last Modified: 2010-05-18
My DTS package containing a Bulk Insert task runs fine when the source is a local path (c:\temp\Data\1.txt). However when I change it to a UNC path (referencing the same server) as \\server\share\temp\data\1.txt, I get  the following error:

"Could not bulk insert because file '\\server\share\temp\Data\1.txt' could not be opened. Operating system error code 5(Access is denied.)"

I guess this is a Permissions error, but I am not sure what exactly needs to be done. I am using SQL Server Authentication (even tried "sa" user) and for this server, "Everyone" has full control on the Data folder.

Could someone point me to the resolution?
Thanks
0
Comment
Question by:svid
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 11953745
Have you made sure that the SERVER has access and can actually see the relevant folder?
0
 
LVL 5

Author Comment

by:svid
ID: 11953851
Yes. Also, for now, its actually the same server.
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 11953930
How about checking that the file isn't open by another process?  Have you tried using a random file name?
0
 
LVL 5

Author Comment

by:svid
ID: 11954373
Its not used. As mentioned earlier, all's fine when using a local file path.
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 11954411
Have you tried using a different file name or not?  

0
 
LVL 5

Author Comment

by:svid
ID: 11954462
The problem is not in the file. Its with the permissions. All files have the problem.

Any other pointers? I don't think we are on the right track.....
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 50 total points
ID: 11955245
Are you sure that the SQL Agent account has permissions on the share?
0
 
LVL 5

Author Comment

by:svid
ID: 11955475
As this is a test server, it has "EveryOne - fullcontrol" as permissions for the share. (The SQL Agent service is running under System Account).

Is there something else I should be checking?
Thanks!
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 10

Accepted Solution

by:
Jay Toops earned 75 total points
ID: 11955734
The Problem is this THE agent process is running under a LOCAL account
this LOCAL account cant get access to a DOMAIN UNC folder

you need to go to services, SQL Server Agent
and change the id that is used for agent to a DOMAIN user (preferrably a domain
ADMIN account setup for this purpose)
and grant that account access to that folder.

Jay
0
 
LVL 5

Author Comment

by:svid
ID: 11955973
I setup a domain admin account, changed the user for the Agent service and granted access(full control) to the folder and the share. (This particular server is NT, running SQL Server 7)

And I still get the same error.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11956241
Hmm, I'm at a loss--if it is a domain account and has full rights, it should work.

If you go to the server and do Start/Run and paste the UNC path in, does it pull up an explorer window?  have you tried to use the admin share instead of the folder name just to check (\\servername\drive$\folder\filenam.txt)?
0
 
LVL 5

Author Comment

by:svid
ID: 11956288
The file is there when I paste the unc path.
When using the admin share for the c drive (this is where the file is) I get

"Could not bulk insert because file '\\server\C$\temp\Data\1.txt' could not be opened. Operating system error code 5(Access is denied.)"
0
 
LVL 34

Expert Comment

by:arbert
ID: 11956324
hmmm, if this was a domain admin account, you shouldn't get a permissions error....You still have some problems with permissions...
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11956464
TRY these in order
a) Did you restart the AGENT service?
b) are u running this from the scheduler? or are u running it yourself?
c) if in the scheduler check the "OWNER" in the properties of the task to make sure
its trying to execute as the domain account.
d) Try creating a new share NOT a C$ share (thats an admin share and may possiblly
have some restrictions placed on it by DOMAIN System admins (depending how
draconian they are)
IIF ALL ESLE fails add an EXECUTE PROCESS Task
and create a BAT file to copy the file locally.

Good Luck

Jay
0
 
LVL 5

Author Comment

by:svid
ID: 11956630
Jay,

a. I restarted the Agent
b. I am running the process myself (eventually it will be running from a VB app)
c. N/A
d. It has always been a non-admin share. I tried using an admin-share and it did not work, so reverted to the original

I am under the gun to get this done ASAP so, I will try this later (BAT file)

The way I got around this problem was to use another test server which has seemingly the same config, but works fine. So I am still unsure of the problem but for now have got it going.

Thanks for your help.
0
 

Expert Comment

by:electoralcommission
ID: 12680647
a big thanks to jltoops

been trying to find a solution to this problem for a week now, and your solution was the one

cheers
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12695838
Great!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now