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

Microsoft Windows Server 2003 Standard SP1

Server is member of an OU with the High Secure Template C:\WINDOWS\security\templates\ hisecws.inf applied to it.

Server contains a custom made .NET 2.0 WindowsService with Identity DOMAIN\srvaWindowService

.NET 2.0 WindowsService has a database connectionstring: "Data Source=ServerB;Initial Catalog=DBTEST;Integrated Security=True; "

Server contains a Network Share ‘\\ServerA\NetworkShare\’

 

ServerB

Microsoft Windows Server 2003 Standard SP1

Microsoft SQL Server 2005 SP2

Microsoft SQL Server 2005 named instance (ServerB\InstanceName) on static port 1433

Microsoft SQL Server 2005: Only Shared Memory en TCP/IP enabled (Named Pipes are Disabled)

Microsoft SQL Server 2005 Server authentication: Windows Authentication mode

The Microsoft SQL Server 2005 named instance identity is a Active Directory account DOMAIN\srvaSQLServer

Microsoft SQL Server 2005 instance (ServerB\InstanceName) contains a database (DBTEST) with Compatibility level: SQL Server 2005 (90)

 

Active Directory Server

Microsoft Windows Server 2003 Standard SP1

Server is member of an OU with the High Secure Template C:\WINDOWS\security\templates\hisecdc.inf applied to it.

 

Accounts and Rights

Active Directory account DOMAIN\srvaSQLServer is local admin on ServerB (causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer has no Service Principal Name for the MS SQL Service on port 1433(causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer is not "Trusted for Delegation" (causes a problem, see solution)

Active Directory account DOMAIN\srvaSQLServer has "log on as a service" rights

The Active Directory account DOMAIN\srvaWindowService has modify rights to the networkshare ‘\\ServerA\NetworkShare\’

The Active Directory account DOMAIN\srvaWindowService has modify rights to the filesystem networkshare ‘\\ServerA\NetworkShare\’

Active Directory account DOMAIN\srvaWindowService has "log on as a service" rights.

Active Directory account DOMAIN\srvaWindowService is mapped to a login (DOMAIN\srvaWindowService) on the Microsft SQL Server 2005 instance (ServerB\InstanceName)

The Microsoft SQL Server 2005 Login DOMAIN\srvaWindowService is member of the server role "bulk admin"

The Microsoft SQL Server 2005 Login DOMAIN\srvaWindowService is mapped to a DBTEST database user DOMAIN\srvaWindowService

The database user DOMAIN\srvaWindowService has db_datareader and db_datawriter rights on the database DBTEST.

ServerB is not trusted for delegation (causes a problem, see solution)

 

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

MSDN Forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1

This post started the solution for us, many tanks to Rich Baumet

MSDN Forum

http://forums.microsoft.com/msdn/showpost.aspx?postid=107193&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

This form ends with: After watching forums, etc. for almost a year regarding this issue, we ran a MS Support Request. After several hours of walking them through the issue, they said it was definitely a bug ( # 396489 ) in 2005 and would be fixed in 2008.
In our situation it was not a bug in Microsoft SQL Server 2005 SP2, but a configuration error in Active Directory

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.