Skip to main content

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server.

According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition.
The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you would get errors, because Secure Store Service is missing as a module.
For more information about that issue please have a look here:

In my scenario we have our own mighty CRM system called Wylde CRM where data is stored in SQL Server 2008 database and hosted on a remote server. Because I don't want to migrate our customers data to SharePoint I just want to leverage the new shiny functionality that SharePoint 2010 offers out of the box - Business Connectivity Services. Here I will show you how to set up ECT to work properly with remote SQL Server database.

1. Create an ECT in SharePoint Designer 2010.

1.1. Select SQL Server as a type and provide the SQL Server connection settings:

I provided WyldeCRMSS as a Secure Store Application ID, but it can be any name at this stage, because it will only be used on creation and will never work anymore.

1.2. Create all the necessary commands for your ECT to check that all works well from SharePoint Designer, save you ECT and then Export your BDC model to a .bdcm file:

2. Modify your .bdcm file. Open the file in an appropriate editor and find LobSystemInstances element and within it the LobSystemInstance element related to your ECT.

<LobSystemInstance name="SQL2008">
<Property Name="AuthenticationMode" Type="System.String">RdbCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">DB Server Name</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DB Name</Property></Properties></LobSystemInstance>

Whatever is conatined within the <Properties> element should be replaced with the following values:

<Property Name="AuthenticationMode" type="System.String">PassThrough</property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">DB Server Name</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DB Name</Property>
<Property Name="RdbConnection Pooling" Type="System.String">True</Property>
<Property Name="RdbConnection User ID" Type="System.String">SQL User Name</Property>
<Property Name="RdbConnection Password" Type="System.String">SQL User Password</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">False</Property>
<Property Name="ShowInSearchUI" Type="System.String"></Property>

Please pay attention to the elements in bold. They should be provided exactly as above.

Save the .bdcm file.

3. Delete the ECT you have created in SharePoint Designer.

4. Go to Central Administration or to Administration Web site if you have a multi-tenant environment and Import your updated .bdcm file:

5. If the file has been imported successfully - create an External List using the imported ECT:

6. Enjoy working with your external SQL Server data via SharePoint 2010 Foundation:

Here we go.

If you have any questions in regard to External Content Types, you need assistance or if you think you don't know how you could benefit from using External Content Types for your business - contact us now for a consultation.

