Displaying a SQL Server table as a List in SharePoint 2010

If you want to display a table (or a view or a stored procedure) data from your SQL Server database in your SharePoint 2010 which resides in a different server, these are the steps that you can take: 

1. Create a Secure Store Service (SSS) using SharePoint 2010 Central Administration as shown below:
First select “Manage Service Application”

Then select Secure Store Service

And follow the Wizard instructions. For the purpose of this simple example select the default values. Then set the credentials for the SSS. Let’s call this SSS_1.

2. Create an External Content Type (ECT) using SharePoint Designer as shown below:

For External System choose SQL Server : 

You can choose tabels, view or stored procedures for your data source. For External System Authentication Mode select “Impersonate Windows Identity” and then select SSS_1 that you set up earlier for the Secure Store Application ID as shown below:

3. After creating an ECT, there will be a Business Data Connectivity Service (BDC) in SharePoint Central Administration associated with this ECT. Open that BDC and give permissions to users as shown below:

4. Create a list in SharePoint Designer of Type “External List” as shown below:

Select the content type as the ECT that was generated before.

In this scenario the credential required to access this list is the credentials that you stored at SSS.