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 (10)

  1. stef says:

    A clear explanation of the solution. Thanks

  2. Jez says:

    Clear concise information and explanation.

  3. Andrew says:

    Thanks!

  4. TTatum says:

    Very helpful. Thank you!

  5. 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.

  6. Giles says:

    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).

    1. Eric Parvin says:

      @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?

      1. Anne says:

        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,

        1. Khaled says:

          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.

  7. Kathleen Elizabeth West says:

    You are my hero. This is probably my 20th site I looked at to try and solve the problem. I was creating my first service application in Visual Studio (Northwinds project) and was scratching my head for hours trying to figure out why it could not connect to the database in Microsoft SQL Server. I knew the problem was in the SQL server and guessed it needed a login credential for the DefaultAppPool (the verbose error message told me this). Problem was, I could find nor add this user because I was going through the SEARCH method to find and validate my virtual user when adding a new user in SQL server. Then there was a bug if I did that THEN tried to add it without the search, it would give an SQL exception. Trick is to not do the search method at all like you said the FIRST time. I was dancing when I was finally able to add IIS APPPOOL\DefaultAppPool to my SQL server. There is a trick to get this user account into your SQL logins the first time without a validation exception. For the folks reading, just a reminder you have to setup a login on your northwinds database in addition to the server.

    Thanks!!!!

    For The Search Engines To Find This Solution:
    Cannot open database “Northwind” requested by the login. The login failed. Login failed for user ‘IIS APPPOOL\DefaultAppPool’.

    This article shows you how to solve this problem!

Skip to main content