I’ve done this plenty of times and never documented it, and one of these times I’m going to be working on something else, have to build one real quick, and forget how I did it, so i’m writing it down.

You can have a SharePoint 2010 List that connects to an external database table and perform CRUD operations on it, and it’s not that difficult to do. There’s generally speaking 3 specific steps required.

First, decide if you want to grant your users of this list database access individually or if you want to use the Secure Store Service in SharePoint. If you plan to grant users individual access to the database resources, just impersonate their credentials when building the External Content Type. In this example, we will use the Secure Store Service.

Open a browser and navigate to your farm’s Central Administration site. Under Application Management, choose Manage Service Applications. Locate the Secure Store Service in the list and click the link to open. In the ribbon at the top, click New.

The Target Application ID will be a unique name you give that identifies this connection. The Display Name can be any friendly name identifying the connection. The Contact Email should be someone who can administer this connection if there is a problem. Choose Group for the Target Application Type if you plan on granting more than one person access to the list, then click Next.

On the next screen, you can choose Field Names and Field types. This is how you will be prompted when entering credentials to connect to the database itself. Typically it’s Username and Password, but can be customized to fit your business’s needs.

The next screen requires you to enter Target Application admins and members. The Admins box should be groups or individuals who have admin level rights to the farm. The Members box are the groups and/or individuals who will need access to the resources in the database. NOTE: This is a very specific list, and SharePoint will only allow the people listed in this box to access the data resources, regardless of your SharePoint access. Even if you’re a Farm Admin, you can be denied access to database resources if you’re not listed in this box. Separate multiple users/groups with semicolons.

Once created, check the box next to the newly created item and click the Set Credentials button in the ribbon above

Enter the SQL database username and password that has access in the Username and Password fields. Re-enter the password to confirm and click OK to set the credentials.

You now have a Secure Store Application you can use to connect your external database to a SharePoint List!

Second, we need an External Content Type. Open SharePoint Designer 2010 and navigate to the desired site where the list will live. In the left pane, under Site Objects, choose External Content Types, then click the External Content Type button in the ribbon at the top.

Choose a name for the External Content Type, then click the link next to External System to begin building the connection.

In the next screen, click Add Connection near the top

You will be prompted to choose the data source type. Choose SQL Server and click OK. Enter the Database Server, the Database Name, and choose “Connect wth Imersonated Custom Identity from the list. Enter the Application ID you created from above and click OK to connect.

Expand out your table listing and locate the table you want to connect to the SharePoint List. Once located, right-click on it and choose Create All Operations

In the next few screen, you set up the Create, Update and Delete (CRUD) operations. It’s best if the table already has a primary key. If it doesn’t, designate a field that could act like a primary key if there were one in the table by highlighting the field and selecting Map to identifier under Properties, then selecting an Identifier in the drop down list. Here you can also decide if any fields are Required, Read Only, change the Display Name of how it will look in the List, etc. Click Finish when done.

Lastly, back in Central Administration, under Manage Applications click Manage Service Applications, then locate the Business Data Connectivity service and click the link. Locate the newly created service, check the box next to it and click Set Object Permissions in the ribbon above

Enter the names or groups of the individuals who will need access to the database resources in the top box, separated by semicolons, and click Add. Then, for each item, select the item and in the Permissions box below the list of names check the appropriate permissions you want to grant. At a minimum, an average user will need everything but Set Permissions. Do this for every affected user/group and click OK at the bottom.

You now have an External Content Type linked to the database! The only thing remaining is to hook up a list to the External Content Type.

Finally, in SharePoint Designer 2010, on the left under Site Objects choose External Content Type. When the page loads, locate your newly created External Content Type and click it once to select it. Then, in the ribbon at the top choose External List.

You will be prompted to give the list a name and description. Click OK, and your list will be created automatically!

That’s it, hope this helps!