Sunday, June 20, 2010

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. :)

34 comments:

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

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

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

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

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

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

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

    Slava

    ReplyDelete
  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?

    ReplyDelete
  9. Hi Roger,

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

    Best wishes,
    Slava

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

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

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

    Slava

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. 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. Thanks for the link Unknown! I have updated my post as well.

    ReplyDelete
  16. 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. Ok find the solution:
    Just

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

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

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

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

    ReplyDelete
  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?

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

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

      Delete
    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?

      Delete
    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

      Delete
    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!

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

      Delete
    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

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

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

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

    ReplyDelete
  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

    ReplyDelete