none
Does WITH EXECUTE AS work with BULK INSERT command involving UNC path?

    Question

  • We have a stored procedure that performs a BULK INSERT using a UNC path.  We get the access denied message as a result of the two hop security changes in SQL 2005.  One solution is to use a SQL login id to execute the stored procedure instead of a domain account.  The other solution is to make some Active Directory changes allowing account delegation, but I am not comfortable with the security ramifications.

    We want to have the domain account execute the stored procedure containing the BULK INSERT statement and then have the stored procedure execute using the security context of a local SQL Server login id.  We thought this could be accomplished by creating the SQL account, adding it to the bulkadmin server role and then creating the stored proc using WITH EXECUTE AS.

    When we run the proc we get the following error.

    Cannot bulk load because the file "\\xzy\abc\DBA.txt" could not be opened. Operating system error code 5(Access is enied.).

    I'm wondering if there is a security context issue in that it changes twice.  Once from the domain account to the id in the WITH EXECUTE AS statement and a second time when BULK INSERT is executed, where now the service account takes over.


    Any ideas?

    Thanks,   Dave

    • Moved by Tom PhillipsModerator Friday, January 29, 2010 2:41 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Thursday, January 28, 2010 11:19 PM

Answers

All replies

  • You will have to make sure that the SPNs are set correctly here. Please refer the following post for more details:
    http://blogs.technet.com/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    Friday, January 29, 2010 12:25 AM
  • Dave,

    To my best knowledge it is still going to execute as the service account here to access the external resource.  You could impersonate the context using SQLCLR, I've done that before and used a SqlBulkCopy object to push the data into SQL after using CLR impersonation to access the file:

    http://www.sqlclr.net/Examples/tabid/55/ArticleType/ArticleView/ArticleID/11/PageID/9/Default.aspx

    I can probably whip up a complete example for you if you'd like, but it might take me a bit.  It would be losely based on the above example and the example I wrote for exporting through SQLCLR to replace BCP and xp_cmdshell:

    http://www.sqlservercentral.com/articles/SQLCLR/65657/


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, January 29, 2010 1:55 AM
  • Amit,

    Thanks for the link.  It contains some good information that I will save for future reference, but I do not wish to use account delegation because of security concerns with this approach.  My preference is to use SQL Server authentication for BULK INSERT and I do not believe SPNs pertain to this method.

    Dave
    Friday, January 29, 2010 3:03 AM
  • Hi Jonathan,

    Good to hear from you.  I hope things are well.  I hate to admit it, but I don't know SQLCLR.  It's on my list, but I've yet to start working with it.  I may end up asking the developer to use SQL Server Authentication since I know this will work as long as EXECUTE AS is not involved.  If I can't figure out why EXECUTE AS is not working by tomorrow I'll give MS a call to get an explanation.

    Unrelated, our HP/EMC SAN evaluations are wrapping up.  We've seen some interesting test results.  HP storage pooling outperforms EMC metaluns with Update Statistics, ties EMC with DBCC CHECKDBs and gets beat by EMC with reindexing, with the exception of a test on a 800GB+ database.  SQLIO tests still need to be completed on the HP.  We've asked the vendors to study their monitoring tool stats and explain why they got beat in different areas.  We should have some good answers next week.  Thanks again for the help.

    Have a nice weekend.

    Dave
    Friday, January 29, 2010 3:49 AM
  • A few details would be helpful here:
    1. Location of the client issuing the Bulk Insert, SQL instance and the source file. Are they three different machines?
    2. Is the source file on a UNC path?
    3. Is the account being used to execute the bulk insert a sysadmin on the box?

    Jon is right. If you use a SQL Authenticated user, then the service account would access the external resource.

    If we are doing a double hop here, then it doesn't really matter if you are using the SQL Authentication or Windows Authentication, you would needs to have the FIPS set for the UNC share in case you are using one. If Kereberos is used in the environment?


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    Friday, January 29, 2010 7:22 PM
  • Hi Amit,

    1.  The client can be any machine outside of the database server.  In one of my test cases the client is my PC.  There are three machines involved, the client, the file server and the database server.  However, I have also repeated the test with only two machines, the client and the database server.  In that test the client contained the file.  Both tests produced the same results.

    2.  The BULK INSERT contains a UNC referencing the location of the file server.

    3.  The login id that invokes the stored procedure is not a sysadmin within SQL Server or a local admin on the database server, neither is the account referenced in the EXECUTE AS statement.  The login id that invokes the stored procedure is a local admin on the file server, but that shouldn't come into play since its credentials would not be used when utilizing the EXECUTE AS.  The SQL Server service account is also a local admin on the file server.

    SQL Authenitication works if that account inokes the stored procedure directly and no EXECUTE AS statement exists.  However, adding EXECUTE AS and using that same SQL account in the EXECUTE AS will not work for the reasons state in the previous post.

    Kerberos is used, but I don't want to use account delegation out of security concerns.  What's FIPS?

    Thanks,   Dave

    Saturday, January 30, 2010 9:00 PM
  • Dave,

    Have you considered using SSIS?

    Excellent error tracking/logging, top performance.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server 2012



    Saturday, January 30, 2010 9:46 PM
  • Thanks for clarifying the scenario with the answers. :)

    The behavior is different when using a SQL Authenticated user versus a Domain Account. The reason for this is when you have Kerberos in your environment. The Kerberos ticket of the account calling the bulk insert can only be forwarded if the delegation rights are granted to the account and a SPN is correctly set for the File Share.

    Otherwise, you would have to use a SQL Authenticated user to avoid the double hop scenario when 3 machines are involved. If you capture a Process Monitor trace on the file server, you will notice that that account requesting access to the file is Anonymous.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    Tuesday, February 02, 2010 1:05 PM