BSC Connection to SQL with Edit Permissions RRS feed

  • Question

  • Hello,

    I am having difficulty setting up a BCS connection to SQL with the functionality that I desire.  I have a SharePoint 2010 front end and a number of SQL instances that are on separate boxes.  When I setup the External Content Type I would like to use the User Identity connection type so that permissions for Read and Edit on the SQL databases can be controlled with our existing Active Directory Security groups which are already in place.  This type of Pass Through Authentication is apparently not an option as it is a double hop authentication issue (from client to SharePoint then from SharePoint to SQL).

    I have also setup a Secure Store Credential to get around this issue but I have a couple of different issues with this:

    1)      If I have a single web page where multiple users will look at the information then everyone that accesses that page has the same read rights to the SQL data.  This means I either must show a subset of SQL data or create multiple pages and Secure Store Credentials then apply SharePoint security to the various pages – this is not optimal as it is a lot of administrative overhead.

    2)      It appears that when using Secure Store Credentials you are only able to obtain read access to the data and users cannot modify the data in the list.  While this works for our basic users there are some users that we would like to edit the data to take the tasks off of the developers/administrators.  Does anyone have any suggestions on how to enable edit access through the Secure Store Credentials?

    Optimally we would like to have a single web page that multiple users could access that would use the SQL permission structure already in place to decide if they have read or edit on a particular list.

    Any ideas on how I might accomplish this?



    Wednesday, December 7, 2011 7:30 PM


  • 1) Secure store when used with BCS provides a self service functionality.  The first time a user hits the BCS connection Secure Store will prompt them to authenticate.  Once they have provided their credentials Secure store will use those credentials on a per user basis from then on.  Since each user has their own credentials you can use the security in the SQL server to only allow users access to a subset of data, without using a seperate page or web part in SharePoint.

    2) Secure store simply passes the credentials to SQL.  It doesn't limit you to Read access.  It depends on what rights the credentials have been given in SQL and whether BCS has been setup to provide R/W access.

    So here's your two options.

    1) Setup a group credential in Secure Store.  This provides a service account kind of access to SQL, but since there is only one account SQL can't identify what your user should be able to see so your application logic will need to filter records that users shouldn't see.

    2) Setup individual credential definition in Secure Store.  Each user will be prompted to authenticate the first time and then those credentials will passed through to SQL.  Rights to the data will now need to be maintained in SQL, but that will simplify your SharePoint configuration.

    Paul Stork SharePoint Server MVP Chief SharePoint
    Architect: Sharesquared Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.
    • Marked as answer by MB2009 Thursday, December 8, 2011 5:15 PM
    Wednesday, December 7, 2011 7:49 PM