May 13, 2008 at 12:58 pm
Hi All
One of the step in sql agent job is to execute the SSIS package to copy files from remote shared location to local server where sql server is installed. The account on which SQL agent runs as has access to remote shared location. However SSIS package always fails with following error:
An error occurred with the following error message: "Access to the path '\\sharedlocation\BCKTST105092008.csv' is denied.".
We have tried using proxies but same issue come with proxy also.
Please help
Thanks
May 22, 2008 at 12:36 pm
I'm having the same issue. I found this tidbit in the BOL, though:
From http://msdn.microsoft.com/en-us/library/ms175915.aspx :
Security Considerations
If a user uses a SQL Server login, the security profile of the SQL Server process account is used.
In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.
I haven't had a chance to examine this to see if it really is what's going on for me, but I thought I'd throw it out there.
May 23, 2008 at 4:05 am
The question is, does your SQL server account have access to the file? Because this is what will be accessing the file at runtime.
How to check:
a) Run the SSIS package from BIDS and check if everything is okey. If not, then try and work out why you cannot access the file
b) Run from the SQL server agent account and check the same
Hope this helps
~PD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply