Solved

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

Posted on 2004-09-01
17
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

729 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