SharePoint 2013 Business Connectivity Services Login failed for user NTAUTHORITY IUSR

SharePoint 2013 Business Connectivity Services Login failed for user NTAUTHORITY IUSR

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.

Configure an environment for apps for SharePoint (SharePoint 2013)

Configure an environment for apps for SharePoint (SharePoint 2013)

 

Updated: April 30, 2013

Summary: Configure domain names, service applications, and URLs for apps for SharePoint 2013.

 

Applies to:  SharePoint Foundation 2013 | SharePoint Server 2013 Standard | SharePoint Server 2013 Enterprise 

To enable users to install and use apps for SharePoint in their sites, you must configure your environment to support them. This article describes how to configure your environment to support apps. Use the Plan for apps for SharePoint 2013 article to review options and determine the values to use for configuration settings in this article.

Important:

The steps in this article apply to both SharePoint Foundation 2013 and SharePoint Server 2013. One of the feature updates of the March 2013 Public Update for SharePoint 2013 enables you to use multiple app domains in SharePoint 2013 environments with alternate access mapping or host-header web application configurations. For more information, see Enable apps in AAM or host-header environments for SharePoint 2013.

The following illustration summarizes the steps to take to configure an environment for apps for SharePoint.

Overview of how to configure an environment for apps for SharePoint

These configuration steps result in example app URLs such as the following:

• http://Apps-12345678ABCDEF.ContosoApps.com/sites/SiteName/App1Name/Pages/Home.aspx
• https://Apps-3456789BCDEFG.ContosoApps.com/sites/SiteName/WebName/App2Name/Default.aspx

This article contains instructions for completing these steps.

Before you begin

• You must purchase a domain name from a domain name provider for your apps, for example, ContosoApps.com.
• You must be a member of the Farm Administrators group to perform the steps in this article. For some steps, you must also be a local administrator on the domain controller.
• Confirm that the SharePoint Administration (spadmin) and SharePoint Timer (sptimer) services are running.
  To verify this, click Start, point to Administrative Tools, and then click Services. In the Services list, verify that the SharePoint Administration and SharePoint Timer services are running.

Note:

Because SharePoint 2013 runs as websites in Internet Information Services (IIS), administrators and users depend on the accessibility features that browsers provide. SharePoint 2013 supports the accessibility features of supported browsers. For more information, see the following resources: Plan browser support Accessibility for SharePoint 2013 Accessibility features in SharePoint 2013 Products Keyboard shortcuts Touch

Configure the domain names in DNS (all hosting options)

You must configure a new name in Domain Name Services (DNS) to host the apps. To help improve security, the domain name should not be a subdomain of the domain that hosts the SharePoint sites. For example, if the SharePoint sites are at Contoso.com, consider ContosoApps.com instead of App.Contoso.com as the domain name. For more information, see Plan for apps for SharePoint 2013. When an app is provisioned, it provisions a unique DNS domain name (for example, Apps-12345678ABCDEF.ContosoApps.com, where 12345678ABCDEF is a unique identifier for the app). You need a wildcard Canonical Name (CNAME) entry for your DNS domain to support these unique names.

Depending on your configuration (for example, if you are using WINS forward lookup), you might have to create a new forward lookup zone first, or you can start with a wildcard CNAME entry in the same zone as the SharePoint site domain. In the following procedures, you create a forward lookup zone, and then create a wildcard alias record for the DNS domain name that allows for individual apps to create unique domain names within your app domain. In these procedures, we use DNS Manager for Windows Server 2008 R2. For more information about DNS server in Windows Server 2008 R2, see DNS Server. If you have a different type of DNS server, follow the procedures in the documentation for that server type.

To create a forward lookup zone for the app domain name

1. Verify that the user account that performs this procedure is a local administrator on the domain controller.
2. Click Start, point to Administrative Tools, and then click DNS.
3. In DNS Manager, right-click Forward Lookup Zones, and then click New Zone….
4. In the New Zone Wizard, click Next.
5. In the Zone Type page, accept the default of Primary zone, and then click Next.
6. In the Active Directory Zone Replication Scope page, select the appropriate replication method for your environment (the default is To all DNS servers in this domain), and then click Next.
7. In the Zone Name page, in the Zone name box type the name for your new app domain name (for example, ContosoApps.com), and then click Next.
   The New Zone Wizard shows the new domain name for apps.
   
8. On the Dynamic Update page, select the appropriate type of dynamic updates for your environment (the default is Do not allow dynamic updates), and then click Next.
9. On the Completing the New Zone Wizard page, review the settings, and then click Finish.

