Thursday, May 10, 2012

IIS Application Pool Identity access to SQL Server

I recently had a need to connect to a SQL Server Express 2008 R2 instance using the 'Integrated Security=SSPI" option in my connectionString setting. Of course when I ran the site the first time, I got an error stating that IIS APPPOOL\{MyApplicationPoolName} could not access the database. So after a little searching, I found this post ApplicationPoolIdentity IIS 7.5 to SQL Server 2008 R2 not working. on the IIS Forums that provided the proper steps to grant the Application Pool Identity access to the database.

The necessary steps from the post are below for reference.

In SQL Server Management Studio ...
    • 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.


Carol Bruns said...

This was the same error once I faced too but with the help of my friend I did get it solved. Actually the thing is that now I am getting to know what exactly the problem was.

williamson david said...

Good details about access to sql server.

VB6 to C# | Migrate Access to SQL Server