Solved

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

Posted on 2004-09-01
17
617 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

758 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

19 Experts available now in Live!

Get 1:1 Help Now