How to add the applicationpoolidentity to a SQL Server Login

The applicationpoolidentity is a virtual account in Windows are dynamically generated when the application pools is CR Eated 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 has a virtual account created under the name of IIS Apppool\myapp when Instan Tiated. Read about the applicationpoolidentity and here for Windows virtual accounts. Information.

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

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

On a local SQL Server, the login request would appear as the IIS application pool identity. For instance, if the application pool is called Authtest, the login would appear as IIS apppool\authtest.

On a remote SQL Server, the login request would appears as the machine name since the built in account are attempting to ACC ESS SQL. For example, the server IIS01 would 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 T His would show the account attempting to access SQL. Or, check the SQL log files.

To Add the account to SQL:

The steps is the same to add the login to SQL for a local or remote SQL Server. However, the identities was different depending on the server if SQL Server was 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 does not CLICK search and select OK (If a SEARCH is executed, it'll resolv E to a account with Servername\apppool Name and SQL would 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 does 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;

Web. config SET integrated security=sspi;

How to add the applicationpoolidentity to a SQL Server Login