2

I have been going crazy trying to figure out why this is not working. I followed Fabian Williams blog entry to the letter. I have a development farm with everything on a single farm. I DID NOT use the configure the farm wizard and install services manually. I am logged into the server with the setup account (spSetup) and not the farm account (spFarm). I can access the Secure Store Service Application with the spSetup account and seemingly create Target Applications. I created a test database called BDCImportTesting which spSetup does not have privileges on. In AD I created a security group called BCSGroup and user account called BCSAccount. I added the user spSetup as a member of the BCSGroup security group. For BCSAccount, I added it to the databases server and made it the db owner of BDCImportTesting.

I went through the process of creating the Target Application withe the name BDCGroupTest setting up spSetup as a Target Application Administrator and BCSGroup as a Target Application Member. I then set the credentials to BCSAccount and opened up SharePoint 2013 Designer (32-bit). SPD is running under the spSetup account. When I try to create the external content type connection to SQL Server using the impersonate Windows Identity with the Target Application BDCGroupTest as the Secure Store Application ID, I get the error:

Cannot connect to the LobSystem Reason:
"Cannot open database BDCImportTesting requested by the login.
The login failed.
Login failed for user: Domain\spsetup
Cannot open database: "BDCImportTesting: requested by the login. The login failed.

Login failed for user: Domain\spsetup

I checked the Eventviewer, ULS, SQL logs, etc. and same message. But I am a bit confused, if I am using the secure store application id, why is it listing the account that I am logged into SPD as the one that is failing? Shouldn't it be account that I entered the privileges for in the Secure Store Service page? It almost seems like to me that it is ignoring the Secure Store Application ID and is using the SPD user account. Sometimes I get challenged for credentials and I have tried all combinations of credentials like the farm account, the spsetup account, the bcs account in SQL Server, but nothing works. I have tried making changes and doing IIS Resets. That doesn't work either. Could this failure be a result of me doing everything on a single box?

I am at a loss and any insight on how to troubleshoot this issue would be greatly appreciated!

Improve this question
2
  • You have likely done so, but thought I'd suggest: turn on verbose logging and check your ULS again.
    – Phil Greer
    Commented Jul 11, 2014 at 15:22
  • I have for Business Connectivity and Secure Store. The only exception I am seeing is for the BCS: MetadataObjectNotFoundException before marshalling and rethrowing on client side. It cannot find the IEntity for the requested entity. Not sure why it is throwing that error since I am in the process of creating the ECT.
    – Mario Pereira
    Commented Jul 11, 2014 at 15:43

1 Answer 1

Reset to default
1

A little-known "feature" of SharePoint Designer -- it always connects to the data source using the logged-on user. :) The Secure Store information is only used at run-time by the BCS Service Application.

Best Practice would be to create the model connected to a development instance of the database. Then export the model and update the LOBSystemInstance element to point to production, then import into the production SP environment.

Improve this answer
3
  • I had a sneaking suspicion that may be the case. I went ahead and added the account spSetup to the database as a datareader and viola, I was able to make the connection. I created the ECT and removed the permissions from the database for spSetup. I went through the process of creating the forms and I was able to access the ECT on the site. I then removed the user from the BCSgroup I created above following Fabian's blog, but I was still able to access the site. Suffice it to say, irrespective of the Secure Store Credentials, it appeared that access to the ECT was being controlled by BDC.
    – Mario Pereira
    Commented Jul 11, 2014 at 23:44
  • ...and not the Secure Store.
    – Mario Pereira
    Commented Jul 11, 2014 at 23:45
  • Paul how do you update the LOBSystemInstance to Point to Production after importing the model.
    – user10711
    Commented Apr 11, 2016 at 20:31

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.