October 27, 2009 at 3:58 pm
We have a SQL box whose SQL Agent is configured to run as a domain user. That domain user has read permissions on a remote file share. There is a SQL job that executes a procedure that reads data from this share.
It fails with an Access Denied to this share error message.
I have verified by logging in as the domain account that SQL Agent runs as that the account can in fact connect to the share and open files.
We got it to work by also granting read access to the server machine account that the SQL instance runs from, but I'm baffled why this is necessary.
What is going on here, and am I missing something obvious?
October 27, 2009 at 7:34 pm
How is access through the file share being accomplished? You mention SQL job. Are you using xp_cmdshell?
K. Brian Kelley
@kbriankelley
October 28, 2009 at 7:22 am
Sorry, I was a little light on details.
The SQL job calls a stored procedure. The stored procedure accesses the files on a file share via UNC. The stored proc does a bulk insert something like:
BULK INSERT myTable FROM '\\server\share\file.txt' WITH ...
October 28, 2009 at 9:51 am
The SQL Server Agent job is probably coming in to SQL Server as sa. If it's a SQL Server based login, it's going to execute the BULK INSERT under the context of the SQL Server service account when it hits the OS.
K. Brian Kelley
@kbriankelley
August 28, 2010 at 4:57 am
K. Brian Kelley (10/27/2009)
How is access through the file share being accomplished? You mention SQL job. Are you using xp_cmdshell?
Hi Brian
I am having the same problem and i am using xp_cmdsheel from within SP which is executed by a job. Just wanted to ask What if I was using xp_cmdshell?
thanks in advance.
Vivek
Vivek Shukla - MCTS SQL Server 2008
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply