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:
http://bit.ly/aWYlHn

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">
<Properties>
<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. :)
Labels:

Comments

  1. Andy MilsarkJune 22, 2010 at 12:48 PM

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

    ReplyDelete
  2. skatterbrainzJanuary 29, 2011 at 8:13 PM

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

    ReplyDelete
  3. WillWJanuary 30, 2011 at 1:37 AM

    Thanks skatterbrainz, I am glad it helped you.

    ReplyDelete
  4. skatterbrainzJanuary 31, 2011 at 9:25 AM

    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.

    ReplyDelete
  5. WillWJanuary 31, 2011 at 2:12 PM

    @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.

    ReplyDelete
  6. Jolly PirogerMarch 24, 2011 at 10:32 PM

    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.

    ReplyDelete
  7. WillWMarch 25, 2011 at 3:47 AM

    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,

    Slava

    ReplyDelete
  8. Jolly PirogerMarch 25, 2011 at 7:53 AM

    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?

    ReplyDelete
  9. WillWMarch 25, 2011 at 6:09 PM

    Hi Roger,

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

    Best wishes,
    Slava

    ReplyDelete
  10. Jolly PirogerMarch 25, 2011 at 6:58 PM

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

    ReplyDelete
  11. AnonymousSeptember 6, 2011 at 7:45 AM

    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
    THX
    James

    ReplyDelete
  12. WillWSeptember 11, 2011 at 2:48 AM

    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,

    Slava

    ReplyDelete
  13. UnknownSeptember 17, 2011 at 6:27 PM

    This comment has been removed by the author.

    ReplyDelete
  14. UnknownSeptember 17, 2011 at 6:30 PM

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

    http://sharepoint.microsoft.com/Blogs/GetThePoint/Lists/Posts/Post.aspx?ID=425

    Thanks for the awesome post Slava!

    ReplyDelete
  15. WillWOctober 9, 2011 at 3:50 AM

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

    ReplyDelete
  16. FacesDecember 29, 2011 at 3:55 AM

    Hi this is my BDCM file





    %





    RdbCredentials
    SqlServer
    BDFSV01
    FaxMakerArchive
    True
    Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
    faxmaker






    I can't find
    SQL User Name
    SQL User Password
    False

    ReplyDelete
  17. FacesDecember 29, 2011 at 6:22 AM

    Ok find the solution:
    Just

    PassThrough
    SqlServer
    DB Server Name
    DB Name
    True
    SQL User Name
    SQL User Password
    False

    ReplyDelete
  18. WillWJanuary 24, 2012 at 6:53 PM

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

    ReplyDelete
  19. RobyApril 6, 2012 at 6:24 AM

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

    ReplyDelete
  20. WillWApril 9, 2012 at 9:29 PM

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

    ReplyDelete
  21. AnonymousJuly 22, 2012 at 10:48 PM

    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?

    ReplyDelete
  22. WillWJuly 22, 2012 at 11:23 PM

    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.

    ReplyDelete
    Replies
    1. AnonymousJuly 22, 2012 at 11:46 PM

      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!
      :S

      Delete
    2. WillWJuly 23, 2012 at 12:31 AM

      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?

      Delete
    3. AnonymousJuly 23, 2012 at 4:52 PM

      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

      Delete
    4. AnonymousJuly 23, 2012 at 6:52 PM

      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!

      Delete
    5. WillWJuly 23, 2012 at 9:27 PM

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

      Best wishes.

      Delete
    6. UnknownAugust 14, 2013 at 5:05 AM

      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

      Delete
  23. OliverDecember 12, 2012 at 7:20 AM

    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,
    Oliver

    ReplyDelete
  24. UnknownMay 16, 2013 at 8:39 AM

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

    ReplyDelete
  25. UnknownJune 11, 2013 at 5:33 AM

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

    ReplyDelete
  26. UnknownJune 11, 2013 at 5:40 AM

    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

    ReplyDelete
  27. AnonymousOctober 16, 2013 at 8:41 AM

    Thanks, it works perfect.

    ReplyDelete
  28. UnknownMay 24, 2016 at 5:13 AM

    This comment has been removed by the author.

    ReplyDelete
  29. UnknownMay 24, 2016 at 5:16 AM

    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?

    ReplyDelete
  30. svrtechnologiesFebruary 11, 2020 at 2:41 AM

    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.

    ReplyDelete
  31. AnonymousMay 31, 2022 at 7:42 PM

    SMM PANEL
    smm panel
    İs ilanlari blog
    instagram takipçi satın al
    HIRDAVATÇI
    BEYAZESYATEKNİKSERVİSİ.COM.TR
    servis
    tiktok jeton hilesi

    ReplyDelete

Post a Comment

Popular posts from this blog

InfoPath 2013 Preview - URL not valid error when publishing

In InfoPath 2010 there is a problem when you try to publish a Form to a SharePoint site that doesn't have root site collection. You will get an error message "The following url is not valid". It's described here:   http://support.microsoft.com/kb/981854 The solution is to create root site collection for the Web application. Same problem is with InfoPath 2013: In my situation I didn't create the root site collection initially, but used "/sites" managed path instead for all site collections. To solve the problem with InfoPath I had to create it. Used a new site template "Project Site". Will get a chance to research that too. :)  
Post a Comment
Read more

Document Sets - SharePoint 2010 - Part 1

Hi again, in this post I am going to demonstrate how set up and start using Document Sets in SharePoint 2010. In Beta version there was a little problem when working with Document Sets. You could see the discussion around it here: Document Set content type issue . Now it is fixed and I will show you how to set up Document Sets properly to also use Keywords. 1. Activate two site collection features - Document Sets and Document ID Service: 2. Select a document library settings where you want to implement Document Sets. In my case it is Shared Documents. When you have selected the settings go to Advanced settings and then allow content types management: 3. Add an existing content type called Document Set: 4. Now I want to create a new Document Set. I have a sales opportunity and I have two documents related to this sales opportunity. So first I select New Document -> Document Set command, then provide name and description and there it is: 5. To check if our Document ID...
15 comments
Read more