March 17, 2009 at 4:45 pm
I am setting up a SQL job that runs a stored procedure. The SP works fine if I run it directly under a domain admin account, but fails when run from the job. The job is set to run under 'NT AUTHORITY\NETWORK SERVICE' and I've given all the folder permissions it needs to access the share/folders/files, it is also a bulkadmin and sysadmin on the SQL server.
This SP pulls data from some flat files into tables, and it looks like it's getting the error on the BULK INSERT:
Cannot bulk load because the file "..." could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000](Error 4861).:cool:
Are there any other special permissions needed for a SQL job to access network files?
Here is the code that is failing:
BULK
INSERT [Table_Name]
FROM '\\server\share\folder\filename.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
March 17, 2009 at 11:35 pm
Please look into proxy accounts for SQL Servers.
http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 18, 2009 at 3:46 pm
Yeah, it wasn't actually the bulk copy command that I was running at the time, it was a BULK INSERT sql command (I edited my post late). When I set up the Proxy account, it still wouldn't run the BULK INSERT statement, so I changed it to this, for my tab-delimited file:
EXEC master..xp_cmdshell 'bcp "DataBaseName.dbo.TableName" IN "C:\temp\filename.txt" -T -c'
And this seemed to work with the Proxy account. Thanks 😀
March 18, 2009 at 4:05 pm
ahager (3/18/2009)
Yeah, it wasn't actually the bulk copy command that I was running at the time, it was a BULK INSERT sql command (I edited my post late). When I set up the Proxy account, it still wouldn't run the BULK INSERT statement, so I changed it to this, for my tab-delimited file:EXEC master..xp_cmdshell 'bcp "DataBaseName.dbo.TableName" IN "C:\temp\filename.txt" -T -c'
And this seemed to work with the Proxy account. Thanks 😀
Hmmm the other way might be causing issue b/c the account doesn't have bulkadmin rights on the server? Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 18, 2009 at 4:08 pm
Actually, it did have bulkadmin rights checked. From what I've read online, BULK INSERT for remote files just doesn't work with SQL jobs, b/c although it makes hidden calls to bcp, it runs under the SQL system service account, that doesn't have remote access permissions.
That is my understanding, if anybody else knows this better, please enlighten me. This was such a headache.
March 23, 2009 at 3:31 pm
Is the system is 64bit?
---------------------------------------------------
Thanks,
Satheesh.
March 24, 2009 at 11:10 am
it is indeed.
March 31, 2009 at 4:13 am
The package which you had created is in 32-bit mode since your server is in 62-bit it was unable to run a 32-bit package. You have to use the dtexec utillity to run the 32-bit package in 64-bit.
Refer the sites, http://msdn.microsoft.com/en-us/library/ms162810.aspx
http://msdn.microsoft.com/en-us/library/ms138023.aspx
Let me know if you still have problem.
Thanks,
Satheesh.
---------------------------------------------------
Thanks,
Satheesh.
March 31, 2009 at 10:36 am
The issue I was having was related to this piece:
BULK
INSERT [Table_Name]
FROM '\\server\share\folder\filename.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
This was not part of a package. The proxy account running the SQL job did not have the necessary permissions to execute this across the network, so I switched it to:
EXEC master..xp_cmdshell 'bcp "DataBaseName.dbo.TableName" IN "\\server\share\folder\filename.txt" -T -c'
which worked out fine. So, this has actually already been resolved. Thanks though.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply