IIS failure

vbboyd · January 29th, 2012, 05:38 PM

I am sorry I am running both SQL Express and SQL Server 2008 the developer's edition. But I have both SQL Express and regular SQL running under the Enterprise Management Studio. I got my terms mixed up with Oracle. Not Enterprise Manager but SQL Server Management Studio. My bad.

Imar said:
This is confusing. Are you using Express or the full commercial version? And which version of Express? What did @@version tell you?

It says version: Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) Feb 25 2011 13:56:11 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

vbboyd · January 29th, 2012, 08:33 PM

Code:

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. Same error message as before:

 <%@ Page Language="C#" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
    //  Create a new connection object
    SqlConnection conn = new SqlConnection(
        "Server=localhost\\SqlExpress;Database=Employees;" +
        "Integrated Security=True");
    // Create a new command object
    SqlCommand comm = new SqlCommand(
      "SELECT EmployeeID, Name FROM Employees", conn);
    // Open connection
    conn.Open();
    // Execute the command
    SqlDataReader reader = comm.ExecuteReader();
    // Read and display the data
    while (reader.Read())
    {
      employeesLabel.Text += reader["Name"] + "<br />";
    }
    // Close the reader and the connection
    reader.Close();
    conn.Close();
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
  <head runat="server">
    <title>Using ADO.NET</title>
  </head>
  <body>
    <form id="form1" runat="server">
      <div>
        <asp:Label ID="employeesLabel" runat="server" />
      </div>
    </form>
  </body>
</html>

I don't know, maybe you can take this snippet and see if you can reproduce on your local machine at home or something. I am totally lost on this issue.

vbboyd · January 29th, 2012, 11:19 PM

Just so you know, I put the Planet Wrox.MDF file back under the App_data folder in the server explorer in Visual Studio 2010 changed the connection string back to the way it originally was and it runs just fine under IIS control. But if try to change the connection string to try to go to the instance that runs under SQL Server 2008, it croaks out again. Go figure!!! I am a total loss for words and I don't know what can be done to fix the problem, other than to run all of my ASP.NET 4.0 apps with the .mdf file running under the App_data folder in Visual Studio 2010. Maybe you might have some ideas as to what is going on here, but as for me I have no clue. To tell you the truth I am beginning to think that there are actual gremlins inside the machine that are mucking things up deliberately :-) or ;-) is better face to use.

Thanks again.

Vbboyd

vbboyd · January 29th, 2012, 11:22 PM

PS I wanted to add that I am not able to connect to SQL server except in only the Windows Authentication mode. If you want to go use SQL Authentication to connect to SQL Server, well then FORGET ABOUT IT!!!!
Like I say there must be MicroGremlins in the computer system playing nasty tricks on me.

Thanks once again
vbboyd

PSS
If you have any ideas about what might be happening here, I would be personally very grateful and appreciative.

Imar · January 30th, 2012, 05:33 AM

Quote:

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.

Hi there,

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.

Quote:

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.

Cheers,

Imar