locked
ApplicationPoolIdentity IIS 7.5 to SQL Server 2008 R2 not working. RRS feed

  • Question

  • User781251464 posted

    I have a small ASP.NET test script that opens a connection to a SQL Server database on another machine in the domain. It isn't working in all cases. 

    Setup:

    IIS 7.5 under W2K8R2 trying to connect to a remote SQL Server 2008 R2 instance. All machines are in the same domain.

    Using the ApplicationPoolIdentity for the web site it fails to connect to the SQL Server with  the following:

     Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    However if I switch the  Process Model Identity to NETWORK SERVICE or my domain account the database connection is successful.

    I've granted the <domain>\<machinename>$ access in SQL Server. 

    I am not doing any sort of authentication on the web site, it is just a simple script to open a connection to a database to make sure it works. 

     I have Anonymous Authentication enabled and set to use the Application pool identity.

     How do I make this work? Why is the ApplicationPoolIdentity trying to use ANONYMOUS LOGON? Better yet, how do I make it stop using Anonymous logon?

    Thursday, December 30, 2010 4:12 PM

All replies

  • User889180217 posted

     Hello,

     Refer http://msdn.microsoft.com/en-us/library/ms998358

    Sunday, January 2, 2011 3:32 PM
  • User744767459 posted

    Hi,

    By default, the application pools run with a unique identity based on the Application Pool name. Unlike a real user / domain user account, this new identity does not have a user profile. This is the reason that switch the  Process Model Identity to NETWORK SERVICE or your domain account the database connection is successful.

    If you must use Anonymous Authentication, you can either switch the Process Model Identity to another account, set Anonymous Authentication to use a custom account or impersonate a user account in your application.

    Thanks.

    Monday, January 3, 2011 2:41 AM
  • User781251464 posted

    So is there no way to allow the Application Pool Identity to authenticate to the SQL Server? 

    Parts of the site will have database access, but not require a logged in user. I don't have a problem using a domain account to authenticate the web application to the SQL server, I was just hoping to use the new App Pool Identities.

    Thank you. 

    Monday, January 3, 2011 11:52 AM
  • User2103511619 posted

     Yes, it can be done. Here're the steps necessary:

    • In SSMS ...
      • connect to your SQL Server and navigate to Security - Logins in Object Explorer.
      • Add a new login:
        • Create a Windows login with the name of your application pool. Use the virtual domain name "IIS AppPool", like: "IIS AppPool\{YourApplicationPoolName}"

          (Dont' forget to use the real application pool name after the backslash, e.g.: "IIS AppPool\DefaultAppPool")
        • Assign the new login to your database(s) in the User Mapping page of the New Login dialog.
          Give it any user name you find appropriate.
        • Save and close the New Login dialog.
      • In Object Explorer ...
        • Navigate to your database, then navigate to Security - Users
        • Navigate to your new user and set permissions according to your needs.

    That's it.
    Monday, January 3, 2011 2:53 PM
  • User781251464 posted

     AxelD,

    I don't know that this will work seeing as the SQL Server isn't on the same box as the IIS Server. I think that the IIS AppPool\<apppoolname> is a local account correct? At any rate, I'm unable to add the IIS AppPool\<apppoolname> to SQL Server as a login.

    I can add the machine for the remote web server from the sql server like so:

    <domain>\<machinename>$

    This doesn't seem to resolve the problem.  

    Monday, January 3, 2011 3:53 PM
  • User-107326002 posted

     Hi there, I know this is kinda old. But does anyone figure this out ? I have the exactly same question.

     

    Thanks,

    Sam

    Tuesday, July 12, 2011 4:34 PM
  • User838046225 posted

     I have tried to CREATE  "IIS APPPOOL\ASP.NET v4.0"  just like the book I am reading says to do.  And get this error:

    "Create failed for login 'IIS APPPOOL\ASP.NET v4.0"  "Windows NT user or group IIS APPPOOL\ASP.NET v4.0 not found. Check the name again.

    I am using Windows 7 x64  and SQL Server 2008 develper.

    I am new to SQL and IIS.  What am I doing wrong?  Do I need to set up something in IIS, so when I create the login, it can find it?  I must just be missing some steps.  SQL will not let me CREATE a login for IIS.

    Thanks,

    Dave

    Sunday, August 21, 2011 4:04 PM
  • User-864475932 posted
    Hello any luck with this situation? I have exactly the same setup and got the same error. I figured out how to add the default app pool to SQL as login but it doesn't help either. If anyone ever figured this out can you please post the solution? Many thanks!
    Sunday, August 28, 2011 10:46 PM
  • User-356119435 posted
    Same issue here. Would be very excited to see a solution.
    Thursday, September 22, 2011 3:31 PM
  • User745092963 posted
    http://msdn.microsoft.com/en-us/library/ms187662.aspx

    The trick is to make sure your connection is via the shared memory protocol. Apparently there is a hard coded block on loopback connections.
    Thursday, September 29, 2011 6:50 PM
  • User71461234 posted

     Just if anyone is yet looking for this, the trick is to write down the IIS Application pool as it is when you add a new login. Don't search for it!!

    More information here: http://stackoverflow.com/questions/1933134/add-iis-7-apppool-identities-as-sql-server-logons

     

    Thursday, August 2, 2012 10:27 PM
  • User478103741 posted

    Sorry to bring up this old thread.

    When following AxelD's steps in SQL Server 2012, I'm getting an error code 15401 - Create failed for Login 'IIS_APPPOOL\myAppPool'.

    How should correct this error? Thank you!

    Saturday, June 1, 2013 3:32 PM
  • User521890537 posted

    Do you use IIS and SQL on same server? The solution is not applicable when SQL runs on a different server.

    Monday, June 3, 2013 3:54 AM
  • User478103741 posted

    They run in the same Windows 8 Pro x64.

    Monday, June 3, 2013 1:52 PM
  • User629428583 posted

    If this question is about how to execute _sp_send_dbmail in msdb database (using the SQL send Database Mail stored procedure in msdb), here is some work around.
    Add the .net application user name of your database (which is define on your connection string in your .net app) to the msdb user with "DatabaseMailUserRole" role membership

    Thursday, July 24, 2014 8:49 PM