Stay tuned for more articles from Wylde Solutions - next article is about setting up ECT properly in SharePoint 2010 Server. :)


  1. Slava, Excellent Post! We will definitely link to this post from our KB for other customers who come across this.

  2. Awesome! Thank you! I have been pulling my hair out over this for a while.

  3. Thanks skatterbrainz, I am glad it helped you.

  4. I spoke too soon. I must be missing something, but when I finish going through the steps above, I get to my list page and it says "Login failed for user ''." Somewhere I'm not getting the credentials passed through. I added "BUILTIN\Users" to have datareader access to the table in SQL, so it should work.

  5. @skatterbrainz:

    What account do you use to connect to the database?

    Is it SQL account or Windows account?

    Please provide some more information about your current set up.

  6. Hi,
    I really can't get the import to work. I replaced the code between the properties brackets and edited the properties regarding, server name, user name etc.

    I get an error message everytime I try an import of the bdcm file.

  7. Hi rsvensson,

    Could you please share the following:

    1. What exactly - as text - do you place into the bdcm file and where?
    2. Error message

    Then it would be easier to help you.

    Best wishes,


  8. I tried to post it here but the layout of the page just cut away a lot of text. I'm therefore sending you an e-mail instead. I hope that's ok?

  9. Hi Roger,

    Yes, that's alright. I have replied to your email. Please check.

    Best wishes,

  10. Thanks for your help Slava! You probably saved my ongoing project. :)

  11. Hi Slava, this post looks like the answer to my problem...
    however I'm not able to connect to ECT getting error:
    "Cannot logon with credentials obtained from Secure Store Provider"

    I have not set up a SSP

    using the same credentials I have set up a Database Connection, so my SQL logon is valid...

    hope you can help with this

  12. Hi James,

    It's hard to say why you are getting this error. Maybe you missed something in your .bdcm file?
    Because it looks like your ECT is trying to connect to external data source using Secure Store Service.

    If you could post your .bdcm file contents then it would be easier to help.

    Best wishes,


  13. This comment has been removed by the author.

  14. For those having issues, take a look at his microsoft blog post that links to this page.

    Thanks for the awesome post Slava!

  15. Thanks for the link Unknown! I have updated my post as well.

  16. Hi this is my BDCM file


    Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c

    I can't find
    SQL User Name
    SQL User Password

  17. Ok find the solution:

    DB Server Name
    DB Name
    SQL User Name
    SQL User Password

  18. Hi Faces, good to see you have found the solution. :)

  19. Thanks a lot this article was exactly what I was looking for ! Now my list works perfectly ! Thanks a lot !

  20. Hi Roby, good to hear it helped! Thanks for the feedback.

  21. After pressing okay on step 1.1, a windows authentication dialogue box with the title "BCS Secure Store: foo"

    the box asks me to "Please enter user name and password for foo"

    Not too sure what I'm meant to type in the username and password box here.

    Can you help? is this normal?

  22. Hi Mbattiston,

    It's been a while since I worked with that last.
    Anyway I think in this case you could provide the credentials that have got access to your external data source. I suppose that it's only going to be used once you create your ECTs by SharePoint Designer.
    Also, if you are using SharePoint 2010 Foundation there is no existing Secure Store Service so whatever you would provide as credentials at 1.1. shouldn't really matter.
    Let me know if that makes sense or not and how did you go.

    1. Hi Slava,

      Sorry, I forgot to thank you for the post. I like that you've found a way to subvert SSS haha

      Yeah, I figured the same thing. I've used the following credentials in an attempt to get it going. I am using Foundation so yeah I thought it wouldnt matter too.

      - SQL auth credentials which give me access to the db
      - Windows auth
      - foo and bar (sample credentials which dont actually exist)

      When I try the any of those methods I get the following error:

      "Cannot connect to the LobSystem (External System)" error 26 - Error locating the Server/Instance Specified

      obviously i've made sure that the Server/instance is correct!

    2. No worries, glad the post is of some help :)

      Hmm, maybe in that case it really tries to connect to that SQL database server you are specifying? So if the "foo" doesn't have access to the database it gives you this error?
      Or you current account doesn't have permissions?

    3. Yeah but it's weird because I get the same error no matter what credentials I put in (whether they're valid or not doesn't seem to effect the outcome). I have also tried with credentials of the DBA account which has all permissions on all dbs haha :S very weird. I'll keep trying and keep you updated. Let me know if you can think of anything else ^_^ thanks again

    4. Here's an update: I can use this to method create an External Content Type when I'm connected to the instance which is running SharePoint and SQL Server via RDP. So I have to go into that machine via RDP and then I can access to the DB.

      I'm guessing this is a firewall or security group issue.

      I'll have to check it out. Thanks a lot for your post/help though.

      It's great that you can use SQL auth to connect to the db even if you dont have sp server or enterprise! Thanks again!

    5. Great news! and thanks for updating. No problems, I am glad it helped.
      I like this free feature of SharePoint 2010 too. :)

      Best wishes.

    6. Hi - don't know if you're still picking up replies on this ... I have followed all the steps above but still get an error with a workflow trying to add a list item. I get the "The workflow could not update the item in the external data source...." error, as workflow is running under SHAREPOINT\system

  23. Hi Slava,

    nice post!

    I´ve created my External Content Type as you described. But when I open the new SharePoint list I get an error that the login failed. The log of the SQL Server says that the login failed because the SQL Server only accepts Windows Authentication.

    Does your trick work only for SQL Authenticaion and not for Windows Authentication?

    Best regards,

  24. you're a monster Slava. You saved my SP career! hehehe

  25. Small remarks,
    Type and Property should start with capital letter :)

  26. Did everything as you said and I get this error
    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    Correlation ID:d8e6d265-9f46-47f9-8c79-0d60aa7796ea

  27. Thanks, it works perfect.

  28. This comment has been removed by the author.

  29. i, Dont know if you still pick things up from here but I seem to be having a problem importing the BDC Model. Getting an error which include 'The BDC Model is not well-formed' which relates to the line changing the authentication mode to PassThrough.

    Any ideas?

  30. Thanks for sharing this post. Your post is really very helpful its students.
    SQL server dba Online Training

  31. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly sql online training and sql server 2012 tutorial.


Post a Comment

Popular posts from this blog

SharePoint 2010 Search Issue - FQDN Crawl

I have recently set up a standalone SharePoint 2010 environment. The Web application was created with host header and the site collection is accessible from the client machines, but not internally. That was because of using FQDN to access the Web site. The error when I tried to access site internally was similar to the one described here: You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version And the search returned the following error in the Event Log: "The start address    cannot be crawled. Context: Application 'Search_Service_Application', Catalog 'Portal_Content' Details: This item could not be crawled because the crawler could not connect to the repository." One of the suggestions was to disable the loopback check, but that would compromise the Web server. So what I have done was: 1. Added a binding to my IIS Web site for a different port. Let's say my Web

SharePoint 2013 - Setting Up External Content Type

There were earlier posts where we discussed External Content Types setup for SharePoint 2010: Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation External Content Types - Reload - Setting up for SQL Server database using SQL Server authentication - SharePoint 2010 Server This one is about creating connection to the custom SQL Server database (External System) in SharePoint 2013. 1. Create Secure Store Service Target Application 1.1. Go to Central Administration -> Manage Service Applications -> Secure Store Service Application. Click "Generate New Key" if required: 1.2. Provide Pass Phrase: 1.3. Create "New" to create new Target Application: 1.4. Provide the name and other parameters and click "Next": Note : It's good idea to specify "Group" for Target Application Type. In that case you would be able to manage access to the external