Resolving 'Operating system error code 5' with BULK INSERT - A different perspective

  • Article

PROBLEM DECRIPTION

While executing a BULK INSERT command from a remote connection the following error is reported:

Msg 4861, Level 16, State 1, Line 6
Cannot bulk load because the file "\\sqlbox\share\myText.txt" could not be opened. Operating system error code 5(Access is denied.).

Checklist to follow to resolve this issue

1. Add the login you are using to the Bulk Insert Administrators Server Role.

[Refer: https://msdn.microsoft.com/en-us/library/ms189934(SQL.90).aspx]

2. Make sure that you are using TCP/Kerberos (for delegation to work Kerberos must be used) - a possible workaround is to use SQL authentication instead:
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid

3. Check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here.

After installation on your workstation run the following from command prompt:

C:\Program Files\Support Tools> setspn -L serviceaccount

There should be at least two SPNs listed, because the following two SPNs for the service account must be present for delegation to properly function:
ServiceClass/Host:Port, where ServiceClass is the appropriate service class, Host is the name of the host computer, and Port is the port the service is running on.
ServiceClass/FQDN, where FQDN is the fully qualified domain name of the host computer.

4. If there is no SPN for the MSSQLSvc, create one. All we do is to use SETSPN to add the service by executing the following:

Setspn -A MSSQLSvc/Sqlbox.def.wxy.com:1433 Domain\sqlSvrSvcAccount

Setspn -A MSSQLSvc/Sqlbox:1433 Domain\sqlSvrSvcAccount

5. Make sure you don’t have duplicate SPNs. Refer https://support.microsoft.com/kb/321044.

6. Now that we created/verified that the SPN for the SQL Service, we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under. In the Delegation tab select "Trust this user for delegation to any service (Kerberos only)

7. Also make sure machine which hosts SQL Server is trusted for delegation.

 

Interestingly, even after verifying everything the BULK INSERT query was failing with the same error. I later discovered that there is a DNS alias configured.

8. If you have configured DNS alias (or, CNAME record) Make sure that you have separate SPNs for DNS alias.

    Note: You can check for DNS Alias or CNAME records using “nslookup” https://support.microsoft.com/kb/200525.

ROOT CAUSE AND FIX

If you have configured a DNS alias (or, CNAME record) ‘SQLBox.abc.xyz.com’ for SQLBox for which there is no corresponding SPN.

You need to add the following SPN(One corresponding to alias) to resolve 'Msg 4861, Level 16, State 1, Line 6' error:

Setspn -A MSSQLSvc/ SQLBox.abc.xyz.com:1433 domain\sqlSvrSvcAccount