The double hop issue is something that comes up a number of times in support questions when people are using the Business Data Catalog with MS SQL Server, and SharePoint and SQL are on separate servers.
There are a few solutions to this:
1, Implement Kerberos
2, Use Single Sign On
3, Use RevertToSelf
We’ll be stepping through how to use BDC Meta Man to configure RevertToSelf.
1, When you have your entities configured within BDC Meta Man it is time to change the BDC Authentication Type. In the bottom left tree view right click on the root tree node and select ‘Edit…’ from the context menu that appears
2, When the Lob System settings form opens up change the Authentication Mode to RevertToSelf
3, You can then click the Save button, and generate your application definition file as normal and import it into SharePoint.
4, When you are trying to use this BDC data within SharePoint, as you are using RevertToSelf – SharePoint will try to connect to SQL Server as the Identity that the Application Pool is running under that the web application is using. To check which account this is, open IIS and expand the application pools.
This account will need permissions to read the data in your SQL Server database so make sure you grant the necessary access rights.
If you still need to secure the data to certain users you can do that through the Shared Service Provider by setting the BDC permissions to specific BDC applications or entities.