Cannot connect to external datasource

Question

I have db user account & used it to create a secure store application id and a managed account. I have also given myslef full permissions on the bdc service & bdc metadata store. I have no problem connecting using a sql server management studio but any type of connection I make to the datasource on the sql server via SPD gets denied.

Answer 1

Try this... it's work perfectly.

Create Secure store App ID (In Central Admin)

1. Create secure store application id.(select Group) and give rights

2. Set credential(Give username and password) and permission. (for eg. testusername, testpassword)

Manage BDC Service (In Central Admin)

1. Click on Business data connectivity services.

2. Set metadata store permission. And give rights.

Configure SQL Server

1. create new user and give rights.
2. Apply username and password. (for eg. testusername, testpassword)
3. Add this user to database. So it can access perticular database

Sharepoint Designer

1. Create new external content type.
2. Add connection to SQL Server.
3. Set Sql Server name and database name.
4. Select Custom impersonated identity and apply secure store application id.
5. Click ok.
6. Apply username and password. (for eg. testusername, testpassword)
7. Done.