System Error: Cannot bulk load because the file "XYZ.txt" could not be opened. Operating system error code 1311(There are currently no logon servers available to service the logon request.)
I have a stored procedure in SQL Server 2008 R2 which is using Bulk Insert command to load data from txt files into SQL Server tables. These files are on a shared folder on a drive located on different domain. I have full access to the drive. I tried copying files to different directory on that drive, moving files, deleting files and everything works.
When I execute the stored procedure from a ssms session from local computer it works like a champ. It is able to open the files on shared drive, read it and load the data into the SQL Server tables without any issue. When I call the stored procedure from a SQL Server Agent job, it throws this error.
SQL Server Agent is using the account which is very powerful with lot more permissions than mine. But the job fails.
To find a workaround I created a SSIS package which calls the stored procedure from "Execute SQL Task". It uses Windows authentication to connect to the database. I tried executing the package and it ran successfully. It is able to upload the data from txt to table.
So, then I created a SQL Server user with my account details, and then used that credentials to create a proxy with ssis sub system. I then scheduled the job to execute the step with newly created proxy to see if it can upload the data. But it failed with the same error.
I am confused what am I doing wrong..? I even added myself to bulkdmin role and ran the job again with no success.
I'll appreciate if someone can help.
Thanks.
