BULK INSERT from DFS shared folder using Kerberos

Question

I am trying to enable my users to BULK INSERT / OPENROWSET() a CSV file that is stored on our DFS/cifs network shares.

I have MS SQL Server 2016 Standard Edition running as a domain account and that account has access to the desired fileshare. So, if I log in to MSSQL using SQL Authentication it is working.

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -U %DB_USERNAME% -P %DB_PASSWORD%
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
auth_type  NumLines
---------- -----------
SQL                 73

(1 rows affected)

However, when I connect to MSSQL from another machine using Kerberos / Windows Authentication, I receive the following error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).
1> SELECT CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type;
2> GO
auth_type
----------
KERBEROS

(1 rows affected)

Normally, if it's a Kerberos Double-Hop issue, I expect the error to be Access is denied. In this case, I've set up the MSSQLSvc/dbserver.example.org and MSSQLSvc/dbserver.example.org:1433 SPNs, verified that I'm connecting using Kerberos, and enabled Kerberos Constrained Delegation for the mssql domain account to the HOST/FILESERVER1 and HOST/FILESERVER2 SPNs.

Any ideas what this error, The user name or password is incorrect., could be about or how I can get more info from Windows or SQL Server?

Update #1

I tried to take DFS out of the equation by using a UNC path directly to one of the file servers, but get the same error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\FILESERVER1.example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\FILESERVER1.example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\FILESERVER1.example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).

Same result if I just use the netbios name for the file server instead of the FQDN (ie. \\FILESERVER1\myshare\path\to\mydata.csv)

Update #2

I enabled Keberos event logging on the SQL Server, ran the OPENROWSET() query again, and then found the following in the System Event Log:

Log Name:      System
Source:        Microsoft-Windows-Security-Kerberos
Date:          3/9/2023 12:47:56 PM
Event ID:      3
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      DBSERVER.example.org
Description:
A Kerberos error message was received:
 on logon session 
 Client Time: 
 Server Time: 17:47:56.0000 3/9/2023 Z
 Error Code: 0xd KDC_ERR_BADOPTION
 Extended Error: 0xc0000225 KLIN(0)
 Client Realm: 
 Client Name: 
 Server Realm: EXAMPLE.ORG
 Server Name: cifs/DOMAINCONTROLLER01.example.org
 Target Name: cifs/[email protected]
 Error Text: 
 File: onecore\ds\security\protocols\kerberos\client2\kerbtick.cxx
 Line: 1292
 Error Data is in record data.

Answer 1

So, we finally figured this out. As part of troubleshooting we enabled kerberos delegation on the machine account of the SQL Server, based on something we read on the internet. This (apparently) caused the system to attempt resource-based constrained delegation (RBCD), which was/is not configured. The real issue was therefore masked and we weren't able to make progress.

After resetting the machine account to no delegation, and confirming internally, we were able to see that both fileservers were backed by a NAS cluster which also uses kerberos authentication and thus also needed to be enabled for delegation from the service account. So, after adding delegations for all three cifs SPNs (2 fileservers, 1 nas cluster), we were able to read files from the share successfully.