Description

Microsoft SQL 2005 Bulk Insert – Kerberos double hop – and Operating system error code 5 (Access is denied)
After upgrading Microsoft SQL Server 2000 SP4 to Microsoft SQL 2005 SP2, a stored procedure with
a bulk insert statement, started to throw an error.

Error

Cannot bulk load because the file "\\ServerA\NetworkShare\Import\20080315.csv" could not be opened. Operating system error code 5(Access is denied.).

TSQL

The t-sql code that caused the problem:
declare @ImportFile as varchar(255)
set @ImportFile = ‘\\ServerA\NetworkShare\Import\20080315.csv‘
exec (‘bulk insert dbo.ImportTable from ”’ + @ImportFile + ”’ WITH (FIELDTERMINATOR = ”,”, ROWTERMINATOR = ”\n”)’)

Infrastructure

To understand the error, you must first understand the environment infrastructure.

ServerA

ServerB

Active Directory Server

Accounts and Rights

Problem Cause

A file on a network share on ServerA is processed by a .NET 2.0 Windows Service on Server A with Identity (DOMAIN\srvaWindowsService). During processing a stored procedure on Server B is called with parameter ‘\\ServerA\NetworkShare\Import\20080315.csv‘ with Windows Integrated Security. The stored procedure tries to bulk inserts the data in the database. In SQL Server 2000 this processing will succeed, because SQL Server 2000 will automaticly delegate the call to the network share on behave of the Active Directory account (DOMAIN\srvaWindowsService), so the call succeeds. Microsoft considered this a security hole and Microsoft SQL Server 2005 will not automaticly delegate control.

Rich Baumet, explains on a MSDN forum
If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.

Solution

After following the steps on the MSDN Forum http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1 it still did not work.
This problem was, that after restarting the Microsoft SQL Server 2005 instance service, the SPN disappeared from active directory. After some testing we found the solution that worked for us.
Add the Active Directory account DOMAIN\srvaSQLServer to the domain admin group.
Restart the Microsoft SQL Server 2005 instance service.

After restarting the service you should see a new Service Principal Name in Active directory:
In the Microsft Windows 2003 support tools, you can found the executable setspn.exe
Execute setspn -L DOMAIN\srvaSQLServer

Registered ServicePrincipalNames for CN=srvaSQLServer,OU=Users,DC=domain,DC=corp:
MSSQLSvc/ServerB.domain.corp:1433

Follow the step 4 on the MSDN Forum
(Now that we created 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.There should be a DELEGATION tab there On the Delegation tab select "Trust this user for delegation to any service (Kerberos only)

This step must be repeated for ServerB (go to active directory, select server ServerB, go to the tab delegation and select "Trust this user for delegation to any service (Kerberos only)

After following this steps it still did not work!!!
This was caused by the caching of the Keberos tickets. During the bulk insert error the ServerA en ServerB, got a Keberos tickets that denied access to the network share. These tickets are cached. Clear the caching (purchase tickets) with the kerbtray.exe tool found in de the Microsft Windows 2003 Resource Kit

Additional Information