Here's the scenario that tripped us up during our SQL Server 2005 upgrade: 

Consider a stored procedure that executes a BULK INSERT command where the file in the FROM clause is located on a remote machine.  When executing this stored procedure using a trusted connection, i.e., Windows Authentication, the Windows account must be trusted for delegation in order for this stored procedure to be executed remotely.  However, based on my testing, the following scenarios will work:

1. Modify the stored procedure to BULK INSERT a local file, i.e., local to the SQL Server.  The stored procedure can be successfully executed either locally or remotely,
2. Execute the stored proceude that BULK INSERTs a remote file locally on the SQL Server machine.  In other words, the SQL Client and SQL Server machines must be one and the same.

For the most common client / server scenario (separate client and server machines with the BULK INSERT file located on a third server) the Windows account used to execute the stored procedure must be trusted for delegation. 

However, from a security perspective, using SQL authentication is probably the best solution ...