April 6, 2005 at 7:33 am
Hi There,
Im having a problem when I try to run a DTS package which uses Bulk Inserts with the Source File on a share on another server.
The SQL Server machine has access to it and can open/read/write to the share.
When the package runs and the datasource is of the type '\\someserver\somefolder\somefile' it fails on the Bulk Insert saying there is an operating system error and that Access is Denied.
From within the same DTS package I can execute a ActiveX Script that can access the same file and read from it ok.
anyone have any idea's as to what is happening here.
H.
April 7, 2005 at 1:07 am
If the problem you are describing is that your DTS package runs OK when executed in an interactive session but fails when run via a SQLAgent scheduled session then check what account SQLAgent runs under - if the service is running under Local System change the service to use a Domain account and ensure the Domain Account is granted appropriate access to the network share and relevant NTFS based files located on the share. You will also need to set your SQL Server security to use Integrated or Mixed and grant the SQL Agent Domain Account relevant access to the required SQL databases.
April 7, 2005 at 6:04 am
Another, sometimes easier solution, is to allow access to the share by granting permissions to the Machine Account of the SQL Server via the active directory. This will allow you leave the SQLAgent Account running under the Local System Account. Often times this is a more secure option than opening up SQL Agent to have rights on all domain members.
-Mike Gercevich
April 7, 2005 at 10:50 am
Seems like there was an issue when using mapped drives in DTS Pkgs that are then run via a Job.
See KB article - INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;269074
- Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply