I have seen many articles on creating external content types to read data from SQL server. Most of these articles are explain about reading data from the database using Windows authentication. Windows authentication to read data from a database won't work in many scenarios. We have to use SQL authentication to read or manipulate data from SQL. Here we have to use secure Store Service to use SQL authentication.

Here I am showing five things
1. Before starting, you should have an SQL server Table. I am creating a database for that.
SSSShr1.gif

2. I give it the name Contact_List:

SSSShr2.gif

3. Click Ok and Save
4. Now we have created the database. Now I have to create a table. Right-click the Table tab of the Contact_List and click on New Table.

SSSShr3.gif

5. I have created the following fields and saved the table with the name Contact_Table:


TBL1.gif
SSSShr4.gif

Table 1

6. Now go to your central Administration Screen. Click on application management and select Manage Services from Service Applications:

Configuring Secure Service Store

SSSShr5.gif

7. From the list of service applications select Secure Store Service:

SSSShr6.gif


8. You will get the following error when you open the service the first time. This is because you didn't create a key for the service.

SSSShr7.gif

9. Click on Generate New Key tab from the top:

SSSShr8.gif

10. Give a Pass Phrase and confirm the Phrase:

SSSShr9.gif

11. Click on new from the ribbon, after you are done with key generation:

SSSShr9.5.gif

12. You will get the following screen; give the following details:


TBL2.gif

Table 2

SSSShr10.gif

13. Click Next. You will get the following screen. Give the details as shown below.
14. Please note to select Username and password from the field type.

SSSShr11.gif

15. Once done with details, click Next.
16. Give service account name in the screen and give permission for the user to edit the details if needed later:

SSSShr12.gif

17. We are almost done with the Secure Service Store. Click on the menu SQL_BCS and select set Credentials from the menu:
SSSShr13.gif

18. Give the credentials owner's .SQL user name and password to retrieve the data from SQL:

SSSShr14.gif

Creating External Content Type

19. Hopefully you already have a web application.

SSSShr15.gif

20. Open your web application in SharePoint designer:



Des4.gif

21. Once opened in SharePoint Designer, click on External Content Types:


Destin.gif








22. Select External Content type from the ribbon as shown below:

SSSShr18.gif

23. Give proper name and display name; I have given BCS_Contacts
24. Select Office item type if you want to integrate the same data with Outlook
25. Click on the "Click here to discover external data source and define operations" link from External System



Destin2.gif



26. You will get the following screen; click on Add connections:

SSSShr20.gif

27. Select SWL Server as the Data Source Type.
28. Click ok:
SSSShr21.gif

29. You will get the following screen. Give the following details:
TBL3.gif

Table 3
SSSShr22.gif

30. You will be prompted for the user name and password. Give the same as we entered to connect to SQL server database:
SSSShr23.gif

31. You will get the following screen populated with the database details:

SSSShr24.gif

32. Right-click on the Table and select Create all operations if you want to do all the operations in the database.

SSSShr25.gif

33. You will be prompted with the following screen:
SSSShr26.gif

34. Click Next.
35. From the following screen map the field to the Outlook field, if you want to synchronize it with Outlook.
SSSShr27.gif

36. If you want to add a filter, add it from the following screen:
SSSShr28.gif

37. Click Save the BCS from the top:

SSSShr29.gif


38. Go to manages services and select Business Connectivity service then click Administration:

SSSShr30.gif

39. Give the ID of the user who can use this service:

SSSShr31.gif

40. Then click on permission and give permission to who all need to use this service:

SSSShr32.gif

41. Give all permissions as shown below:
SSSShr33.gif

42. Then click on the Secure Store Service, select the metadata store permission from the ribbon:

SSSShr34.gif

43. Give permission to all the users who want to use this service to read data from the data base:

SSSShr35.gif

Creating External List

44. Click on View all site content from your web application.
45. Click on create.
46. Select External list from the menu.
47. Give the proper name and click Create.

SSSShr36.gif

48. Give the name for the External content type and select the externalcontent Type; the one we created using the designer.
49. Click Create:

SSSShr37.gif


50. Now you can see your list populated with the data from database:
SSSShr38.gif

51. For verification I have shown the database table below:

Des5.gif

I hopes everyone enjoyed my article. Please reach to me if you have any queries.

See you all in the Mindcracker MVP Summit next month 6th. I would be there with you.