Sunday, June 20, 2010

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. 

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. 

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. 

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. 

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? 

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

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! 

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

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? 

Did everything as you said and I get this errorUnable 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.