Why Does SharePoint External Content Item Picker Not Select Item When OK is Pressed and logs a 401 Error in the ULS?

Question

I have a simple test scenario set up that uses 2 SQL Server tables.

TEST_MainTable
ID Name LookupValue
1 Blue 1
2 Red 2
3 Green 3
4 Yellow 2

TEST_LookupTable
ID Description
1 Value A
2 Value B
3 Value C
4 Value D
5 Value E

I created a SharePoint External Content Type with all operations for both tables.

I created an association that maps the LoopkupValue column of TEST_MainTable to the ID of TEST_LookupTable.

I can create an external list for both content types and see and manipulate the data.

The problem is with the picker. When I use New Item or Edit Item, I can manually type an ID in the LookupValue field and hit the validation button and get the underline showing it validated my input against the foreign key ECT. But if I open the picker and pick one of the lookup codes and then hit the OK button in the picker, nothing happens. If I wait about a minute, the picker will go blank and the message "Connection has been reset" is displayed.

I have been trying everything I can think of for several days now to no avail. I have removed and recreated the ECT countless times. Recycled the app pool of the site. Tried it with out a foreign key on the SQL table. It makes no sense to me why the validation button will immediately underline the value (or give a red error message if the value is invalid) when pressed, but the picker will not load the item into the field when a selection is made and the OK is clicked. I am using SP Designer to do all the work. I have also gone into the model in Central Admin after creating the ECT and given Selectable in Clients permission to all Authenticated users on both ECTs.

Just before posting this question, I discovered that the issue appears to be permissions. The ULS log is logging a 401 for the picker "because the user is not authenticated and resource requires authentication". Yet, it gets the list contents and does the validation so there is no problem with the connection credentials. It just throws a 401 when I try to pick the item from the picker. I am using a secure store application ID I set up to connect to the database and I know that in the connection properties there is a Default and a Client tab and I have made sure that both are set with the same connection method and secure store application ID. I have tried changing the connection settings to use all of the authentication methods offered, but it does the same thing in all circumstances, except if I use "User's Identity". If I use "User's Identity" the list won't display and I get an error that it is trying to connect with anonymous instead of my windows account and I am suspecting that may be related to this issue. I checked the web app and it is set to use Integrated Windows Authentication Negotiate (kerberos).

Answer 1

SOLVED - This has been resolved with the help of Microsoft and our network team. We were using http instead of https for our internal SP site and our network was blocking some traffic between the client and the SP site due to http vulnerabilities. Our SharePoint admin installed a cert and changed the site to use https instead of http. The picker is now working correctly.