How to add the ApplicationPoolIdentity to a SQL Server Login

  • [アーティクル]

 

The ApplicationPoolIdentity is a virtual account in Windows that is dynamically generated when the application pools is created and takes on the name of the application pool in this manner: IIS Apppool\<name of application pool> . For instance, the application pool MyApp would have a virtual account created under the name IIS Apppool\MyApp when instantiated. Read here for more information about the ApplicationPoolIdentity and here for Windows virtual accounts.

Since Windows is creating a dynamic virtual account for the application pool, there is not a set identity or Windows user account to assign to a SQL login for data access. This makes it difficult to assign the application pool to the SQL login. This blog post shows how to add a SQL login for local and a remote SQL Server to allow the applications hosted in an application pool to access the SQL Server.

Side note: The IIS authentication method, anonymous or Windows, will not make a difference on the access to the SQL Server. The security principle used to connect to the SQL Server is the one setup in the application pool configuration Identity.

image

On a local SQL Server, the login request will appear as the IIS application pool identity. For instance, if the application pool is called AuthTest, the login will appear as IIS Apppool\AuthTest.

On a remote SQL Server, the login request will appears as the machine name since the built in account is attempting to access SQL. For example, the server IIS01 will appear as domain\IIS01$ in a SQL trace.

To validate the connection to SQL, run a SQL trace with the Audit Login Failed and User Error Message events enabled and this will show the account attempting to access SQL. Or, check the SQL log files.

To Add the Account to SQL:

The steps are the same to add the login to SQL for a local or remote SQL Server. However, the identities are different depending on the server if SQL Server is installed locally or on a remote server.

For a local SQL Server:

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server.
  • Open the Security folder at the server level and not the security folder for the database.
  • Right click on the logins and select New Login.
  • For the login, type IIS APPPOOL\AppPoolName and DO NOT CLICK SEARCH and select OK (If a search is executed, it will resolve to an account with ServerName\AppPool Name and SQL will be unable to resolve the account’s SID since it is virtual)
  • Select the defaults for the account and select OK to close dialog

The same can be accomplished using T-SQL:

CREATE LOGIN [IIS APPPOOL\AuthTest] FROM WINDOWS;
CREATE USER AuthTest FOR LOGIN [IIS APPPOOL\AuthTest];

For a remote SQL Server:

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server.
  • Open the Security folder at the server level and not the security folder for the database.
  • Right click on the logins and select New Login.
  • For the login, type Domain\ServerName$ and DO NOT CLICK SEARCH
  • Select OK
  • Select the defaults for the account and select OK to close dialog

Using T-SQL:

CREATE LOGIN [computername$] FROM WINDOWS;

Comments

  • Anonymous
    March 31, 2016
    A clear explanation of the solution. Thanks
  • Anonymous
    June 08, 2016
    Clear concise information and explanation.
  • Anonymous
    August 16, 2016
    Thanks!
  • Anonymous
    November 14, 2016
    Very helpful. Thank you!
  • Anonymous
    February 14, 2017
    This doesn't seem to work unless you also change the Application Pool advanced settings to use LOCAL SYSTEM as the identity for the pool. This then correctly passes through the Domain\ServerName$ as the login. If you leave it on ApplicationPoolIdentity then it passes through NT AUTHORITY\ANONYMOUS LOGIN as the account name.
  • Anonymous
    February 16, 2017
    For a remote server, does this mean that ANY authenticated app pool from the IIS server has access to ALL databases with a login from domain\server$? I love the temporal concept of these ApplicationPoolIdentities, but what if I want to control that AppPool1 only sees DB1, and AppPool2 only sees DB2? (Without creating specific windows accounts).
    • Anonymous
      February 24, 2017
      @Giles, the app pool has to be explicitly set to log into the database. Meaning AppPool1 will have to be explicitly set to log into DB1 and will NOT have access to DB2. The same goes for AppPool2 and so on. Treat the AppPool identity as a specific account and is assigned to the specific database for login rights. Does that answer your question?
      • Anonymous
        March 09, 2017
        I have the same question as Giles. In most cases, we have separate database server from web server. So the SQL server is remote. In this case we have to add domainName\ServerName$ to SQl server database instead of AppPool1,AppPool2.If we add domainname\servername$ to a database A, does that mean all the application pool has access to database A?Thanks,
        • Anonymous
          May 02, 2017
          Yes i think Giles and Anne are true, any request from that machine will have access to that database. i believe there is no way to authenticate specific app pool to a REMOTE sql server.
      • Anonymous
        July 14, 2017
        Thank you for write up. It helped with setting my connection between IIS and SQL server (on a separate machine). I had to adjust the permissions for machine account on sql database to suit my needs. Lesson learned applicationpoo id only works if the site is using only one application pool and or has only one website on the server for multiple websites it is better to use managed service accounts for iis application pools.
  • Anonymous
    April 08, 2017
    The comment has been removed
  • Anonymous
    November 25, 2018
    The "at the server level" part is not so obvious to those without sufficient experience. I assume you mean the "Security" node that is at the same level as the "Database" node.Is "ApplicationPoolIdentity" the same thing as "IIS Apppool" except for the context (in other words where it is that we are at)? That is the implication and if that is true then it would help to say it explicitly. If it is not true then I think some relevant clarification would help.