SharePoint 2010 External Content to Read Data From SQL Server

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

• Create a dummy table
• Configure Secure Store Service
• Configure BCS service
• Creating External Content type
• Creating External List

1. Before starting, you should have an SQL server Table. I am creating a database for that.


2. I give it the name Contact_List:



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.



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





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



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




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.



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



10. Give a Pass Phrase and confirm the Phrase:



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



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




Table 2



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.



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:



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


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



Creating External Content Type

19. Hopefully you already have a web application.



20. Open your web application in SharePoint designer:





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











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



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







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



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


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


Table 3


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


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



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



33. You will be prompted with the following screen:


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


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


37. Click Save the BCS from the top:




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



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



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



41. Give all permissions as shown below:


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



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



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.



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




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


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



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.