分享方式：

SharePoint 2010 External Content Type to read data from SQL Server using SQL Authentication and Secure Store Service

Editor's Note: The following MVP Monday post is by SharePoint MVP Destin Joy

SharePoint 2010 External Content Type to read data from SQL Server using SQL Authentication and
Secure Store Service

Most of the articles on BCS are explaining about reading data from database using windows authentication.
Windows authentication to read data from database won’t work in many of the scenarios. We have to use
SQL authentication to read or manipulate data from SQL. We have to use secure Store Service to use
SQL authentication

Here we have following five steps to achieve the same

  • 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 have created a database with name
    Contact List.
  2. Right click Table tab of the Contact_List and click on New Table
  3. I have created  a table with the below fields and saved the table with the name Contact_Table

 

No

Field Name

Type

1

Name

nchar(10)

2

Address

nchar(10)

3

Phone

nchar(10)

 

4. Now go to your central Administration Screen. Click on application management an select
Manage Services from Service Applications

Configuring Secure Service Store

5. From the list of service applications select Secure Store Service

 

6. Click on Generate New Key tab from the top

 

7. Give a Pass Phrase and confirm the Phrase

8. Click on the new from the ribbon once you done with key generation

9. In the next screen give the below details.

 No

Options

Value

1

Target Application ID

Any Name

2

Display Name

Any Name

3

Contact Email

Any Email

4

Target Application

Individual

10. Click Next. In the screen you got, give the details as shown below

11. Please note, to select Username and password from the filed type

12. Once done with details, click Next

13. Give service account name in the screen and give permission for the user to edit the details if needed later

14. We have almost done with Secure Service Store. Click on the menu SQL_BCS and select set Credentials from the menu

15. Give credentials Owner .SQL user name and password to retrieve the data from SQL

Creating
External Content Type

16. Hopes you already have a web application, where we can create external content type.

17. Open your web application in SharePoint designer, Select External Content type from the ribbon

18. Once opened in SharePoint Designer Click on External Content as shown below

19. Give proper name and display name, I have given BCS_Contacts

20. Select Office item type if you want to integrate the same data with Out look

21. Click on “Click here to discover external data source and define operations” link from External System

22. You will get the below screen, click on Add connections. Please note, to select the last option

23. In the screen Give the below screen. Give below details

No

Options

Value

1

Database Server Name

Your database server name or IP

2

Database r Name

Your database  name

3

Name

Any Name

4

Connect with impersonated Custom Identity

The Secure Store Service name we created in first step

24. You will be prompted with the user name and password. Give the same we entered to connect to SQL server
database

25. You will get the below screen populated with the database details

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

27. You will be prompted with the below scree

28. Click Next

29. If you want to add any filter add the same from the below screen.

30. Click Save the BCS from the top

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

32. Give the ID of the user who can use this service

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

34. Click on the Secure Store Service ,select the metadata store permission from the ribbon

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

Creating External List

36. Click on View all site content from your web application

37. Click on create

38. Select External list from  the menu

39. Give proper name and click Create

40. Give the name for the External content type and select the externalcontent Type external
Content Type as the one we created using designer.  Click Create

41. You can see your list populated with the data from database

 

Author's Bio

Destin Joy is a Microsoft MVP on SharePoint Server. He isan author, speaker and a blogger in Microsoft technology. Currently Destin isin the final phase of creating his second EBook on “SharePoint 2010 CapacityPlanning”, which will be published in C# corner.

MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager for Dynamics, Excel, Office 365, Platforms and SharePoint in the United States. She has been working with MVPs since her early days as Microsoft Exchange Support Engineer when MVPs would answer all the questions in the old newsgroups before she could get to them.

Comments

  • Anonymous
    April 16, 2012
    Great Article, I made the same article very similar: www.gokanozcifci.be/.../how-to-configure-BDC.html

  • Anonymous
    May 01, 2012
    Nice article. Well explained, Thanks Destin :)

  • Anonymous
    September 20, 2012
    Nice article. Well explained

  • Anonymous
    October 16, 2012
    Nice post, there's a good article at <a href='www.typeerror.com/.../& explaining how to overcome some of the common issues that may be encountered.

  • Anonymous
    November 27, 2012
    How could u achieve this using webservices programmatically? is there any way to retrive external content types from the server..

  • Anonymous
    February 14, 2013
    When you add an External Data Field to your list it do not have any option relating to "Allow Multiple Values", this same is applied to the LookUp column when you bind it with "External List". <a href="microcosmicsolutions.com/.../BCS%20Does%20Not%20Allow%20To%20Select%20Multiple%20Values.aspx" >Read Complete Post</a>

  • Anonymous
    March 08, 2013
    It was Terrific Destin. Thanks a lot.

  • Anonymous
    April 01, 2013
    I couldn't understand step:13 which service account credential we need to provide?

  • Anonymous
    May 13, 2013
    The comment has been removed

  • Anonymous
    October 06, 2013
    The comment has been removed

  • Anonymous
    February 11, 2014
    Great explanation. Its help a lot. Thanks.

  • Anonymous
    April 10, 2015
    Wonderful post however I was wondering if you could write a little more on this topic? I’d be very thankful if you could elaborate a little bit further. Thank you! <a href="staygreenacademy.com/.../">SharePoint Development Training Online</a>

  • Anonymous
    May 14, 2015
    Very nice article. I am trying to create ECT using SPD 2013 and I am getting error 'Access denied by Business Data Connectivity', I have Site administrator permissions. I got Secure store service application ID which is been setup with credentials  that has readonly access to SQL Server. I worked with farm admin and he tried from his machine creating the same thing but we are facing the same error. Please let me know if there is anything that needs tobe done to resolve this