I created a very, very simple webpage, with no code behind. It runs fine in Cassini and it doesn't run when it is runs under an IIS virtual directory.
All of this makes perfect sense and comes down to what I have been saying for the past couple of posts: it's a security permission issue. Taking your code and run it on my machine wouldn't help. The code is probably fine, it's the security between your IIS account and SQL Server that is the problem.
Again, Chapter 19 and Appendix B provide all the answer as they a) explain how security works, b) how to figure out under which account your site runs and c) how to grant that account permissions to the database.
You seem to be overlooking important parts in these chapters, so here's a quick run-down.
- When you run under Cassini, the web site runs under *your* user account. Since this user account probably has Admin rights everything works fine.
- When you run your site under IIS, it runs under some system account. Which account that is depends. On Windows 7, the default is IIS AppPool\NameOfYourApplicationPool.
- This system account needs permissions to access your SQL Server and your database. For that, you need a login (SQL Server wide) and a user (for your database).
Here's how to make this work:
- Start the IIS Manager
- Locate your Application Pool (called PlanetWrox judging by one of your earlier posts) under the Application Pools node.
- Double-click it and make sure it uses .NET 4 and Integrated pipeline mode. Click OK.
- Right-click the pool and choose Advanced Settings. Under Identity, make sure ApplicationPoolIdentity is selected.
- Switch to the Sites node of the IIS Manager and locate your site
- Right-click it and choose Manage Web Site | Advanced Settings
- Under Application Pool make sure you select the pool you configured in the previous steps.
- Just to be sure, restart IIS (click the name of your machine in IIS Manager and then click Restart)
- Go into SQL Server (Express, as that's what you seem to be using) using Management Studio
- Expand Security | Logins for the *entire server*.
- Create a new login for IIS AppPool\PlanetWrox. As the Login name enter IIS AppPool\PlanetWrox and click OK
- Go into your database, expand Security | Users and create a new user by entering IIS AppPool\PlanetWrox twice. Assign this user to the data reader and data writer *roles* (not schemas)
- Open the web.config and change localhost\SqlExpress to .\SqlExpress. Both should be the same but I have seen situations where the former doesn't work.
- Try again.
You'll find detailed steps for the SQL Server part on page 768 and further. The IIS part is explained on page 715 and further.
If you want to go use SQL Authentication to connect to SQL Server, well then FORGET ABOUT IT!!!!
Not forget about it; but configure it. By default SQL Server doesn't allow SQL Server Authentication. To change that, log in to SQL Server Management Studio, right-click your server and choose Properties. On the "Security" page you can switch modes. However, I recommend not to do this unless you have the need. Using Integrated Security is safer and usually easier to manage.
If all this keeps failing, please work your way completely through Chapter 19 and Appendix B. All of this information is in there, along with detailed steps and an explanation of why you need to do what and where.