3

I'm trying to bring external content into our SharePoint environment. We are running SP 2010. The data I want to bring in exists in Sql Server. What I'm struggling to do is map the logged in SharePoint user to an underlying sql user.

In SharePoint I have created a new Target Application in the Secure Store Serivce:
Target Application ID: TestApp
Target Application Type: Group

I have specified two fields:

Name :: Type
User Name :: User Name
Password :: Password
--these are not the Windows User Name and Windows Password types just the basic types

Target Application Administrators and Members are both set as myself (AD User).

In SharePoint Designer I've created a new External Content Type. I've added a connection of (Type -> Sql Server). I've set the database server name to the server name that is not on the same box as SharePoint and I've set the database name. I've selected the Connect with Impersonated Custom Identity option and set the Secure Store Application Id to TestApp. When I click OK I get prompted for credentials so I enter the Sql Server user credentials and the connection succeeds. I expand out the tables, right click the table I want accessible, and click Create All Operations. I go through the wizard and enter one limit filter.

Next I right click MyTable in the External Content Types windows, select External List, and give it a name. Next I go into Central Admin and set the credentials of TestApp.

Now when I log into SharePoint I can see my external list in the left hand TOC. I click on the list and I get the error Access Denied by Business Data Connectivity with a correlation code. I've opened up the logs to see what is being returned which has some interesting logs but I'm not sure exactly how to remedy the problem:

Log:
Access Denied for User '0#.w|domain\myuser, which may be an impersonation by 'domain\myuser'. Securable MethodInstance with Name 'Read List' has ACL that contains

Another Log:
Error while executing web part: Microsoft.SharePoint.SPException: Access denied by Business Data Connectivity. ---> Access Denied for User '0#.w|domain\myuser', which may be an impersonation by 'domain\myuser'. Securable MethodInstance with Name 'Read List' denied access.

I know if I'm using pass through creds we will need kerbors to handle the double hop but didn't think I would need kerbos with cred mapping to a sql server user.

Any help would be greatly appreciated!!!!

Greg
  • 73
  • 2
  • 7

2 Answers2

5

I was playing around with BCS and ran into what I think is a similar problem. This helped to solved the problem. See if this helps

http://www.zimmergren.net/archive/2010/05/08/access-denied-by-business-data-connectivity-solution.aspx

Angel
  • 51
  • 1
4

This happens because you have not set access on the BCS object that you created. Go into Central Administration and select your External Content Type or other object and select Set Permissions from the dropdown. Your authentication is working or you would not be able to save the object you created to the metadata store. You now need to tell the metadata store who has access to the objects you created in BCS.

Junx
  • 454
  • 3
  • 7