For more information about how to create a forward lookup zone, see Add a Forward Lookup Zone.

You have now created a forward lookup zone (and a domain name) to use for apps in your environment.

To create a wildcard Alias (CNAME) record for the new domain name

1. Verify that the user account that performs this procedure is a local administrator on the domain controller.
2. In DNS Manager, under Forward Lookup Zones, right-click the new app domain name, and then click New Alias (CNAME).
3. In the New Resource Record dialog box, in the Alias name (uses parent domain if left blank) box, type *.
   The Fully qualified domain name (FQDN) box displays *. followed by the domain name that you created for apps. For example, *.ContosoApps.com or *.Contoso-Apps.com.
4. Next to the Fully qualified domain name (FQDN) for target host box, type the FQDN of the server that hosts the SharePoint sites.
   For example, SharePoint.Contoso.com.
   Or:
   1. Next to the Fully qualified domain name (FQDN) for target host box, click Browse and navigate to the Forward Lookup Zone for the domain that hosts the SharePoint sites.
      For example, Contoso.com.
   2. And then navigate to the record that points to the server that hosts the SharePoint site.
      For example, SharePoint.
   New Resource Record dialog box shows the wildcard alias for the app domain and the FQDN of the server that hosts the SharePoint sites.
   
5. Click OK.

For more information about how to create a wildcard alias record in DNS Manager, see Add an Alias (CNAME) Resource Record to a Zone.

You can verify the new domain name and alias by pinging them.

To verify the new domain name

1. Verify that the user account that is performing this procedure is a local administrator on the domain controller.
2. Click Start, and then click Command Prompt.
3. At the command prompt, type ping followed by a subdomain of the domain that you created, and then pressENTER.
   For example, ping Apps-12345678ABCDEF.contosoapps.com
   If the ping command returns the correct IP address, then your wildcard for the domain name was configured successfully.

Create a new wildcard SSL certificate

If you are using Secure Sockets Layer (SSL) for the SharePoint sites in your environment, or if you use any apps that use data external to the SharePoint sites, you should use SSL for your apps. To use SSL, you create an SSL certificate for your app domain (for example, ContosoApps.com).

The domain should be added in the form of a wildcard (for example, *.ContosoApps.com). You need a wildcard certificate instead of individual certificates because each installed app has its own subdomain.

Configure the Subscription Settings and App Management service applications

Apps rely on the App Management and Microsoft SharePoint Foundation Subscription Settings service applications. These service applications use the multi-tenancy features to provide app permissions and create the subdomains for apps. Therefore, even if you are not hosting multiple tenants, you must still establish a name for the default tenant for your environment (any SharePoint site that is not associated with a tenant will be in the default tenant).

Note:
You can use the SharePoint Central Administration website to set the default tenant name (also know as the app prefix) for non-hosting environments. You must use Windows PowerShell to configure tenant names for hosting environments. You perform the steps to set the app prefix in the next section, Configure the app URLs to use.

To configure these services, you first start the services in Central Administration. After the services are started, you use Windows PowerShell to create the Subscription Settings service application, and then use either Windows PowerShell or Central Administration to create the App Management service application.

To start the Subscription Settings and App Management services in Central Administration

1. Verify that you are a member of the farm administrators group in Central Administration.
2. In SharePoint 2013 Central Administration, click System Settings.
3. On the System Settings page, under Servers, click Manage services on server.
4. On the Services on Server page, next to App Management Service, click Start.
5. On the Services on Server page, next to Microsoft SharePoint Foundation Subscription Settings Service, clickStart.
6. Verify that the App Management and Microsoft SharePoint Foundation Subscription Settings services are running. The following illustration shows the Services on Server page where you can verify that the App Management and Subscription Settings services are running.
   Services on Server showing the App Management and Subscription Settings services running.
   

To configure the Subscription Settings service application by using Windows PowerShell

1. Verify that you have the following memberships:
   • securityadmin fixed server role on the SQL Server instance.
   • db_owner fixed database role on all databases that are to be updated.
   • Administrators group on the server on which you are running the Windows PowerShell cmdlets.
   An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

   Note:
   If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

   
   
2. On the Start menu, click All Programs.
3. Click Microsoft SharePoint 2013 Products.
4. Click SharePoint 2013 Management Shell.
5. First you must establish the application pool, run as account, and database settings for the services. Use a managed account for the SPManagedAccount (which will be used for the application pool runas account).
   At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the application pool:
   $account = Get-SPManagedAccount "<AccountName>" 
   # Gets the name of the managed account and sets it to the variable $account for later use.
   

   
   
   Where:
   • <AccountName> is the name of the managed account in the SharePoint farm.
   
   
   $appPoolSubSvc = New-SPServiceApplicationPool -Name SettingsServiceAppPool -Account $account
   # Creates an application pool for the Subscription Settings service application. 
   # Uses a managed account as the security account for the application pool.
   # Stores the application pool as a variable for later use.
   

   
   
6. At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the new service application and proxy:
   $appSubSvc = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $appPoolSubSvc –Name SettingsServiceApp –DatabaseName <SettingsServiceDB>
   # Creates the Subscription Settings service application, using the variable to associate it with the application pool that was created earlier.
   # Stores the new service application as a variable for later use.
   

   
   
   Where:
   • <SettingsServiceDB> is the name of the Subscription Settings service database.
   
   
   $proxySubSvc = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $appSubSvc
   # Creates a proxy for the Subscription Settings service application.
   

   
   

For more information, see Get-SPManagedAccount, New-SPServiceApplicationPool, New-SPSubscriptionSettingsServiceApplication, New-SPSubscriptionSettingsServiceApplicationProxy.

You can use either Windows PowerShell or Central Administration to create and configure the App Management service application. The following procedures provide the steps for each method.

To configure the App Management service application by using Windows PowerShell

1. Verify that you have the following memberships:
   • securityadmin fixed server role on the SQL Server instance.
   • db_owner fixed database role on all databases that are to be updated.
   • Administrators group on the server on which you are running the Windows PowerShell cmdlets.
   An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

   Note:
   If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

   
   
2. On the Start menu, click All Programs.
3. Click Microsoft SharePoint 2013 Products.
4. Click SharePoint 2013 Management Shell.
5. First you must establish the application pool, run as account, and database settings for the services. Use a managed account for the SPManagedAccount (which will be used for the application pool runas account).
   At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the application pool:
   $account = Get-SPManagedAccount "<AccountName>" 
   # Gets the name of the managed account and sets it to the variable $account for later use.
   

   
   
   Where:
   • <AccountName> is the name of the managed account in the SharePoint farm.
   
   
   $appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account
   # Creates an application pool for the Application Management service application. 
   # Uses a managed account as the security account for the application pool.
   # Stores the application pool as a variable for later use.
   

   
   
6. At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the new service application and proxy:
   $appAppSvc = New-SPAppManagementServiceApplication -ApplicationPool $appPoolAppSvc -Name AppServiceApp -DatabaseName <AppServiceDB>
   # Creates the Application Management service application, using the variable to associate it with the application pool that was created earlier.
   # Stores the new service application as a variable for later use.
   

   
   
   Where:
   • <AppServiceDB> is the name of the App Management service database.
   
   
   $proxyAppSvc = New-SPAppManagementServiceApplicationProxy -ServiceApplication $appAppSvc
   # Creates a proxy for the Application Management service application.
   

   
   

For more information, see Get-SPManagedAccount, New-SPServiceApplicationPool, New-SPAppManagementServiceApplication and New-SPAppManagementServiceApplicationProxy.

To create the App Management service application in Central Administration

1. In SharePoint 2013 Central Administration, on the Application Management page, click Manage service applications.
2. On the ribbon, click New, and then click App Management Service.
3. In the New App Management Service Application page, in the Service Application Name box, type the name for the service application.
4. In the Database section, in the Database Server box, type the instance of SQL Server where you want to store the database, or use the default server.
5. In the Database Name box, type a database name, or use the default name.
   The database name must be unique.
6. Under Database authentication, select the authentication that you want to use by doing one of the following:
   • If you want to use Windows authentication, leave this option selected. We recommend this option because Windows authentication automatically encrypts the password when it connects to SQL Server.
   • If you want to use SQL authentication, click SQL authentication. In the Account box, type the name of the account that you want the service application to use to authenticate to the SQL Server database, and then type the password in the Password box.

     Note:
     In SQL authentication, an unencrypted password is sent to SQL Server. We recommend that you use SQL authentication only if you force protocol encryption to SQL Server or encrypt network traffic by using IPsec.

     
     
7. In the Failover Database Server section, if you want to use a failover database server, specify the server name.
8. In the Application Pool section, do one of the following:
   • Click Use existing application pool, and then select the application pool that you want to use from the drop-down list.
   • Click Create a new application pool, type the name of the new application pool, and then underSelect a security account for this application pool do one of the following:
     • Click Predefined to use a predefined security account, and then select the security account from the drop-down list.
     • Click Configurable to specify a new security account to be used for an existing application pool. You can create a new account by clicking the Register new managed account link.
