@WonderLaura

It’s so easy, even I can do it!  In SharePoint 2010, Business Connectivity Services (BCS) is what used to be the BDC in SharePoint 2007.  With the BCS, you create External Content Types (ECT) that let you define a connection to a database or web service that is external to SharePoint.

The actual steps of the creation of the External Content Type are actually the most intuitive of this whole process.  It’s the security / authentication setup that is the most complicated.  There are several levels and options for giving access to the data you connect to.  First of all, you can go here to go through those steps of creating the ECT, here’s a MSDN page:

How to: Create an External Content Type Based on a SQL Server Table

Also, here’s a good Introduction to Business Connectivity Services.

I’m no BCS guru like Brett Lonsdale (@brettlonsdale), but I’m going to explain some very high level information about the authentication stuff, and then show you the steps for a scenario where we will use a single SQL account to impersonate when we access the external data from SharePoint.

• Database Level
  • You have the option of passing the “User’s Identity” to the external data source.  This means that each user who will be accessing the ECT data will need to have direct access to that back end database, such as a SQL database.  Also, when the external data is on a separate server than SharePoint (most likely), than you’ll need to implement Kerberos authentication on your farm because of the double hop issue.
  • You can also use impersonation, which means that you can give a single account access to the external database, and simple impersonate that account when accessing the data.
• ECT Level
  • In Business Data Connectivity Service Application in Central Admin, you use the permissions buttons in the ribbon to set permission on individual objects or set the default and propagate it to all objects.
• SharePoint List Permissions
  • Once external content types are created, you can create SharePoint external lists from them, from within SharePoint Designer.  Once the list has been created, you can set permissions on it just like any other SharePoint list.

Here are the steps to take your external content and make it available in SharePoint, and make it accessible to the desired group of people.  I have to admit that these steps are high level in some places, for example I’m not going to talk you through database permissions in SQL.  Also, SQL needs to be in mixed mode if you’re going to be using a SQL account as opposed to a Windows account.  This post is about how to impersonate a SQL account, but the Windows account steps are pretty similar.

What to know ahead of time:

• Which SQL account are you going to use, to impersonate.
• Which group of domain users will you be giving access to the data?  In this example, I’m using “Domain users” which is everyone, but you don’t have to give access that liberally.

1. In SQL:
   Give the SQL account the appropriate level of access to the database that will be used in the SharePoint External Content type.  So whatever permission you give this account, will be the effective permissions of the group (domain users in this case).
2. In SharePoint Designer 2010:
   Create the External Content Type. The authentication type can be initially set as “User’s Identity”. This will be changed later.  Create an External List also, if you so desire.  With this, users will be able to edit the database data directly.
3. In Central Administration, Open the Business Data Connectivity service application.
4. Check the box next to the External Content Type that you just created, and click the Set Object Permissions button.
5. Give the appropriate group access, such as Domain Users = Edit. Again, this part depends on the situation and your preferences.In testing I like to use the practice of granting permissions liberally at first, just to make sure everything’s working, and then go back through and start taking things away.
   
6. In Central Administration, Open the Secure Store service application.
   If this is the first time that secure store has been used, you will need to generate a new key before the first secure store item can be created. You will be prompted to create a password.

Click the New button in the ribbon at the top left.

Target Application ID – make up something that is relevant to the name of the SQL server you’ll be authenticating to, and the type of authentication, such as ServerASQL

Display Name – can be something like “ServerA SQL Group”, which is descriptive as to what this target application is going to be used for.

Contact Email – the email address of the target application’s primary contact person

Target Application Type – Pick Group. This is so that a whole group of users can impersonate the one SQL account. If individual was selected, it would only be a one to one mapping of one person impersonating one SQL account.

1. Click NEXT.
2. For the first field name, call it SQL Account, and pick “User Name”. For the second field name, call it SQL password and pick “Password”. Click NEXT.
3. Target Application Administrators – pick the account of the person who is responsible for this target application.Members – This is the group that you want to be able to impersonate the SQL account. Pick domain users if you would like everyone in the company to have access to the data. The security permissions were given to the SQL account at step one in this process will dictate what type of access this group will have. 
4. Click OK.
5. Back on the main screen of the Secure Store service application, click the drop-down box on the name of the item that you just created. Click Set Credentials:
   
6. Type the name of the SQL account that you want to use, and the password. Click OK.
   
7. In SharePoint Designer
   Open the external content type that was created at step two.
8. Click the Edit Connection Properties button in the ribbon.
   
9. Change Authentication Mode to “Impersonate Custom Identity”.
10. In the Secure Store Application ID box, type the ID that you created at step 4b above.
11. Click OK and save the external content type.

Now, when the external list is created from the external content type, all domain users will have access to it.