Post reply

Bulk Insert and (Access is denied)

  • March 20, 2006 at 2:45 pm

    #168183

    I have a stored procedure in SQL2005 where I am inserting records from a text file that is located on machine other than the machine with the SQL2005 instance.  I am using UNC to point to the data in the form of:

    \\remotepc\c\fr.txt.  When the procedure is executed from the machine with the SQL instance, it works correctly.  When attempting to execute this procedure from another machine (through the Management Studio) it fails with the error: 

    "\\remotepc\c\fr.txt" could not be opened. Operating system error code 5(Access is denied.).

    The RemotePC has permissions set to everyone in the domain as well as specific rights to administrators.  In addition, the SQL services is running as administrator.  I have connected through Windows Authentication as well as through SQL authentication.  I have made the permission as wide open as possible in the hopes of "stumbling upon" the right solutions. 

    I was able to make this work approach work in SQL2000.

    Any help would be greatly appreciated.

    Gary

  • March 21, 2006 at 4:25 am

    #627824

    The service running as a local administrator won't help.

    Grant access on the share and the folder to the *domain* account the SQL Server is running under.

    And please, lock it all down agan once you've got it working

  • March 21, 2006 at 10:13 am

    #627935

    Thank for the reply.  I currently running my instance as my domain\administrator account.  After re-running my test, if I connect to the database with through SQL Authentication and I give permission to the domain\administrator account, the Stored Procedure works as expected. 

    But, (and there is always a but), when I try to use Window Authentication, I get the same (access is denied) result.  I have two questions.  When using Windows authentication, who does "SQL" impersonate for network access in SQL2005?  And the more important question is, how do I make this work using Windows Authentication(WA)? 

    BTW, My WA account has wide open rights from the SQL Server perspective.

    Also, BTW, this is a totally isolated test environment and I will be implementing the appropriate lock downs upon implementation (but I appreciate the words of security).

    Again, thanks for the support.

     

  • March 22, 2006 at 4:13 am

    #628045

    Remove the account from Domain Administrators, grant access on the share and the folder to the account the SQL Server is running under.

    Trust me, and microsoft, this will work and prove that domain admins is not necessary.

    ***

    The other issue is who can use the SQL Server Agent to access shares.

    If the connecting user is a Sysadmin, it uses the Agent account to authenticate.

    If the connecting user is not a sysadmin, you can add them to the SQLAgentUserRole, OR you can set EXECUTE AS in the procedure, OR you can configure a proxy account for the Agent.

    ***

    However, if this issue is for a single process I would not grant the users themselves any access, I would utilise the EXECUTE AS feature in the procedure, and grant the users EXECUTe on the procedure.

  • March 29, 2006 at 7:43 am

    #629485

    First, I would like to thank you for your help.  You are getting me on the right track.  Second, I apologize for taking so long to respond but I have been out for the last week. And lastly, I still have a problem.

    I have removed my SQL Server Account from domain administrators and have implemented its own domain account.  I have granted access to the share and folder on the remote machine for this newly created account. 

    I have created a stored procedure with the code exec (@execstring) as login = 'bulkinsert' where execstrings is the bulk insert statement referring to a remote txt file using UNC. 

    I created a server login named bulkinsert and a server login name gary.  The bulkinsert server role privileges include BulkAdmin and SysAdmin.  The gary server role is BulkAdmin.  I have added the securables to gary to grant permission to impersonate bulkinsert and gave gary Execute rights to the stored procedure.

    While in SQL Server Management Studio (SSMS) on the SQL box, I connect to the server using the SQL authenticated account gary. I run the stored procedure and it works as expected. (...and there was much rejoicing!)

    I then ran the same test using Windows authentication connection on the SQL box.  I granted is the same permissions as the gary login and, again, it worked as expected.

    When I tried to do this same test on a remote machine using SQL Authentication through the gary login, it worked as expected, but, (and as I have said, there is always a but).  When I used the Windows Authenticated login, I received the dreaded (Access is denied) message.

    What do I need to do differently to allow the remote computer to use Windows authentication.  I know it has to do with granting access to the share and folder but I don't know what network account the service is using when the stored procedure is running.  Also, in your last post you talked about SQL Server Agent, but I did not understand how this applied to my situation.

    Thanks for the help.

    Gary

  • March 21, 2007 at 4:15 am

    #696106

    Hi,

    Did anyone ever manage to find a solution to this?

    I'm getting exactly the same results as Gary. It seems to be related to the fact that SQL Server 2005 will impersonate the calling domain account when it tries to access the remote file we are trying to bulk in.

    If you run the SP using a SQL login (remotely or locally) it works perfectly, also works locally with Windows Authentication but as soon as you try to call it remotely using WA it fails every time.

    I've tried setting the domain account to be trusted for delegation but still can't get it to work. Any ideas?

    Thanks

    Martyn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply