Is it possible to create external content types without secure store?

Question

Our organization is going to be using Office 365 - D (BPOS-D) as our SharePoint environment. I'm told we can't use secure store in this environment. So how do we use SharePoint to connect to an external SQL database without that?

1. Using SharePoint Designer I can create an external content type and view all the tables in the database. I select the table I want to use in an external list.
2. Then I create an external list with the external content type
3. When I attempt to view the site though, I get a "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

I believe all the correct accounts are created, but for the connection settings I believe I have tried all the authentication methods presented in the dropdown. I would like it to connect user the current user logged into the site viewing the data, but I don't see how that is possible without secure store.

Answer 1

You get the 'NT AUTHORITY\ANONYMOUS LOGON' because You probably use NTLM which doesn't support passing credentials when You need to make more than 1 "hop" between the servers. To make it work You have to set "RevertToSelf" option on the external content type. But to do it, You have to enable it first on the server where You have Your SharePoint installed with the powershell command:

$apps = Get-SPServiceApplication
$bcs = #Do something appropriate here to get the app that is
#BCS. If you’re doing this by hand, just type $apps and look
#for the Business Data one, then index into it like $apps[i].
#If you’re doing it for automation, filter by
#$_.GetType.FullName (not $_.TypeName, which is localized).
$bcs.RevertToSelfAllowed = $true 

You can read more about "RevertToSelf" here and the full solution for this problem is here