ACC97: How to Use ASP Files to Query a Security-Enhanced Microsoft Access Database
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article was previously published under Q163159
Advanced: Requires expert coding, interoperability, and multiuser
skills.
On This Page
SUMMARY
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.
There are three main steps to using ASP files to query a
secured 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.
You must use an HTML form to enter the username and password,
and then pass that information to the ASP files. You cannot configure Microsoft
Internet Information Server (IIS) Basic Authentication or NT Challenge/Response
to achieve this functionality because those IIS options authenticate users
against Windows NT or Windows 2000 permissions, not Microsoft Access security
accounts. It is possible to use Basic Authentication and Challenge/Response
with Microsoft SQL Server databases because SQL Server can be integrated with
Windows NT or Windows 2000 Security. Microsoft Access security does not provide
that capability.
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 a System DSN for a Secure Microsoft Access Database
1.
On your Web Server, open Control Panel.
2.
Open the ODBC Administrator. If you are using Windows
2000, in Control Panel, open Administrative Tools, and then Data Sources
(ODBC).
3.
In the ODBC Data Source Administrator dialog box, click
the System DSN tab.
4.
Click the Add button.
5.
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.
6.
In the ODBC Microsoft Access 97 Setup dialog box, type
NorthwindASP in the Data Source Name box.
7.
Click the Select button, and browse to select
Northwind2.mdb. Click OK.
8.
In the System Database box, click Database, and then click
the System Database button. Browse to select Northwind2.mdw, and then click OK.
9.
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.
10.
Click OK to close the ODBC Microsoft Access 97 Setup dialog
box.
11.
Click OK to close the ODBC Data Source Administrator dialog
box.
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:
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 twice.
9.
You are 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.
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 product support professionals 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
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:
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:
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.
Need More Help? Contact a Support professional by Email, Online or Phone.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Help and Support
Back to the top
