1

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.
10
  • Does your account (the one you are connecting with) have access to the share? user name or password is incorrect is probably because it's coming up as ANONYMOUS. Check the SMB Server logs in Event Viewer on the file server, what username is coming up? Mar 9, 2023 at 2:11
  • @Charlieface - yes, my AD account does have access to the share. I'm the one who put the file on the share originally and I can still read the file. Why would it come up as ANONYMOUS? What does that even mean in this context? Will see if I can convince the admin to check the logs on the fileserver.
    – JoeNahmias
    Mar 9, 2023 at 11:59
  • It comes up as ANONYMOUS if Kerberos is failing for some reason. I'm thinking now that you need SPNs on the two file servers for the DFS namespace, instructions here learn.microsoft.com/en-us/troubleshoot/windows-server/… Mar 9, 2023 at 12:36
  • @Charlieface - That link describes modifying DFS to use FQDNs instead of simply NETBIOS names. I don't see any mention of SPNs. Also, please see my update above -- perhaps something else is going on as even a UNC path going directly to the fileserver does not work...
    – JoeNahmias
    Mar 9, 2023 at 13:58
  • Sorry bit tired :-) meant to say that it can't use Kerberos for DFS with the FQDN unless you use the FQDN. But I see now you have an update that it's still not working. Double-check delegation again: SQL Server domain account needs to have delegation for HOST/FILESERVER1.example.org for any authentication protocol. Double-check that SQL Server is actually running under that account. Double-check that your own account you are connecting from is not a "Protected User" for example a domain/enterprise admin. Mar 9, 2023 at 14:31

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.