3

Following the technet articles on how to get external data into SharePoint 2013 (for testing purposes) I've created:

  • Business Data Connectivity Service with Metadata Store Permissions
  • Secure Store Service Application (SSS) with SQL Server credentials (and not windows credentials, since they fail too)

The credentials in SSS are owners of the database Northwind I'm trying to connect to. When signing in with SQL Server Credentials, the user can read and execute the database as expected.

enter image description here

In SharePoint Designer I'm adding the new External Content Type, providing it a name and database server, database and connecting with Impersonated custom identity referencing the SSS. When I provide the SQL Server Credentials, it fails to connect asking for windows credentials. But since windows credentials don't work on the SQL Server login either, I'm stuck in a loop I can't get out of.

Error from SharePoint Designer

enter image description here

Error from SQL Server Log

enter image description here

The Web Application has service connection to BDC, and in the Secure Store Service Application Everyone is member. Still - no connection made because of the access denied error. Why is that, and how do I overcome this issue?

Improve this question
9

3 Answers 3

Reset to default
2

As it turned out, I needed to configure an External Content Type Profile Page Host within the Business Data Connectivity Service.

enter image description here

enter image description here

When the ECT Profile Page Host was configured, I could connect to the database and see the tables from the database within SharePoint Designer.

enter image description here

Improve this answer
1

SharePoint Designer does not use the Secure Store connection. Try creating the ECT first by connecting with user's identity. Once the ECT is created, modify the properties to use the Secure Store application ID.

Improve this answer
1
  • I tried this too, with individual credentials using on attempt one Windows credentials that has DB access, and secondly with SQL Server user credentials. I got different error logs in the SQL server, but still no connection. Do I miss a setting in the BDC Service Application?
    – Benny Skogberg ♦
    Commented Apr 15, 2015 at 16:05
1

I had to grant Metadata Store Permissions:

Central Administration > Application Management > Manage service applications > BDC Service Application

  • click 'Set Metadata Store Permission'

    Set Metadata Store Permission

  • then add your user and permisisons

    enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.