Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

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

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
svid
Asked:
svid
  • 7
  • 3
  • 3
  • +2
2 Solutions
 
SQL_StuCommented:
Have you made sure that the SERVER has access and can actually see the relevant folder?
0
 
svidAuthor Commented:
Yes. Also, for now, its actually the same server.
0
 
SQL_StuCommented:
How about checking that the file isn't open by another process?  Have you tried using a random file name?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
svidAuthor Commented:
Its not used. As mentioned earlier, all's fine when using a local file path.
0
 
SQL_StuCommented:
Have you tried using a different file name or not?  

0
 
svidAuthor Commented:
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
 
arbertCommented:
Are you sure that the SQL Agent account has permissions on the share?
0
 
svidAuthor Commented:
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
 
Jay ToopsCommented:
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
 
svidAuthor Commented:
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
 
arbertCommented:
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
 
svidAuthor Commented:
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
 
arbertCommented:
hmmm, if this was a domain admin account, you shouldn't get a permissions error....You still have some problems with permissions...
0
 
Jay ToopsCommented:
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
 
svidAuthor Commented:
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
 
electoralcommissionCommented:
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
 
Jay ToopsCommented:
Great!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now