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 (
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
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?
- File Server Security Event Log Entry
- Service Account Delegation
- Service Account SPNs
- SQL Server #1 Computer Account Delegation
- File Server #2 Computer Account SPNs
- Group Policy Objects
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.