Cannot access file in network drive

Question

Cannot access file in network drive

Chen Yongquan

Valued Member

Points: 61
More actions
December 21, 2005 at 1:53 am

#68056

Hi Guys ... I am trying to access a textfile on a network drive that has permission openned to "Everyone". I am trying to access the file and import the data into a table.
When I tried to execute it manually "Execute Package", it works fine and it will populate my table fine.
But when trying to schedule it using local permission, it fails and the error log shows "The job failed. The Job was invoked by Schedule 34 (License Refresh). The last step to run was step 1 (License Refresh)."
I have did a check on the job and found out that it fails at the step where it imports the textfile from the network drive.
I know it could be some permission settings I have to do with the SQL Server Agent. Could anyone please advice me on any good solution to my problem.
Thanks a lot.
Yongquan

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

You need to ensure that SQL Server Agent is running under an account that has access to the network drive you are trying to access. To check this, from Enterprise Manager, expand 'Management' and right-click on 'SQL Server Agent' - select Properties.

Modify the Service Startup Account as necessary to provide the required access.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chen Yongquan Valued Member Points: 61 More actions · Answer 2

do you meant that i need to use an existing account that has priviledge to access the network domain? I think I have an idea to solve it. I will try this out in office and see if it works ... Thanks Phil.

Chen Yongquan Valued Member Points: 61 More actions · Answer 3

hi dear Phil

I tried this morning but I got some error when using our office common domain login. I also tried mine own personal login but I keep hitting some login error.

May I know if there is anyway to find out what login access I can use to access the domain network from SQL Server?

For your info, I login my server using a local machine login ... like administrator followed by my password. Will this affect the access?

Chen Yongquan Valued Member Points: 61 More actions · Answer 4

Hi ppl out there,

if any expert out there know the solution to my current situation, can please reply to this thread.

Thanks a lot.

Aasif Chanandin Grasshopper Points: 21 More actions · Answer 5

The account you use should also have access rights to the file and it's directory. If the username running the DTS package cannot write to the fiel, then it will error out. Ask your network Admin what accounts have access to that file and then add the account that is running the DTS package to that list.

Chen Yongquan Valued Member Points: 61 More actions · Answer 6

So sorry Aasif,

When you say add the account that is running the DTS package to that list. Which account are you refering to? Is it my SQL Server agent account? Or my windows login account.

But when I go to the network drive (permission already opened to everyone for read/write permission) and try to open/view/edit file, it is possible. And when I am trying to execute the package manually, it is also possible. But just when I am trying to schedule it, then it fails.

When I view the Startup Service Account for my SQL Server Agent, it is set as System Account. Does this refers to my windows login account? -> my server administrator account.

Quite confuse in the settings.

Maybe based on my points here, you can advise me more on your solution. Thanks a lot

Cheers, Yongquan.

punniyakodi SSC Veteran Points: 274 More actions · Answer 7

Hi,

You can try this option, start SQLServerAgent and MSSQLSERVER services in your account domainname\username by rightclick on the service and goto properties->logon tab. The username should have the access permission to the network file.

Chen Yongquan Valued Member Points: 61 More actions · Answer 8

Hi kodi,

I had tried this way. Still not successfull.

punniyakodi SSC Veteran Points: 274 More actions · Answer 9

can u try this option, copy the source file to new file and try to access new one. one more option, restart the system and test it out.

Kodi

Chen Yongquan Valued Member Points: 61 More actions · Answer 10

Hi Kodi

I tried also using all type of file format. Still unable to work.

But when I tried to copy the file to local and schedule it, it will work

regards

Yongquan

Brian Willson Grasshopper Points: 16 More actions · Answer 11

Are you getting the data file from a mapped drive? Try using the UNC path. The server will probably not be able to see your mapped drives when you are not running it yourself.

Chen Yongquan Valued Member Points: 61 More actions · Answer 12

hi brian

what you meant by UNC path?

is it something like \\networkdrive\folder\file

had tried this also and is not working too.

Brian Willson Grasshopper Points: 16 More actions · Answer 13

Hi Chen, Yes that is what I meant - sorry it didn't work.

Chen Yongquan Valued Member Points: 61 More actions · Answer 14

Hi brian,

amzingly after trying so many times, it finally worked. Thanks all folks for helping out. realli very gratefull to all of u..