SharePoint 2013 Business Connectivity Services Login failed for user NTAUTHORITY IUSR

If you have created an external content type and are tryingto access it in a list and get AccessDenied, that is because you have not given permissions to the logged inuser to use that external content type. You can easily give permissions to thatuser or to all users in your site by going to central administration site andconfiguring business connectivity service. For exact steps on how to do it,read my following blog:

SharePoint 2013 Business Connectivity Service Access DeniedError: http://blog.walisystemsinc.com/2012/11/sharepoint-2013-business-connectivity.html

Some users get another very interesting error message afterthey have assigned permissions on the external content type. The error messageis:

Message from ExternalSystem: ‘Login failed for user ‘NT AUTHORITY\IUSR’.’.

 This error occurs because database does recognize thecredentials passed from SharePoint. Which database? Database that you areconnecting to in your external content type! It depends on you have setupapplication pool identity in SharePoint. If you are not using managed serviceaccount for your services, you are most likely to get this error. This is notlikely to occur in corporate environments but in home or test environmentswhere users use LOCAL SYSTEM OR NETWORK SERVICE in application pools,this error will occur. This is because NETWORKSERVICE translates to NTAUTHORITY\IUSR when credentials are passed from web server to databaseserver. IUSR is used when usercredentials are not available, for example, for anonymous users. To resolvethis problem, either change application pool identity or add NT AUTHORITY\IUSR to the database permissions.The first scenario is quite common. You can change application pool identity bygo to the web server and updating application pools or by logging intoSharePoint central admin site and going to ManageService Accounts. We will cover second scenario here, that is, to add IUSR to the database.

1.      Go to MicrosoftSQL Server Management Studio and connect to your database server.

2.      Expand Securitynode and right-click Logins node andselect New Login.

3.      In the LoginName, enter IUSR and click Search…. This will open a new searchbox. Enter IUSR in the object nameand click Check Names. Click OK.

4.      You will notice that Login Name has been populated with MACHINENAME\IUSR where MACHINENAMEis your machine name, for example, in the figure below, you see SP2013\IUSR. SP2013 is my machine name.

This is not what you want to add to the logins. Change MACHINENAME to NT AUTHORITY so Login Name shouldread NT AUTHORITY\IUSR. Click OK.

5.      Now you may think that the user has been addedand your external content type will work. Right? Wrong! You still have to mapuser to the database otherwise you will get following error on the list page(where you are trying to load external content type):

The query against thedatabase caused an error.

 

 

This error occurs, as I said, because user is not mapped tothe database yet.

6.      To map user to the database, right-click NT AUTHORITY\IUSR in Logins and select Properties.

 

7.      There are two ways to do this mapping. One wayis to add user to one of the server roles, for example,  serveradminor sysadmin. This will give userfull rights to all databases and thus you won’t have to do explicit mapping. Todo this, on the properties page, click ServerRoles and check sysadmin. Click OK. Obviously you would not want to dothis in real environment. So the other option is to map the user directly tothe database that has been used in the external content type.

To add user mapping, in LoginProperties, click User Mapping.Locate the database in the list and then check the box in the Map column. As soon as you check thebox, NT AUTHORITY\IUSER appears inthe User column. That’s it. Click OK to save the setting. By the way onthe same properties page, you can also assign database role membership to theuser for the database but that is not required.

Now, go back to the list page and reload it. You will seeresults from the external data source.