Sql-server – Configure unconstrained delegation for BULK INSERT

authenticationavailability-groupsbulk-insertsql serversql-server-2016

I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a Windows Server 2016 File Server Failover Cluster, but I get the following error:

Msg 4861, Level 16, State 1
Cannot bulk load because the file "\nas2.my.domain\Microsoft SQL Server 2016 Enterprise\test.txt" could not be opened. Operating system error code 5(Access is denied.).

This will occur regardless if I use the active node name (nas2.my.domain) or the failover cluster listener (nas.my.domain).

After looking around I found out this was due to the SQL Server being unable to impersonate the user account I am connected with due to nuances with BULK INSERT.

If you connect to the SQL Server using Windows Authentication, the SQL Server service account attempts to impersonate your user account when connecting to the file server. If you connect using SQL Server authentication, it will connect to the file server as the SQL Server service account.

If delegation and impersonation are not configured properly (the default state), the SQL Server service will not be able to impersonate your user account and will fall back to trying to connect to the file server as an anonymous user.

This can be confirmed by looking through the security event log on the file server. These facts along with a guide on configuring unconstrained and constrained delegation is documented in these links:

I've tried following the instructions in thesqldude's guide but it still isnt working.

The database I am trying to BULK INSERT to is not part of the availability group so only the MSSQL1 node should be relevant. The File Server was active on the NAS2 node. Checking the event log on the file server does show that it is still suffering from this problem and the SQL Server is trying to authenticate to the file server as an anonymous user rather than impersonating my user account.

Does anyone know what is going wrong? Or if something changed in SQL Server 2016 to make these guides obsolete?

I can confirm that this GPO has been applied to MSSQL1 via gpresult.exe /R, and both the SQL and File Server nodes were rebooted afterwards to ensure any caches have been flushed.

Best Answer

Your configuration looks solid. SPNs and Delegation are configured as I would have done it myself.

The only thing which you haven't explained are the file/share permissions. This is where my solution steps in.

Solution 1 (Personal Account)

  1. Verify that your personal account is allowed to access the file on the file server's share
    • your personal account needs at least READ permissions at the share permission level or in most cases you might see EVERYONE has READ permission.
    • your personal account needs at least READ permissions at the file's permission level
    • Check permission inheritance at file level for that one file
      • Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.
    • Alternatively: Log in to the SQL Server with your personal account and connect to the share and open the file.
  2. Open SSMS with your personal account.
  3. Open up a connection to your SQL Server instance with Windows Authentication.
    • Verify you are connected to your instance with the SQL Server service account with the script you have used before.
  4. Perform the import with BULK INSERT

Solution 2 (SQL Server Service Account)

  1. Verify that the SQL Server service account is allowed to access the file on the file server's share
    • the SQL Server service account needs at least READ permissions at the share permission level or in most cases you might see that EVERYONE has READ permissions.
    • the SQL Server service account needs at least READ permissions at the file's permission level
    • Check permission inheritance at file level for that one file
      • Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.
    • Alternatively: Log in to the SQL Server with the SQL Server Service Account and connect to the share and open the file.
  2. Open SSMS with the SQL Server service account.
    • Runas: your_domain\SQL_Server_service_account
    • Provide password
  3. Open up a connection to your SQL Server with Windows Authentication.
    • Verify you are connected to your instance with the SQL Server service account with the script you have used before.
  4. Perform the import with BULK INSERT

BULK INSERT permissions

Security Account Delegation (Impersonation)

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

Reference: BULK INSERT (Transact-SQL)

Related Question