Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

• Prep Work
  • Active Directory Users in Play
    • The Service Account I am selecting as the Impersonated User (Credential Owner)
    • The Security Group where all the people that will consume BCS Data will reside
  • SQL Server Security
    • Who has Access to What
• Setup
  • Creating & Configuring the Secure Store Object
  • Creating & Configuring the External Content Type in SharePoint Designer 2010
    • Creating External Connection with Secure Store
    • Creating the External Content Type
  • Reviewing the External Content Type (ECT)
  • Reviewing the Security on the ECT
• Test & Validation
  • Creating an External List derived from the ECT
  • Logging on as a User from the Security Group AND Secured in the permission setting of the ECT
  • Logging on as a User from the Security Group NOT Secured in the permission setting of the ECT

Part 1: Setup

Above:  This represents the AD Account [appBCSUser] which I will use as the Impersonated User i.e. the Broker if you will that will connect to the LOB system on behalf of the Group of people who should have access to the data but DOES NOT have access to the database. This is something your DBA will love because he doesn’t have a flurry of people having accounts on his/her DB.

Above: This represents the AD Security Group [SecureStoreBCSUsers] that have access or should have access to LOB Systems. You can of-course have multiple of these for any number of LOB Systems. Note here that Fabian and Hardeep are in this list, we will be the test users later on.

Above:  Lets look into CA now and set up our environment

Above:  Click Applications Management then Manage Service Applications

Above: We are interested in the Secure Store Service so we click it

Above:  We already have some there from previous Labs, but we will create a new one… click New

Above:  We create a Target Application ID [note this cant be changed once committed], Display Name which can be the Same App ID, and so on.

Above:  I populate the fields and choose “Group” as my Target Application Type. MSDN has a good explanation as to why you want to do that over other options. the Long and Short is that it allows me in this example to tie an AD Group FabianLabSecureStoreBCSUsers to a single set of credentials i.e. the FabianLabappBCSUser account. Ill show a few other options below

Above:  By default it wants to know how you will collect the credential of the Impersonated User in my case it is a Windows Account so this works.

Above: I change it around a bit for kicks by adding the word Testing infront of the default text

Above:  Here are a few other options that you can use. SSS is a Claims Aware SSO solution and can take in just about any Authentication Mechanism

Above:  So here because I only log on to CA with the Farm Admin Account, I set that as the target App Admin, however here is where we start to make the App Work for our design. In Members, you can see that i have my AD Group Account earlier. This means that I dont have to meddle with the SSS App anymore, just add and subtract from the AD Security Group.

Above:  It processes once i click OK

Above: Now i have a NEW SSS App, but wait you may ask… what about the Impersonated User.. we are coming to that…

Above:  We click on the custom actions available and select SET CREDENTIALS to set the Mapping for the Impersonated Users to the Group that we will Manage of “Allowed Users”…

Above:  Our trusty Silverlight App shows the progress of us opening a Dialog Pane

Above: The default look of the Credential Mapping

Above: I populated the values with my User Account previously mentioned in the AD Step

Part 2: Validation and Testing

Above:  So in SQL Sever you can clearly see that the only account that has Access to the Database “FabianPlayPen” is the AD User mentioned above right…

Above:  We create a new External Content Type by defining the name and Selecting External system to define our Connectivity

Above:  We choose SQL from the list of choices

Above: We define our SSO connection. One note here though in full disclosure, I had tried a few times to make this work and did a typo, so I re-did my SSS App and called it FabianLABSSSMSDNForumQ from what i had it last but the steps are the same.

Above:  Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.

Above:  Once completed you will be able to connect to your LOB System, expand it and perform any operation allowable to you

Above: In our instance lets just create a FULL CRUD operation

Above: Validation that it is complete

Above: Click the “Save” button to push the ECT up to the BDC Metadata Store.

Above:  Now we can check a place where alot of Gotchas happen. Now one may assume that because they have access to the LOB system via the impersonated user and Group Mapping you are done… You’d be wrong, now you NEED to have permission to use the ECT and I already have mine set up by default under “Set Store Permission” to add myself, the search account, and my service account by default. You may need to put your security group here to make it seamless, but because i am doing demos and want it to break depending on my use case, i leave it fluid.

Above:  to do that, click the custom actions and select “Set Permissions”

Above: Do your business here by adding the users you want to have access. Here note that Hardeep doesnt have access while he IS a member of the Security Group.

Above:  Once done, now we can create our External List by choosing our ETC recently created.

Above:  Commit to the System and cross your fingers…. Voilla!

