Setting up Business Data Connectivity on SharePoint 2010

I have a SharePoint 2010 deployment where the SharePoint server sits in a DMZ outside of a firewalled domain and individual accounts are set up on the server to then authenticate to the SharePoint site.  The backend is a SQL Server 2008 server inside the firewalled domain.  I am trying to set up my first External Content Type.  I get to the database connection screen.  I know what the server is and the database name.  I don't know which to select between Connect with User's Identity, Connect with Impersonated Windows Identity, and Connect with Impersonated Custom Identity.

I have read articles and watched videos on having to go into Central Administration and set the permissions once the content type is configured, but I can't seem to get past this step.

Any help or guidance with this would be greatly apprecitated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mounty95Author Commented:
To add onto this troublesome problem.  I was able to select the first radio button for Connect with User's Identity and was able to connect and then just went through the process of creating pages or such.  I went into Central Administration and set the Permissions for the External Content Type to all Authenticated, All Users (windows), and All Users (forms.aspnetsqlmembershipprovider) and checked all four boxes for Edit Execute, Selectable in Clients, and Set Permissions (at this point I am just trying to test and get this to work, I can go back and modify permissions at a later point).

I go to a SharePoint site, go through the process of creating a list from External List.  And it comes back login failed for user "NT Authority\Anonymous Logon"

So now I am stuck at a different place.

Please help!!!!!!
Have you enabled the Sharepoint Claims To Windows Token service in CA?  The “Claims to Windows Token Service” must be running on the farm;   see Central Admin-> Services on Server

Have you got a "Business Data Conection Service" proxy configured in CA?

Have you configured Sharepoint Secure Store  to configure the connection to the database?Sharepoint Secure Store

I've only done this inside a domain, using "simple" (not claims based) authentication, so you may need to configure extra stuff I know nothing about in your specific case.  I would recommend you initially try to set up a simple SQL Authenticated connection to a test database (with no real data) to get this established, preferably on a dev SharePoint farm inside your domain.  It's a bit of a faf to get it going even in a simple environment, but not complicated once you know how and understand what each bit is doing.  If you can work your way through this in a simple environment you shoul dbe able to scale it up to the live system.

If you do not have a dev system then I would recommended you ask for one before you try anything like this, since you are potentially putting your system at risk working on the live system, and also you are potentially setting up a security hole.  You can have as many dev SharePoint/SQL instances as you need if you buy an MSDN licence.

Since this is going to sit in a DMZ, I would also recommend you get outside help in sorting this from a good MS partner, just to ensure that everything is sorted.  I've been running SharePoint farms since 2003 and this is not something I would be comfortable in doing myself without help :)
mounty95Author Commented:
After several days of fooling around with this and setting up the Secure Store Service, setting the credentials within the secure store, building a new External Content Type selecting the application ID that I created, setting the credentials on the External Content Type, I have gotten a little further along, but the error now is Unable to display the Web Part which I researched and it isn't because of too many records, right now just trying to retrieve some 20 records.  When I open it up in SPD it is returns this error:

soap:ServerException of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.An error has occurred.

So not sure what to do now.  I started with authentication issues, but those seemed to go away once I got the Secure Store service setup and had it configured with a SQL account that I created specifically for this.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

A soap exception is from the web services.  This API has awful error reporting through the front end.  However you can use this free tool to see what is really happening:

Dowload  Fiddler

Run this, and you can watch the http traffic between the web session/SPD and the web services API.  Use teh "inspectors" tab to see what is going on in each request.  You should see a request in red, and you will be able to read whatever issue SOAP is reporting, or at teh very least you will see teh XML of the command that fails.

Most likely it will be "permission denied", but try it out an it should become clearer.

Another source of errors is the ULS logs.  I would recommend you you use somehting like:
to look at them, and see if any back end errors are being reported.
mounty95Author Commented:

Thank you for all of the leads and trying to point me in the right direction.  So I pulled the log file and am seeing errors that start with SecretKey is not initialized, SyncMasterKey-No passphrase found in memory, RefreshMasterKey-Masterkey refreshed failed, The Microsoft Secure Store Service application Secure Store Service failed to retrieve the master secret key.  The rror returned was 'Unable to obtain master key'.

I clicked the Generate New Key under the Secure Store Service when I established the target application.  Am I missing something?  Is there some other masterkey that I am supposed to be looking for?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
Thank you for all of the direction.  Using the tools and resources that you pointed me to I was able to determine that there was an issue with the key generated.  I deleted the Secure Store that I had created, used the default Secure Store, started over, and my problem was resolved.
No problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.