System and Application Softwares
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.
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:
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:
CREATE LOGIN [computername$] FROM WINDOWS;
© 2020 Digcode.com. All rights reserved.