9. In the Create App Management Service Application Proxy section, leave the Create App Management Service Application Proxy and add it to the default proxy group check box selected.
10. Click OK.
    The following illustration shows the App Management service application and proxy that were created.
    Manage Service Applications page showing the App Management service application and proxy.
    
    Now you must start the service on the server.
11. In SharePoint 2013 Central Administration, click System Settings.
12. On the System Settings page, under Servers, click Manage services on server.
13. On the Services on Server page, next to App Management Service, click Start.

Configure the app URLs to use

In this section, you create the app domain prefix and the tenant name to use for apps in your environment. The app URL points to your app domain and a prefix that determines how each app is named. If you host multiple tenants in your environment, you must use Windows PowerShell to configure the app URLs.

Use the following procedure to configure app URLs for non-hosting (single tenant) environments by using Central Administration.

To configure app URLs

1. In Central Administration, click Apps.
2. On the Apps page, click Configure App URLs.
3. In the App domain box, type the isolated domain that you created for hosting apps.
   For example, ContosoApps.com or Contoso-Apps.com.
4. In the App prefix box, type a name to use for the URL prefix for apps.
   For example, you could use “apps” as the prefix so that you would see a URL for each app such as “apps-12345678ABCDEF.ContosoApps.com”. The following illustration shows the Configure App URLs page after you have filled in the App domain and prefix.
   The Configure App URLs page in Central Administration shows the App domain and App prefix.
   
5. Click OK.
6. If you will install apps and you have changed the App prefix (also known as the site subscription name), you must perform additional steps that involve restarting the World Wide Web Publishing Service (WWW Service) that hosts the apps.

   Important:

   Restarting the WWW Service will also restart the IIS Admin Service and the Windows Process Activation Service. This will also shut down all Web sites and applications that depend on these services and they may lose existing state and will be unavailable until the services successfully restart. You should plan to perform these steps during a planned maintenance time. To complete the App prefix rename tasks, perform these steps: Stop the SharePoint Timer service. Restart the World Wide Web Publishing Service that hosts the apps. Start the SharePoint Timer service.

   
   

Use the following procedure to configure app URLs for multi-tenant hosting environments.

To configure app URLs by using Windows PowerShell

1. Verify that you have the following memberships:
   • securityadmin fixed server role on the SQL Server instance.
   • db_owner fixed database role on all databases that are to be updated.
   • Administrators group on the server on which you are running the Windows PowerShell cmdlets.
   An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

   Note:
   If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

   
   
2. On the Start menu, click All Programs.
3. Click Microsoft SharePoint 2013 Products.
4. Click SharePoint 2013 Management Shell.
5. At the Windows PowerShell command prompt, type the following commands and press ENTER after each one:
   Set-SPAppDomain <appDomain>
   

   
   
   Set-SPAppSiteSubscriptionName -Name "app" -Confirm:$false
   

   
   
   Where:
   • <appDomain> is the domain name that you created.
6. If you will install apps and you have changed the App prefix (also known as the site subscription name), you must perform additional steps that involve restarting the World Wide Web Publishing Service (WWW Service) that hosts the apps.

   Important:

   Restarting the WWW Service will also restart the IIS Admin Service and the Windows Process Activation Service. This will also shut down all Web sites and applications that depend on these services and they may lose existing state and will be unavailable until the services successfully restart. You should plan to perform these steps during a planned maintenance time. To complete the App prefix rename tasks, perform these steps: Stop the SharePoint Timer service. Restart the World Wide Web Publishing Service that hosts the apps. Start the SharePoint Timer service.

   
   

For more information, see Set-SPAppSiteSubscriptionName and Set-SPAppDomain.

Configure the Internet-facing endpoints feature (Optional)

The SharePoint Store contains apps for SharePoint intended for use with sites that require Internet-facing endpoints. By default, these apps are not available (greyed out and cannot be purchased) because they are incompatible with most sites. However, if your farm is configured to allow internet-facing end points, you can turn on the Internet-facing endpoints feature to show these apps in the SharePoint Store. You turn this feature on in Central Administration.

To configure Internet-facing endpoints for apps

1. In Central Administration, click Application Management.
2. On the Application Management page, click Manage Web applications.
3. On the Manage Web Applications page, select the web application that you want to change.
4. On the ribbon, click Manage Features.
5. In the feature list, next to Apps that require accessible internet facing endpoints, click Activate.
6. Click OK.