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.
