ACC97: How to Use ASP Files to Query a Secure MS Access Database

• Microsoft Access 97
• Microsoft Internet Information Server version 3.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

WARNING: Although this article discusses Microsoft Access security features, any information you send over the Internet with the techniques described in this article is sent unencrypted. To send encrypted information over the Internet, you must use a protocol that sends client certificates, such as Secure Sockets Layer (SSL). Note that client certificates cannot be used on Personal Web Server for Windows 95. ANY USE BY YOU OF THE METHODS PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this sample "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

This article describes a technique you can use to create Active Server Pages (ASP) files that allow you to type a username and password in an HTML form in order to query a secure Microsoft Access database. This article describes how to use the technique when exporting a query and when exporting a form.

MORE INFORMATION

There are three main steps to using ASP files to query a secure Microsoft Access database:

• Use ODBC Administrator to create a System DSN that points to the workgroup information file (System.mdw) you use with your secured database.
• Create an HTML form that requests a username and password. The HTML form passes the values to parameters in your ASP file.
• Modify the script in the ASP file to use the HTML form to request the username and password parameters in order to authenticate user access to your database.

NOTE: The following example assumes that you are familiar with Microsoft Access security and will require you to create a secured copy of the Northwind sample database shipped with Microsoft Access.

This example contains the following sections:

• Creating a System DSN for a Secure Microsoft Access Database
• Creating the ASP Files and the HTML Logon Form
• Customizing the ASP Files and HTML Logon Form
• Testing the Query

Creating a System DSN for a Secure Microsoft Access Database

1. Double-click the ODBC icon in Control Panel on your Web Server.

2. In the ODBC Data Source Administrator dialog box, click the System DSN tab.

3. Click the Add button.

4. Select Microsoft Access Driver, and then click Finish.

   NOTE: If the Microsoft Access Driver does not appear, it is not installed on your Web server. For information about installing the driver on your Web server, search the Help Index for "Microsoft Access Desktop driver," or ask the Microsoft Access 97 Office Assistant.

5. In the ODBC Microsoft Access 97 Setup dialog box, type NorthwindASP in the Data Source Name box.

6. Click the Select button and browse to select Northwind2.mdb. Click OK.

7. In the System Database box, click Database, and then click the System Database button. Browse to select Northwind2.mdw, and then click OK.

8. Note that you have the option to click the Advanced button in the ODBC Microsoft Access 97 Setup dialog box, and set a default Login name and Password for the System DSN. Any of your ASP files that do not provide a username and password will use the default settings.

   NOTE: Username and password information needs to be supplied by either the System DSN or by the ASP file itself when accessing databases that have been secured by changing the permissions of objects. An interesting feature to note is that if a database has been secured with a database password rather than object permissions, the password information supplied by the System DSN or by the ASP file will still be used to gain authorization into the database even though the password has nothing to do with a user account in a system database.

9. Click OK to close the ODBC Microsoft Access 97 Setup dialog box.

10. Click OK to close the ODBC Data Source Administrator dialog box.

Creating the ASP Files and the HTML Logon Form

In this section, you create a query with username and password parameters, and then export the query to ASP format. When you create ASP files from a parameter query, Microsoft Access automatically creates an HTML form for entering the parameters. This is an easy way to create the HTML form you need to collect the username and password information. However, you do not have to use this technique to create the HTML form; you can use Notepad or another tool, such as Microsoft Front Page 97, to create your own HTML Logon form.

NOTE: Username and password information needs to be supplied by either the System DSN or by the IDC file itself when accessing databases that have been secured by changing the permissions of objects. An interesting feature to note is that if a database has been secured with a database password rather than object permissions, the password information supplied by the System DSN or by the IDC file will still be used to gain authorization into the database even though the password has nothing to do with a user account in a system database.

  1. Start Microsoft Access.

  2. Open Northwind2.mdb.

  3. Create the following new query called GetUserPass based on the
     Customers table:

        Query: GetUserPass
        ------------------
        Type: Select Query

        Field: CustomerID
        Table: Customers

  4. On the Query menu, click Parameters.

  5. Type the following in the Query Parameters dialog box, and then click
     OK.

        Parameter          Data Type
        ----------------------------
        [UserParam]        Text
        [PassParam]        Text

  6. Save the GetUserPass query and close it.

  7. On the File menu, click Save as HTML. The "Publish to the Web Wizard"
     dialog box opens. Click Next.

  8. Click the Queries tab, and select the GetUserPass query. Click the
     Forms tab, and select the Customers form. Click Next, and Next again.

  9. You will be asked to select a format type to create. Select Dynamic
     ASP (Microsoft Active Server Pages). Click Next.

 10. In the Data Source Name box, enter NorthwindASP, and click Next.

 11. The Publish Objects Locally button should be selected. Note the
     folder where the ASP files will be exported to. You do not need
     to make any more selections, so you can click Finish at this point.

 12. Click OK in each of the two Enter Parameter Value dialog boxes that
     appear.

 13. The ASP output creates four files: GetUserPass_1.asp,
     GetUserPass_1.HTML, Customers_1.asp, and Customers_1alx.asp.

Customizing the ASP Files and the HTML form

NOTE: This section contains information about editing ASP and HTML files, and assumes that you are familiar with editing HTML files, Active Server, and Visual Basic Scripting. Microsoft Technical Support engineers do not support customization of any HTML, HTX, IDC, or ASP files.

  1. Use Notepad or another text editor to open the GetUserPass_1.asp file.
     At the top of the file, you will see the following code:

      <HTML>
      <HEAD>
      <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
      1252">
      <TITLE>GetUserPass</TITLE>
      </HEAD>
      <BODY>
      <%
      Param = Request.QueryString("Param")
      Data = Request.QueryString("Data")
      %>
      <%
      If IsObject(Session("NorthwindASP_conn")) Then
         Set conn = Session("NorthwindASP_conn")
      Else
         Set conn = Server.CreateObject("ADODB.Connection")
         conn.open "NorthwindASP","",""
         Set Session("NorthwindASP_conn") = conn
      End If
      %>

      Replace the above code with the following (the rest of the code
      should remain the same):

      <%
      If IsObject(Session("NorthwindASP_conn")) Then
         Set conn = Session("NorthwindASP_conn")
      Else
         If Request.Form("[UserParam]") = "" then
         response.redirect "GetUserPass_1.HTML"
         Else
         Set conn = Server.CreateObject("ADODB.Connection")
         conn.open "NorthwindASP", Request.Form("[UserParam]"), _
            Request.Form("[PassParam]")
         Set Session("NorthwindASP_conn") = conn
         End If
      End If
      %>
      <HTML>
      <HEAD>
      <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
       1252">
      <TITLE>GetUserPass</TITLE>
      </HEAD>
      <BODY>

  2. Save the GetUserPass_1.asp file and close it.

  3. Use Notepad or another text editor to open the Customers_1.asp file.
     At the top of the file you will see the following code:

      <HTML>
      <HEAD>
      <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
      1252">
      <TITLE>Customers</TITLE>
      </HEAD>
      <BODY>
      <%
      If IsObject(Session("NorthwindASP_conn")) Then
         Set conn = Session("NorthwindASP_conn")
      Else
         Set conn = Server.CreateObject("ADODB.Connection")
         conn.open "NorthwindASP","",""
         Set Session("NorthwindASP_conn") = conn
      End If
      %>

      Replace the above code with the following (the rest of the code
      should remain the same):

      <%
      If IsObject(Session("NorthwindASP_conn")) Then
         Set conn = Session("NorthwindASP_conn")
      Else
         If Request.Form("[UserParam]") = "" then
           response.redirect "GetUserPass_2.HTML"
         Else
           Set conn = Server.CreateObject("ADODB.Connection")
           conn.open "NorthwindASP", Request.Form("[UserParam]"), _
             Request.Form("[PassParam]")
           Set Session("NorthwindASP_conn") = conn
         End If
      End If
      %>
      <HTML>
      <HEAD>
      <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
       1252">
      <TITLE>Customers</TITLE>
      </HEAD>
      <BODY>

      Save the Customers_1.asp file and close it. These changes will make
      the ASP file use the HTML form to request a username and password if
      the user had not yet entered a username and password for the session.

  4. Use Notepad or another text editor to open the GetUserPass_1.HTML
     file. By default, the HTML form uses the GET method to submit its
     data. Get variables appear in the address box of Web browsers.
     Therefore, you must change the GET method to the POST method if you do
     not want your password to be visible in the address box of your Web
     browser. Locate the following line in the GetUserPass_1.HTML file

      <FORM METHOD="GET" ACTION="GetUserPass_1.asp">

     and change it to:

      <FORM METHOD="POST" ACTION="GetUserPass_1.asp">

  5. Text boxes use an Input Type setting of Text by default. In order to
     prevent your password from being visible in the text box on your form,
     you must change the Input Type to Password. Locate the following line
     in the GetUserPass_1.HTML file

       [PassParam] <INPUT TYPE="Text" NAME="[PassParam]"><P>

     and change it to:

      [PassParam] <INPUT TYPE="Password" NAME="[PassParam]"><P>

  6. In Notepad, on the File menu, click Save. You will need to create
     another HTML form so do not close Notepad.

  7. In Notepad, on the File menu, click Save As.

  8. In the File Name box, type GetUserPass_2.HTML.

  9. In the Save As Type box, select All Files, and click Save.

 10. Locate the following line in the GetUserPass_2.HTML file

      <FORM METHOD="POST" ACTION="GetUserPass_1.asp">

     and change it to:

      <FORM METHOD="POST" ACTION="Customers_1.asp">

 11. On the File menu, click Save. On the File menu, click Exit.

 12. Copy GetUserPass_1.HTML, GetUserPass_2.HTML, GetUserPass_1.asp,
     Customers_1.asp, and Customers_1alx.asp to a folder on your Web Server
     computer where you have both Read and Execute permission.

     For more information about configuring Microsoft Internet Information
     Server (IIS) permissions, please refer to the IIS Help Index, and see
     the following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q160754
        TITLE     : Error "HTTP/1.0 403 Access Forbidden" Browsing IDC Page

Testing the Query

1. Start Microsoft Internet Explorer 3.0, or another Web browser program.

2. Type the Uniform Resource Locator (URL) in the address box of your Web browser to view GetUserPass_1.asp. For example, if you saved your ASP files in a folder called Test in the wwwroot folder of your Web Server, type:

        http://<servername>/test/GetUserPass_1.asp
   

   Note that the URL depends upon where your files are located on the Web Server and that Internet Explorer 3.0 with the HTML Layout Control is necessary to view forms exported to ASP.

3. The GetUserPass_1.HTML form opens in your Web browser with a [UserParam] box, a [PassParam] box, and a Run Query button. Type Admin in both boxes, and then click the Run Query button. The GetUserPass_1.asp file opens and displays a list of CustomerIDs.

4. Type the appropriate URL to view Customers.asp and the Customers form will open. You will not be prompted for a username and password because the Session began when you first logged on. The Session ends when the Web browser is closed. A Session will also end if a user does not request or refresh a page within the timeout period. The Session Timeout property default is 20 minutes and can be changed in your ASP scripts. You can also end the Session by using the Abandon method of the Session object in your scripts. For more information about using the Abandon method and setting the Timeout property for the Session object, please refer to your online ASP documentation.

   NOTE: If you type an incorrect username or password, you receive the following error:

         Microsoft OLE DB Provider for ODBC Drivers error '80004005'
         Not a valid account name or password.
   

REFERENCES

For more information about how to create and modify ASP files, please refer to your Microsoft ASP online documentation.

For more information about IIS authentication, security, and Secure Sockets Layer (SSL), please refer to your IIS online documentation, or see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q142868
   TITLE     : IIS: Authentication & Security Features