FIX: You cannot step into a stored procedure from ASP.NET code on Windows Server 2003

On Windows Server 2003, if Microsoft Internet Information Services (IIS) 6.0 runs in Worker Process Isolation Mode (WPIM) mode, you cannot step from managed code into a Microsoft SQL Server stored procedure while you debug in the Visual Studio .NET integrated development environment (IDE).

You do not receive any error messages. However, the stored procedure window does not appear while you step through the ASP.NET code.

When you invoke SQL debugging, the debugger creates a shared memory segment of debugging information. Multiple processes that are involved in debugging stored procedures can use this debugging information.

However, only local administrators and the SYSTEM account have permission to access this shared memory segment. The client process that is being debugged (IIS 6.0 [W3WP.exe]), which runs in the security context of the NETWORK_SERVICE account, cannot access this shared memory segment and therefore cannot enable SQL debugging.

Use one of the following methods to work around this problem:

• Run IIS 6.0 in IIS 5.0 isolation mode. When IIS 6.0 runs in IIS 5.0 isolation mode, IIS runs in the security context of the SYSTEM account. This gives IIS access to the shared memory segment so that IIS can enable SQL debugging. By default, IIS 6.0 is installed to run in Worker Process Isolation Mode (WPIM) mode. To change this setting, follow these steps:
  1. Open the IIS manager.
  2. Right-click Web Sites, and then click Properties.
  3. Click the Service tab, and then click to select the Run WWW service in IIS 5.0 isolation mode check box.
  Note that when you change this setting, you must restart IIS.
• Change permissions. In a secure testing environment, you can add the NETWORK_SERVICE account to the local administrators group to give this account access to the shared memory segment. However, Microsoft strongly discourages this approach for security reasons.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section. This bug was corrected in Microsoft Visual Basic .NET 2003, Microsoft Visual C# .NET 2003, and ADO.NET (included with the .NET Framework 1.1).

Steps to reproduce the behavior

1. On a Windows Server 2003-based computer, make sure that IIS is configured to run in non-legacy mode.
2. In Microsoft Visual Studio .NET, on the File menu, point to New, and then click Project.
3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
4. Add a Button control from the toolbox to WebForm1.
5. Add the following code to the top of the code window:

   Imports System.Data.SqlClient
   					

6. Add the following code to the Click event of the button:
   
   Note You must change User ID=<username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

           Dim cn As SqlConnection
           Dim strCn As String
           Dim cmd As SqlCommand
           Dim prm As SqlParameter
           strCn = "Data Source=(local);Initial Catalog=Northwind;" & _
               "User ID=<username>;Password=<strong password>"
           cn = New SqlConnection(strCn)
           cmd = New SqlCommand("CustOrderHist", cn)
           cmd.CommandType = CommandType.StoredProcedure
           prm = New SqlParameter("@CustomerID", SqlDbType.Char, 5)
           prm.Direction = ParameterDirection.Input
           cmd.Parameters.Add(prm)
           cmd.Parameters("@CustomerID").Value = "ALFKI"
           cn.Open()
           Dim dr As SqlDataReader = cmd.ExecuteReader
           While dr.Read
               Response.Write("Product ordered: " & dr.GetSqlString(0).ToString & "<BR>")
           End While
           dr.Close()
           cn.Close()
   					

7. Modify the SQL Server connection string as necessary for your environment.
8. In the Project Explorer window, right-click on the project (not the solution), and then click Properties. Click Configuration Properties, and then click to select the SQL Server Debugging check box on the Debugging page to enable stored procedure debugging.
9. Set a breakpoint on the following line of code:

          Dim dr As SqlDataReader = cmd.ExecuteReader
   					

10. In Server Explorer, locate the CustOrderHist stored procedure. Right-click the stored procedure, and then click Edit Stored Procedure.
11. Set a breakpoint in the stored procedure on the SELECT statement, which appears as one line of executable code.
12. Press the F5 key to run the Visual Basic project, and then click the command button. The code runs until it hits the breakpoint that you set before the stored procedure is called.
13. Press the F11 key. You expect to step into the code of the stored procedure. Instead, the debugger skips the stored procedure and continues with the next line of Visual Basic code.