Connect Sharepoint 2010 (external content type) to MSSQL Linked Server

Question

I can't seem to connect to my linked server database from within the external content type definition screen in Microsoft Sharepoint 2010.

When I access my Linked Server from with SQL Server Management Studio, it works fine:

SELECT * FROM MyLinkedServer.MyCatalog.MyDatabase.MyTable

returns all the records just fine.

But in Sharepoint I choose "External Content Type", i create one; On the next screen I click "External system, click here to detect external datasources .." (or something similiar).

- Add connect/datasource  
  * Datasourcetype: SQL server  
  * Databaseserver: MySQLServerIP\InstanceName (ex. 192.168.1.2\SQLEXPRESS)  
  * Databasename: MyLinkedServer.MyCatalog.MyDatabase (ex. AS400.COMPANY1.ERPSYS)  
  * Name (optional): <Empty>  
  * Select 'Connect using the user-id (first option)'  
- OK.

ERROR:

Cannot connect to the LobSystem (external system) Cannot open database "AS400.COMPANY1.ERPSYS requested by the login. The login failed. Login failed for user 'MYDOMAIN\me'.

I use the same user for SQL Management studio and Sharepoint. I should have permission to access the Linked Server. I've set the specific credentials for the Linked Server for MYDOMAIN\me.

I can connect directly to a SQL database (which is not on a linked server) though..

Any ideas?

JoeK JoeK · Accepted Answer · 2011-10-25T13:45:35

I had a similar task and I found the following site very useful. http://wyldesharepoint.blogspot.com/2010/06/external-content-types-reload-setting.html

You may also need to configure the permissions on the BDC Model Metadata after that, so I used http://www.zimmergren.net/archive/2010/05/08/access-denied-by-business-data-connectivity-solution.aspx

Let me know how you get on? JK

Connect Sharepoint 2010 (external content type) to MSSQL Linked Server

2 Answers