Search code examples
sql-serverssmsbulkinsertimpersonation

Bulk Insert failed when executed from remote client but success on local

Please find the diagram as below for my issue:

enter image description here

I have 3 servers in the same domain, there is a SQL Server instance A (it's windows service run under domain\User1), In this instance, we have a Stored Procedure used for BULK INSERT a text file from a network shared folder in server C, the domain\User1 has full permissions on this folder.

My issue is: The Stored Procedure runs ok (green arrow) when connecting by SSMS in its (server A). But it failed when I change to SSMS in server B (log in by the same domain\User1 to the same Instance A). The error is "Access denied" to the text file (red arrow). Does the client have a role in this? I think the client does not matter, the file reading is done from the server (by the user that run Instance A service)

Note: If I connect Instance A from SSMS B with SQL Logon User (not windows account), the stored procedure works fine.

Could anyone give me some advice and sorry for my bad English

Solution

  • This is just a link answer but hopefully it helps.

    BTW I commend you for taking the time to analyse the issue to the extent of drawing a diagram. This is far higher quality than most questions on here.

    I believe you are running into a double hop issue. I searched everywhere for the BULK INSERT permission model and finally found this https://dba.stackexchange.com/questions/189676/why-is-bulk-insert-considered-dangerous

    which says this about using BULK INSERT:

    When accessing SQL Server via a Windows Login, that Windows account will be impersonated (even if you switch the security context using EXECUTE AS LOGIN='...') for doing the file system access

    and this

    when accessing SQL Server via a SQL Server Login, then the external access is done in the context of the SQL Server service account

    When you have issues with windows authentication and there is three servers and impersonation, it's often a double hop issue.

    This may help you with that:

    https://dba.stackexchange.com/questions/44524/bulk-insert-through-network

    Which in turn references this:

    https://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied/