I am experiencing the same issue in the following scenario.
One Web Front server
with SharePoint Server 2010
One SQL 2008 Server (here I have created an extra database
(Coffee) which I want to connect
to via an external
In SPD 2010:
- I create an external content
- For Authentication mode on the connection
in the external content type
I choose BDC Identity (meaning the service running the BDC will be the one connecting to the external datasource, not the current user, sort of running with elevated priveleges I guess). MS refers to this as RevertToSelf on technet.
- I create all operations and hit save.
- I create the external list and saves. Everything works fine.
I then hit the external list from the browser and ends up with the following: "Cannot connect to the LobSystem (External System)" when surfing from my client and "Access denied
by Business data
Connectivity" when surfing from the server.
If I look at the external content type in Central Administration under Set Permissions only one user (me, who is the Site Collection Administrator for the site that i connected to in SPD) has permissions.
the service account for the BCS Service application, thinking that guy must have permissions to execute, since he is the one authenticating the connection (see above).
Problem remains the same.
A look in the event viewer:
Could not open connection using 'data source=mydatasource;initial catalog=Coffee;integrated security=SSPI;pooling=true;persist security info=false;connect timeout=60' in App Domain '/LM/W3SVC/1068680523/ROOT-1-129114486771613688'. The full exception text is: Cannot open database "Coffee" requested by the login. The login failed.
Login failed for user 'mybcsserviceaccount'.
A look in the database:
mybcsserviceaccount has no permissions in my Coffee-database.
I manually set mybcsserviceaccount to dbowner in my Coffee-database, and it works like a charm!
But this is clearly not how it's supposed to work (I guess), but currently the only way I can access
the data through my external list.
Or maybe this is the way you have to tweek it when using BDC Identity (RevertToSelf) or User's Identity??
The backend I guess must somehow be aware of the accounts messing with it's data....