[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 714
  • Last Modified:

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.
0
SAIonline
Asked:
SAIonline
  • 5
  • 4
1 Solution
 
YveauCommented:
... here's a thought:
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 ...
0
 
SAIonlineAuthor Commented:
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.


0
 
YveauCommented:
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 ...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SAIonlineAuthor Commented:
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.
0
 
YveauCommented:
now it's getting exciting !!! :-)
Is the test environment part of the same domain, or is ti set up as a different domain altogether ?
0
 
SAIonlineAuthor Commented:
Different domain
0
 
YveauCommented:
... 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 ...
0
 
SAIonlineAuthor Commented:
I appreciate anything you can find, I cannot find any deviation in settings between the domains.
0
 
YveauCommented:
Try the delegation (that what is is really called) settings in AD.
As you might have noticed, I'm only a DBA, so this is a bit out f my league ... hope you find it though ...

Hope this helps ...
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now