Part 3: UAT

Above: Logged on as Me…

Reference

http://www.fabiangwilliams.com/2010/04/16/how-to-create-configure-consume-sharepoint-2010-secure-store-in-business-connectivity-services/

Getting started with Business Connectivity Services (BCS) in SharePoint 2010

Getting started with Business Connectivity Services (BCS) in SharePoint 2010

BCS in SharePoint 2010 is an awesome refinement of the Business Data Catalog from MOSS 2007. With BCS – or Business Connectivity Services – you get the possibility to connect external data and work with it from SharePoint.

In this article I will not cover the basics of what BCS is all about (MSDN/TechNet does this very well) – I will rather give you a walkthrough of how you can setup a BCS connection to an external database, and then work with this information directly from a SharePoint list – without the user actually knowing anything about the connection to the database.

BCS Poster: Business Connectivity Services Poster
BCS Team Blog: http://blogs.msdn.com/bcs/

A sample SQL database

I’ll just show you how my sample database is set up – simply create a new database in your SQL Server and have it filled with some example data. In my case, this is the data in my SQL database, called Zimmergren_DB:

In this sample database, I’ve added a table called ProductList which in theory will represent some products in this database, like this:

I’m filling the database with some sample data, so we will be familiar with this data when we later watch this information from SharePoint:

Alright – we have some sample data in our SQL Server. Nothing fancy, just some very simple data. Great, let’s get going with the fun stuff!

Creating an external content type

The most effective and easy way to set up a simple BCS connection, is to use SharePoint Designer 2010. You heard me, we can now get up and running with BCS by using SPD instead of modeling complex ADF files and things like that.

In order to do this, we need to create a new External Content Type!

Here’s how do create our External Content Type and hook it up with our database, step by step:

1. Open the site you want to work with using SharePoint Designer 2010
2. Select "External Content Types" in the left hand navigation:
   
   Loading this page might take some time, be patient!
3. Click to create a new External Content Type like this:
   
4. Click the link that reads: "Click here to discover external data sources and define operations":
   
5. Click "Add Connection"
   
6. Select "SQL Server" as your Data Source Type:
   
7. Enter the details about your connection to your SQL Server:
   
8. When the connection is made, your Data Source Explorer will be filled with the database you have specified. Now choose the table you want to work with, and right-click and select "Create All Operations":
    
   You’ll be presented with a wizard-like dialog where you can specify the operations, elements and other properties for your BCS connection.
9. Click "Next" to get to the Parameters page
10. Select the field that you want to act as an Identifier. In my case I’ve selected my ProductID just to get on with it:
    
11. Click "Finish"
12. You’ll be presented with a list of operations that your External Content Type can do, like this:
    

That’s it. A few points, a few clicks – and you’re done. Let’s create an external list (using the Browser to show how simple it is..) and hook up our external content type with it!

Creating an external list

There’s a few ways to create an external list in SharePoint 2010. We will create it using the Browser UI to show you how simple it can be.

1. Open your site and choose Site Actions - More Options…
    
2. Select the External List template, and click Create
   
3. Enter a name for your list, e.g. Product List
4. You’ll see a field in this list called External Content Type, click the browse-button beside it:
   
   What is really awesome here, is that you’re now presented with a dialog where you simply can choose the data source for this list. That means, you’ll select the data source you’ve created (mine is called Zimmergren_DB). Then your list will automatically work against the SQL database, but still have the look and feel of a SharePoint 2010 list. 
5. Select your data source and click OK:
   
6. Now simply click the button called Create:
   

Would you look at that! You’re now working with external data, from your (what looks to be) normal SharePoint list! This is brilliant!

You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.

Proof of concept – Adding a new product

Let’s just for the fun of it add a new product called "Awesome Product 1.0" like the following screenshot:

Now go to your SQL Server and see the changes take effect immediately. The data is NOT stored in SharePoint, it’s stored in your SQL Database.

This is what my table now looks like in the SQL Server, after adding a new item in the SharePoint list:

Summary

With a few points, followed by a few clicks – you’ve set up your external data connection. Basically it’s that simple.

Of course there’s a lot of things to consider when doing these configurations – and you might not want to auto-generate the CRUD-operations, but rather create them one by one and specify more fine-grained permissions etc.

This is merely a sample to show you how easy it is to actually get up and running with the SharePoint 2010 Business Connectivity Services (BCS) and work with external data!

Reference

http://zimmergren.net/technical/sp-2010-getting-started-with-business-connectivity-services-bcs-in-sharepoint-2010