External List - Fetch data from SQL using SQL Authentication and Secure Store Service

Steps

Configuring Secure Store Service

1.       Go to Central Administration à Manage Service Application à Check Secure Store Service Application is running.

2.       If Secure Store Application is not there, create new Secure Store Service

3.       Then click “New” button to create a new target application using the “Group” type

4.       Give the details for – Target Application Id, Display Name, Contact Email and Target Application

5.       Click Next, Choose “User Name” and “Password” from the dropdown options. Also change the name from Windows User Name to “User Name” and Windows Password to “Password”

6.       Give Service account/(account with admin access) name in the screen and give permission for the user to edit the details if required later

7.       Now click on the Target Application and select ‘Set Credentials” from the menu

8.       Give Credential Owner as – Service application/account with admin access

9.       Give SQL User Name and Password to retrieve data from SQL

 

Creating External Content Type

1.       Using SharePoint Designerà Click External Content Type à Give External List Name and other details

2.       Click External System – Click here to discover external data store and enter details of SQL

3.       Database Server Name, Database Name, SQL User Name and Password

Importing External Content Type using Powershell Script

-- To Import BDCM in a Server --- STARTS-

$MetadataStore = Get-SPBusinessDataCatalogMetadataObject -BdcObjectType "Catalog" -ServiceContext <<SiteUrl>>

Import-SPBusinessDataCatalogModel -Path "<<*.bdcm>>" -Identity $MetadataStore

-- ENDS --

 

Below link talks about the same with screenshots,

 

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/04/12/sharepoint-2010-external-content-type-to-read-data-from-sql-server-using-sql-authentication-and-secure-store-service.aspx