External Content Types – SQL Auth, NO Kerberos

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.
clip_image002

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. clip_image004
  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.  clip_image005
  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:
    clip_image007
  6. Type the name of the SQL account that you want to use, and the password. Click OK.
    clip_image009
  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.
    clip_image010
  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.

Advertisements

16 comments

  • Great post! This worked perfectly fine.

    Like

  • Hi Laura, To add your amazing post, I had problems with BCS and the Double Hop Issue. I was connecting my customer Sharepoint 2010 with his legacy system. He asked me to retrieve the Data and the Permission from the system and show them on Sharepoint Interface. But I got the Double Hop Issue I couldn´t change the network authentication to Kerberus, as Microsoft recommends. Instead of that, I keep my Service (WCF in case) with anonymous authentication and pass the “User Context” filter (set using the SPD 2010) as parameter to all my Methods. The user permission was set inside the service and I got the permissioned Data . No Kerberus, no Double Hop! Just for share on BCS experience. Keep Posting, Greetings from Brazil, Ernesto

    Like

  • You are Awesome!!!! Thank you! Thank you! Thank you!!!

    Like

  • Finally! A how-to that was easy to the point and actually WORKED! I hate it when I read days of overly complicated “how-to” procedures to do something in SharePoint, only to figure out how ieasy it actually is. Kudos!

    Like

  • You are a really star!! You saved my life. I have been trying to work on for coupde of days. Thanks very much IT Solution Provider

    Like

  • nice article

    Like

  • Can we create external content type at web application level or at site collection level?

    Like

  • Nidhi,
    You create these from within SharePoint Designer, but you can admin them all from within Central Admin, in the service applications, the business connectivity service.

    Like

  • Laura, what happens that if you have a sharepoint foundation?

    Like

  • Manuel,
    Sorry, but the secure store service app doesn’t exist in Foundation.

    Like

  • I followed your destructions to the letter, but no joy. :((
    The account I use is me (with God-like powers), but I am still refused access to the external list.
    Any ideas as to why it hates me?

    Like

  • Chas,
    All I can think of is that you don’t have access to that data in whatever database you’re trying to access. Have you tried different databases in different locations?

    Like

  • I always have trouble getting the attention of the SQL owners to give me accounts. How hard can it be?

    Like

  • Yeah Marie, those damn server admins! 😉

    Like

  • Glad you like it!

    Like

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s