Give ASPNETDB.MDF SQL DB IIS_IUSRS Permissions

Question

Have been getting pretty bald over this situation!

I am using MS VS 2010 C# ASP.NET, and MS SQL EXPRESS 2008, IIS 7 on Windows 7.

I have the asp.net login control on a page. It works quite flawlessly in development environment.

When I move the site to IIS, the login aspx page is displayed and all seems to be working fine. As soon as I put in my login credentials (which have been tested on the same database in MS VS Dev server) and click login, I am presented with an IIS error that states I do not have permission. (Please see stack trace)

I have done some digging, and read that when MSVS creates the database for the login control, it gives permissions to the current user. Therefore, when we move this to our production server (its not really a server, its just IIS on one of our win 7 machines to test) and try to login via the internet, it seems as if IIS_IUSRS does not have permissions.

Things I have tried

1: Detaching the database from the solution, attaching it in SQL Server Manager, and trying to set the permissions there. Couldn't recognize IIS_IUSRS account at all. (I tried computer name / IIS_IUSRS too). This probably makes sense since IIS_IUSRS is not really a windows account. I also had the same issues trying to give permissions for NT AUTHORITY.

2. I was thinking if I could somehow create the database from IIS_IUSRS it will automatically figure out the plumbing for me, but wasn't sure where to start here, or if thats possible.

This is my connection string that I am using to connect to the db:

  <connectionStrings>
    <remove name="ApplicationServices"/>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
  </connectionStrings>

Any help is greatly appreciated, I have been stuck for quite some time now.

Answer 1

This may be a little specific, but there are most surely some solutions for others in this mix. Here is what I had to do to port my asp.net website from a Development environment (MS VS 2010, SQLEXPRESS) to IIS 7.

What this will explain, is how to set up two web application under one website (Website1, and Website2). Website1 will use aspnetdb.mdf (created by visual studio) and Website2 will use website2 database which was manually created in MS SQL Server 2008. We will be porting the aspnetdb.mdf to sql server 2008 as well for ease of manipulation.

Website1 will also have permissions to create files and directories on website2.

Section 1 IIS Setup

****Step 1:**** create a new folder in wwwroot to hold both applications (We called it websites) Copy paste "WebSites" folders for website2 and website1.

Step 2: App pool for website1 If not already created, create the Application pool and configure it to use .NET framework 4.0, Integrated pipeline mode, we called it asp4.

App pool for website2 Currently uses App pool for website1

Step 3:

Add website1 In IIS MANAGER, add new website. Point its physical directory to website1 that we just copied into the new folder on the root (Section 1, Step 1). Select the application pool for website1, See Section 1, step 2. We set the site name the same as the physical directory (website1).

Verify other requirements, Section 2 website1 DB Setup and IIS folder permissions.

Add website2 In IIS Manager, right click the new website application we just created, select "Add Virtual Directory".
Set the physical path to the website2 folder that we copied to our new folder on the wwwroot holding both applications, See Section 1, step 1. Convert to application and select the application pool for website1, See Section 1, step 2. We set the alias to the same as the physical path folder, website1.

Currently, there are no connection requirements to pass through. Verify other requirements Section 3 - website2 Database setup for website2

Section 2 website1 DB Setup and IIS folder permissions

Step 1 aspnetdb.mdf adjustments: Note: From now on will probably just use most recent aspnetdb.bak file.

Attach aspnetdb.mdf into ms sql server by rightclicking database's and attaching DB.

Step 2 - IIS User logon If user is not set up - Section 4, Step 1 Create a DB Logon for the IIS USer

if permissions not setup - Section 4, Step 2 Setting Permissions for the IIS USER on the db

Code Adjustments / Verifications need to remove user instance variable (;User Instance=true) auto generated in connection string by MS VS in web.config.

-    <remove name="ApplicationServices"/>
-          Not 100% neccessary but this will make it more scalable accross platforms
-       In the web.config, make sure we are using the database we attached /        restored on MS SQL Server.
    -     "data source=.\SQLEXPRESS;Integrated Security=SSPI;initial catalog=aspnetdb" (because we renamed it aspnetdb)

Section 3 - website2 Database setup for website2

Make sure DB is restored on MS SQL SERVER Database requirements

Step 1

If user is not set up - Section 4, Step 1 Create a DB Logon for the IIS USer

Step 2

if permissions not setup - Section 4, Step 2 Setting Permissions for the IIS USER on the db

Step 3 Folder permissions (after child site website2 is added)

Set folder permissions : Since website1 writes files and creates folders in website2, (its set up to be always looking in the parent folder for website2 folder) we need to give server IIS_IUSRS , ours is (IIS_IUSRS(MIKE71\IIS_IUSRS) permissions.

From within IIS, rightclick the nested application website2 and select edit permissions. Ensure that modify and write priveledges are checked off for IIS_IUSRS.

Section 4 - Adding IIS user to databse and giving permissions

Step 0 Attaching and restoring the Databases

• If the database is not already attached to MS SQL Server, we need to attach the .mdf file.
• We can restore the database by rightclicking Databases, restoring to our Attached Database, and restore from supplied .Bak file.
• If having troubles restoring because of database definitions (creating a clean one from scratch) try: RESTORE DATABASE trimweb FROM DISK = 'C:\trimweb.bak' WITH REPLACE

Step 1 Create a DB Logon for the IIS USER

Why? Need create and allow IIS APPOOL\asp4 user to have permissions to databse, set as data reader (the application pool created in Section one, Step 2)

How: In the security section in MS SQL SERVER, need to right click users and add IIS APPPOOL\asp4 (IIS APPPOOL\apppoolname, created in Section one, Step 2). Add IIS 7 AppPool Identities as SQL Server Logons

Step 2 Setting Permissions for the IIS USER on the db

Right click on the database name, (website2 db) and click properties. In permissions section add your APPPOOL user that you just created in Section 4, Step 1, be sure to give him Execute and Select permissions.

Note: Website1 needs Execute, Select, Delete, Insert permissions as well. Website2 is also using this app pool which it shouldnt be because we dont not need to alow the user to have that kind of functionality from within the trimdynamics application. We could seperate the app pools so that our trim dynamics IIS USRS is only getting Execute and Select permissions.

Answer 2

This whole idea of using a SQLEXPRESS UserInstance connection string on a production IIS server is fraught with difficulties. What you need to do is attach the database to the real SQL Server that is in your production environment and use a regular SQL server connection string without the "UserInstance" directive. This is my beef with SQLEXPRESS because it causes so many problems for beginning developers who "think" you can just port your local development environment to a real server. Almost all of the time, you cannot.