Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-09-01
17
Medium Priority
?
641 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
Independent Software Vendors: 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!

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

963 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