SharePoint Business Connectivity Services - Part 3

BCS Login failed for user NT Authority

If you have created an external content type and are trying to access it in a list and get Access Denied that is because you have not given permissions to the logged in user to use that external content type. You can easily give permissions to that user or to all users in your site by going to central administration site and configuring business connectivity service.


Some users get another very interesting error message after they have assigned permissions on the external content type. The error message is:

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

Figure 9: Login failed for user ‘NT Authority\IUSR’

This error occurs because database does recognize the credentials passed from SharePoint. Which database? Database that you are connecting to in your external content type! It depends on how you have setup application pool identity in SharePoint. If you are not using managed service account for your services, you are most likely to get this error. This is not likely to occur in corporate environments but in home or test environments where users use LOCAL SYSTEM OR NETWORK SERVICE in application pools, this error will occur. This is because NETWORK SERVICE translates to NT AUTHORITY\IUSR when credentials are passed from web server to database server. IUSR is used when user credentials are not available, for example, for anonymous users. To resolve this 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 by go to the web server and updating application pools or by logging into SharePoint central admin site and going to Manage Service Accounts. We will cover second scenario here, that is, to add IUSR to the database.

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

2. Expand Security node and right-click Logins node and select New Login.

Figure 10: Add New Login

3. In the Login Name, enter IUSR and click Search... This will open a new search box. Enter IUSR in the object name and click Check Names. Click OK.

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

Figure 11: Add IUSR

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

5. Now you may think that the user has been added and your external content type will work. Right? Wrong! You still have to map user 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 the database caused an error.

Figure 12: The query against the database caused an error

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

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

Figure 13: Login properties

7. There are two ways to do this mapping. One way is to add user to one of the server roles, for example, serveradmin or sysadmin. This will give user full rights to all databases and thus you won't have to do explicit mapping. To do this, on the properties page, click Server Roles and check sysadmin.

Click OK. Obviously you would not want to do this in real environment. So the other option is to map the user directly to the database that has been used in the external content type.

Figure 14: Login properties

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

Figure 15: User mapping

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

Figure 16: External list in browser