Tag: ASP.Net

Configuring Trusted SQL Connections in ASP.Net and Windows 2003

Posted on | by brian | Leave a Comment on Configuring Trusted SQL Connections in ASP.Net and Windows 2003

While configuring a new server running Microsoft Windows Server 2003, I decided to start using trusted SQL connections from ASP.Net Web sites (instead of specific SQL user accounts). One of the differences between Windows 2000 and Windows 2003 is the ASP.Net process account; the former uses ASPNET, the latter NETWORK SERVICE.

One thing you’ll notice is that when creating a new login for SQL Server is that you can’t select the NETWORK SERVICE account. (Oddly, you can select the ASPNET account, but I don’t know what it is used for in Windows 2003/IIS 6.0, since it’s been replaced with NETWORK SERVICE.) After plenty of searching I found a newsgroup posting from Microsoft that solved the problem.

In brief: Either specify the username as NT AUTHORITY\NETWORK SERVICE in SQL Enterprise Manager’s New Login window, or run the following query:

exec sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]

Yet another example of Microsoft hiding the obvious.

Permissions Error When You Try to Debug an ASP.NET Web Application

Posted on | by brian | Leave a Comment on Permissions Error When You Try to Debug an ASP.NET Web Application

While working in Microsoft Visual Studio .Net 2003 and trying to start an ASP.Net application in debugging mode (on the local Web service), I received the following error:

Error while trying to run project: Unable to start debugging on the Web server. You do not have permissions to debug the server.
Verify that you are a member of the ‘Debugger Users’ group on the server.

There’s a great many reasons why debugging in ASP.Net can fail, but the one that caused my error can easily be overlooked. The resolution is documented in Microsoft Knowledge Base Article 319842. From the article:

“If your security settings for the Internet zone are not set to Automatic logon with current username and password, authentication may be unsuccessful and you may receive this error message.”

Many times, it’s useful to log in to internal (intranet) zone Web sites using different user accounts (for testing and troubleshooting). The above comment applies to the intranet zone in Internet Explorer as well if you are debugging on the local Web server. I had this setting set to Prompt for user name and password. VS.Net will not prompt for the user name and password; it will prompt you with an error message. Change the setting to  Automatic logon with current username and password and you’ll be good to go.

Running Ad-Hoc SQL Queries in ASP

Posted on | by brian | 1 Comment on Running Ad-Hoc SQL Queries in ASP

Have you ever wished you can run ad-hoc queries on your database from a Web page? This article shows you how a simple Web form and some ASP code can execute SQL statements against a database and display the results in your Web browser.

Step 1: Keep it secure!

The first thing to do is to secure the page you’re about to create! (In this article, we’ll call the page runsql.asp.) There are a number of ways to secure your Web page using IIS (which we won’t get in to here). If you don’t secure the page, you’re taking the chance that anyone can execute SQL statements on your server – something you likely don’t want to do – so be sure to start off with security in mind! You should also consider using the HTTPS protocol, as your SQL statements and results can be intercepted if they are sent in clear text.

Another suggestion is to use a read-only account in the connection string when connecting to a SQL Server database. Although this prohibits your from executing INSERT, UPDATE, and other SQL statements, it will eliminate a significant security risk.

Step 2: ASP Necessities

To keep things clean and proper, we’re going to use Option Explicit at the top of our ASP page. To trap errors later on, we’ll use On Error Resume Next. We’ll also declare the variables we’ll be using later on in the page, and set three ADO constants (which is much cleaner than importing the entire ADOVBS.INC file!).

Option Explicit
On Error Resume Next
Dim objConn, objRec, strSQL, i, iFields
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001

Step 3: Create the HTML form

The HTML form has a TEXTAREA field where we’ll type our SQL statements, and the typical submit and reset buttons. The form submits to itself (i.e. runsql.asp). Note that the TEXTAREA’s initial value is set to Request.Form("sql") – so after we submit the form, we’ll easily be able to see the query we just submitted and make changes to it.

<form action="runsql.asp" method="post">
<textarea name="sql" cols="60" wrap rows="10">
<%= Request.Form("sql") %>
</textarea><br>
<input type="submit">&nbsp;&nbsp;<input type="reset">
</form>

Step 4: Executing the SQL Query

We check to see if the form was submitted by examining Request.ServerVariables("REQUEST_METHOD"), which equals POST when our form is submitted. (Remember, we used the POST method in our form.)

If the form is submitted, we’ll be nice and display the SQL query received in Request.Form("sql"), then create our ADO Connection and Recordset objects. Be sure to open the Connection object with the connection string of your datasource.

Finally, we can open our Recordset. Note the use of adOpenForwardOnly and adLockReadOnly – since we don’t need any special recordset cursors or locking, specifying these options gives the best performance.

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
 
    Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"
 
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")
 
    strSQL = Request.Form("sql")
    objConn.Open "dsn=mydsn"
    objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

Step 5: Checking for Errors

Remember when we specified On Error Resume Next at the top of our code? We can trap the error here. Chances are if anything failed up to this point, the objRec.Open call will fail, too, generating an error. If there’s an error, we’ll display it to the user.

If Err.Number <> 0 Then
    Response.Write "An error has occurred!<br>"
    Response.Write "Error number:      " & Err.number & "<br>"
    Response.Write "Error description: " & Err.description & "<br>"

Step 6: Displaying the Results

Continuing right after the error checking, we have to handle two situations: if the recordset is empty, or if it is not empty. If it’s empty, we’ll simply tell the user.

ElseIf objRec.EOF Then
    Response.Write "Empty recordset returned."

If objRec is not empty, we need to display it in an HTML table. First, we want to display the field names as a header row. Recordset field names are stored in the Fields collection, which is the default member of the Recordset object. We’ll read the number of fields (objRec.Fields.Count) into a variable, then use a For loop to output the Name parameter of each field (which contains the column name for the field).

Note that we could have specified objRec(i).Name instead of objRec.Fields(i).Name. Since the Fields collection is the default member of the Recordset object, it isn’t necessary to specify it, assuming it’s what you want.

Else
    iFields = objRec.Fields.Count
    Response.Write "<table border=1><tr>"
    For i = 0 To iFields - 1
        Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"
    Next
    Response.Write "</tr>"

Next, to display the rows in the recordset, we’ll loop through the Recordset using a While loop, moving from record to record using Recordset.MoveNext. Using the same technique as above, we’ll loop through the Fields collection and return the Value parameter for each field, which contains the data in the field.

Again, we could have substituted objRec(i).Value for of objRec.Fields(i).Value, since Fields is the default member of the Recordset object.

    While Not objRec.EOF
        Response.Write "<tr>"
        For i = 0 To iFields - 1
            Response.Write "<td>" & objRec.Fields(i).Value & "</td>"
        Next
        Response.Write "</tr>"
        objRec.MoveNext
    Wend
    Response.Write "</table>"
End If

Step 7: Cleaning Up

As always, you should close your Connection and Recordset objects when you’re done with them, and set their values to Nothing. Don’t rely on the script engine to do this for you. One memory leak can do considerable damage to your server’s performance!

objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn = Nothing

Summary: RUNSQL.ASP

Being able to execute SQL statements from a Web page can come in very handy, especially when you’re not able to connect to the SQL server directly (if it’s inaccessible through a firewall or proxy server). If you secure this properly, you’ll have a powerful and easy to use tool in your Web development toolbox.

COMPLETE SOURCE CODE FOLLOWS:

<%

Option Explicit

On Error Resume Next

Dim objConn, objRec, strSQL, i, iFields

Const adOpenForwardOnly = 0

Const adLockReadOnly = 1

Const adCmdText = &H0001

%>

<html>

<head>

<title>RUNSQL</title>

</head>

<body>

<form action="runsql.asp" method="post">

<textarea name="sql" cols=60 wrap rows=10>

<%= Request.Form("sql") %>

</textarea>

<input type="submit">&nbsp;&nbsp;<input type="reset">

</form>

<%

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

  Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"

  Set objConn = Server.CreateObject("ADODB.Connection")

  objConn.Open "dsn=mydsn"

  strSQL = Request.Form("sql")

  Set objRec = Server.CreateObject("ADODB.Recordset")

  objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

  If Err.Number <> 0 Then

   Response.Write "An error has occurred!<br>"

   Response.Write "Error number:      " & Err.number & "<br>"

   Response.Write "Error description: " & Err.description & "<br>"

  ElseIf objRec.EOF Then

   Response.Write "Empty recordset returned."

  Else

   iFields = objRec.Fields.Count

   Response.Write "<table border=1><tr>"

   For i = 0 To iFields – 1

    Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"

   Next

   Response.Write "</tr>"

   While Not objRec.EOF

    Response.Write "<tr>"

    For i = 0 To iFields – 1

     Response.Write "<td>" & objRec.Fields(i).Value & "</td>"

    Next

    Response.Write "</tr>"

    objRec.MoveNext

   Wend

  End If

  objRec.Close

  Set objRec = Nothing

  objConn.Close

  Set objConn = Nothing

End If

%>

</body>

</html>

ASP.Net Web Forms, BASE HREF, and FORM ACTIONs

Posted on | by brian | Leave a Comment on ASP.Net Web Forms, BASE HREF, and FORM ACTIONs

In some of the sites I develop, I use the BASE HREF tag. Unfortunately, doing this poses problems when you’re using Web forms in ASP.Net if your Web form is in a directory below the root directory.

If I create a page using Web forms in a subdirectory – as in http://somehostname/subfolder/webform.aspx – the FORM ACTION property of the form will point to http://somehostname/webform.aspx. Note how subfolder disappeared. This happens because of the BASE HREF tag. ASP.Net Web forms create the FORM ACTION without any reference to the physical path of the file – something that is not a problem unless you’re using BASE HREF.

Unfortunately, in a server-side FORM tag, you can not specify the ACTION property. But you can use JavaScript to change it. So, on each Web page where I need to adjust the FORM ACTION of a server-side ASP.Net Web form, I do something like this:

I use JavaScript to compile the complete path to the form, which includes the base URL (which I store in Application["basehref"]) followed by the subfolder (which I must type explicitly) followed by the current form action (which is taken dynamically within JavaScript). In four simple lines, I work around an ASP.Net shortcoming and continue to allow myself the benefit of using the BASE HREF tag!