Bulk Insert Problm with SQL Server 2005
Trying to perform a bulk insert from a text file located on a shared folder on a remote computer. Recieving the error message Operating system error code 5(Access is denied.).
SQL Server 2005 running on Windows 2003 SP1 connecting to a Windows 2003 SP1 server with a shared folder.
SQL Server and SQL Agent start with the same domain account, this account has Full Control permissions to share.
In the security log of the server hosting the shared folder there are entries showing attempted connections from sqlserver$ then multiple anonymous logon attempts. This one has us stumped, thanks in advance.
SQL Server 2005 running on Windows 2003 SP1 connecting to a Windows 2003 SP1 server with a shared folder.
SQL Server and SQL Agent start with the same domain account, this account has Full Control permissions to share.
In the security log of the server hosting the shared folder there are entries showing attempted connections from sqlserver$ then multiple anonymous logon attempts. This one has us stumped, thanks in advance.
ASKER
Okay here is our test scenario and why we are confused, we log into the server using the account that sql server runs under. Then open SQL Mgmt Studio using Windows Authentication, New Query, enter BULK statement and voila, Access Denied.
Yeah, so that is what I pointed out in the hopping part.
Try creating a job that runs that SQL code for you, you won't be having this hopping 'nonsense' and my guess is that the job will run ...
Hope this helps ...
Try creating a job that runs that SQL code for you, you won't be having this hopping 'nonsense' and my guess is that the job will run ...
Hope this helps ...
ASKER
Sorry, yes, I understand that. However my DBA cannot run this as a SQL job, to attempt to alleviate this we have tried setting up delegation, but this has not been effective. The part that makes this interesting is that he has this working in our test envioronment and did not have us use delegation and runs under a similar configuration and service account.
now it's getting exciting !!! :-)
Is the test environment part of the same domain, or is ti set up as a different domain altogether ?
Is the test environment part of the same domain, or is ti set up as a different domain altogether ?
ASKER
Different domain
... same domain settings ??? That 'hopping' thing is a setting within a domain as far as I can remember. I'll have to check for you ...
ASKER
I appreciate anything you can find, I cannot find any deviation in settings between the domains.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You run this bulk insert statement as a domain user that is a member of the sysadmin server role. That way, you are entitled to enter the file system sub system and no proxy account is needed. However when trying to connect to the remote file server, an attempt is made to forward the credentials and that is not allowed in the domain, this is also known as hopping. That would result in an anonymous attempt to reach the remote file ... and would generate the error you mentioned.
If this is the case, try running the bulk insert as a job. That way it will run under the credentials of the SQL Server Agent account and that is a domain account with sufficient permissions. Make sure that the job owner is 'sa' and not some domain user.
Hope this helps ...