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

Initial Setup

I have the MSSQL service set to run as a domain user account, EXAMPLE\svc_mssql, and have added that user to a security group that has read access to the relevant DFS share (eg. \\example.org\myshare\data\path\to\mydata.csv). This allows users connecting to the database via SQL Authentication to read the files on the share successfully as there's no Kerberos double-hop issues present:

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\data\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\data\path\to\mydata.fmt'
7> ) AS f;
8> GO
auth_type  NumLines
---------- -----------
SQL                 73

(1 rows affected)

Kerberos

I have manually registered the SPNs for MSSQLSvc in AD and they do show up properly on that service account:

C:\Temp>setspn -Q MSSQLSvc/DBSERVER.example.org
Checking domain DC=example,DC=org
CN=svc_mssql,OU=ServiceAccounts,DC=example,DC=org
        MSSQLSvc/DBSERVER.example.org
        MSSQLSvc/DBSERVER.example.org:1433

Existing SPN found!

This allows me to successfully connect to SQL Server using kerberos authentication:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type;
2> GO
auth_type
----------
KERBEROS

(1 rows affected)

Unconstrained Delegation

If I set the service account to be trusted for delegation (ie. unconstrained delegation) like so:

PS C:\Temp> Get-ADUser svc_mssql -Properties TrustedForDelegation,TrustedToAuthForDelegation `
>>   | Select-Object Name,TrustedForDelegation,TrustedToAuthForDelegation

Name      TrustedForDelegation TrustedToAuthForDelegation
----      -------------------- --------------------------
svc_mssql                 True                      False

Then everything works fine and dandy:

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\data\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\data\path\to\mydata.fmt'
7> ) AS f;
8> GO
auth_type  NumLines
---------- -----------
KERBEROS            73

(1 rows affected)

Packet Trace – Unconstrained Delegation

After much trial and error and trying to surmise/guess what is needed for constrained delegation, I installed wireshark on the database server and grabbed a packet trace from the working unconstrained setup. I'm not a networking expert, but this is what (I think) I see in that trace:

1. TGS-REQ/REP for cifs/DOMAINCONTROLLER01.example.org
2. TGS-REQ/REP for krbtgt/EXAMPLE.ORG
3. SMB tree connect request for \\DOMAINCONTROLLER01.example.org\IPC$
4. SMB Ioctl FSCTL_DFS_GET_REFERRALS for \example.org\myshare
5. TGS-REQ/REP for cifs/FILESERVER01
6. SMB tree connect request for \\FILESERVER01\IPC$
7. SMB Ioctl FSCTL_DFS_GET_REFERRALS for \FILESERVER01\myshare
8. TGS-REQ/REP for cifs/FILESERVER02
9. SMB tree connect request for \\FILESERVER02\IPC$
10. SMB Ioctl FSCTL_DFS_GET_REFERRALS for \FILESERVER01\myshare\data
11. TGS-REQ/REP for cifs/NASCLUSTER01
12. SMB tree connect request for \\NASCLUSTER01\Data
13. More SMB traffic to actually read the files…

I can see the need to proxy/impersonate to the two cifs/FILESERVER SPNs since they are the underlying nodes for the DFS namespace. After some research, I can also understand the cifs/NASCLUSTER SPN, as that is where the file servers are actually storing the data.

The first two requests though, I do not fully understand. I'm guessing the first one cifs/DOMAINCONTROLLER01.example.org is so that SQL Server can look up the nodes in DFS namespace that is hosting the requested fileshare. If so, I guess I need the service account to be allowed to delegate to cifs on ALL domain controllers, right?

That leaves the krbtgt/EXAMPLE.ORG. I don't understand what this is doing. Why is it getting a TGT? Am I misreading the trace? I see it is a tgs-req with a msg-type of krb-tgs-req (12) — same as the others. Is this protocol transition? If yes, why is that happening in this scenario?

Update

So, I tried (again) to set up svc_mssql with constrained delegation for the following SPNs:

1. cifs/FILESERVER01
2. cifs/FILESERVER02
3. cifs/NASCLUSTER
4. cifs/DOMAINCONTROLLER01

Looking at the packet trace, I see krb-tgs-req (12) for cifs/FILESERVER01 which gets a krb-error (30) with an error-code of eRR-BADOPTION (13). The original krb-tgs-req had kdc-options = 40830000, which wireshark decodes as:

kdc-options: 40830000
    0... .... = reserved: False
    .1.. .... = forwardable: True
    ..0. .... = forwarded: False
    ...0 .... = proxiable: False
    .... 0... = proxy: False
    .... .0.. = allow-postdate: False
    .... ..0. = postdated: False
    .... ...0 = unused7: False
    1... .... = renewable: True
    .0.. .... = unused9: False
    ..0. .... = unused10: False
    ...0 .... = opt-hardware-auth: False
    .... 0... = unused12: False
    .... .0.. = unused13: False
    .... ..1. = constrained-delegation: True
    .... ...1 = canonicalize: True
    0... .... = request-anonymous: False
    .0.. .... = unused17: False
    ..0. .... = unused18: False
    ...0 .... = unused19: False
    .... 0... = unused20: False
    .... .0.. = unused21: False
    .... ..0. = unused22: False
    .... ...0 = unused23: False
    0... .... = unused24: False
    .0.. .... = unused25: False
    ..0. .... = disable-transited-check: False
    ...0 .... = renewable-ok: False
    .... 0... = enc-tkt-in-skey: False
    .... .0.. = unused29: False
    .... ..0. = renew: False
    .... ...0 = validate: False

I compared the options to the packets in the unconstrained delegation and the only difference is the constrained-delegation bit is flipped — as expected. What am I doing wrong?

Help!?!?!?