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

ID: Q161172

The information in this article applies to:

Microsoft Access 97

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 Internet Database Connector (IDC) files that allow you to type a username and password in an HTML form in order to query a secure Microsoft Access database.

MORE INFORMATION

There are three main steps to use IDC 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 IDC file.

Modify the IDC file to use the username and password parameters 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 IDC 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 Microsoft Windows NT permissions, not Microsoft Access security accounts. It is possible to use Basic Authentication and NT Challenge/Response with Microsoft SQL Server databases because SQL Server can be integrated with NT Security. Microsoft Access security does not provide that capability.

This example contains the following sections:

Creating a Secure Copy of Northwind.mdb

Creating a System DSN for a Secure Microsoft Access Database

Creating the HTX/IDC Files and the HTML Logon Form

Customizing the IDC Files

Testing the Query

Creating a Secure Copy of Northwind.mdb

Copy the sample database Northwind.mdb to C:\My Documents\Northwind2.mdb.

Start the Workgroup Administrator by executing the Wrkgadm.exe file. The file is installed to your Windows\System folder by default. You can find a shortcut to this file in the C:\Program Files\Microsoft Office folder.

In the Workgroup Administrator dialog box, click Create.

In the Workgroup Owner Information dialog box type the following information, and then click OK:


        Name:  <Your name>
        Organization: Northwind Traders
        Workgroup ID: 1234

In the Workgroup Information File dialog box, make a note of the path and file name that appears in the Database box so you can rejoin that workgroup file when you are finished. Then type C:\My Documents\Northwind2.mdw, and click OK.

In the Confirm Workgroup Information dialog box, verify that the information is correct, and then click OK.

Click OK when you see the message that you have successfully created the workgroup file, and then click Exit in the Workgroup Administrator dialog box.

Start Microsoft Access and open C:\My Documents\Northwind2.mdb.

On the Tools menu, point to Security, and then click User And Group Accounts.

Click the Change Logon Password tab, and type Admin in the New Password and Verify boxes. Click OK.

Quit Microsoft Access.

Copy the Northwind2.mdb and Northwind2.mdw files to a folder on your Web Server computer, or to a network location that you can access from your Web Server computer. Be sure to retain a copy of Northwind2.mdb on your local drive for use later in this example.

At this point, you can rejoin your original workgroup information file.

Start the Workgroup Administrator program following the procedure in step 2 of this section.

Click the Join button.

In the Workgroup Information File dialog box, type the path and file name of the system database that you noted in step 5, and then click OK.

Click OK when you receive the message that you have successfully joined the workgroup, and then click Exit in the Workgroup Administrator dialog box.

Creating a System DSN for a Secure Microsoft Access Database

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

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

Click the Add button.

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.

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

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

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

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 IDC files that do not provide a username and password will use the default settings.

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

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

Creating the HTX/IDC Files and the HTML Logon Form

In this section, you create a query with username and password parameters, and then export the query to HTX/IDC format. When you create IDC 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.

Start Microsoft Access.

Open Northwind2.mdb.

Create a new query called SecureIDC based on the Customers table:


        Query: SecureIDC
        ----------------
        Type: Select Query

        Field: CustomerID
        Table: Customers

On the Query menu, click Parameters.

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


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

Save the SecureIDC query and close it.

Select the SecureIDC query in the Database window, and then click Save As/Export on the File menu.

In the Save As dialog box, click "To an External File or Database," and then click OK.

In the "Save Query 'SecureIDC' In" dialog box, select Microsoft IIS 1-2 (*.htx;*.idc) in the Save As Type box, and type SecureIDC.htx in the File Name box. Click Export.

In the HTX/IDC Output Options dialog box, type NorthwindIDC in the Data Source Name box, and then click OK.

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

The HTX/IDC output creates three files: SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC

Customizing the IDC Files

NOTE: This section contains information about editing IDC and HTML files, and assumes that you are familiar with editing HTML files. Microsoft product support professionals do not support customization of any HTML, HTX, IDC, or ASP files.

Use Notepad or another text editor to open the SecureIDC.IDC file. The Password and Username fields show nothing entered next to them by default. You must add the parameters that will hold the values from the HTML Logon form. Change the SecureIDC.IDC file so it looks as follows:
```
      Datasource: NorthwindIDC
      Template:SecureIDC.htx
      Username:%[UserParam]%
      Password:%[PassParam]%
      SQLStatement:SELECT Customers.CustomerID
      +FROM Customers; 
```

Save the SecureIDC.IDC file and close it.

Use Notepad or another text editor to open the SecureIDC.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 SecureIDC.HTML file
```
      <FORM METHOD="GET" ACTION="SecureIDC.IDC"> 
```
and change it to:
```
      <FORM METHOD="POST" ACTION="SecureIDC.IDC"> 
```

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 SecureIDC.HTML file

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

and change it to:

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

Save the SecureIDC.HTML file and close it.

Copy SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC 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:

Q160754 ACC97: Error "HTTP/1.0 403 Access Forbidden" Browsing IDC Page

Testing the Query

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

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

http://<servername>/test/SecureIDC.HTML

Note that the URL depends upon where your files are located on the Web Server.

The SecureIDC.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 SecureIDC.IDC file opens and displays a list of CustomerIDs.

NOTE: If you type an incorrect username or password, you receive the following error:
```
      Error Performing Query
      Not a valid account name or password. 
```

REFERENCES

For more information about how to create and modify the optional fields in IDC files, please refer to your Microsoft IIS 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:

Q142868 IIS: Authentication & Security Features

For more information about Microsoft Access security, search the Help Index for "security, overview," or ask the Microsoft Access 97 Office Assistant.

Additional query words: internet intranet


Keywords          : kbusage 
Version           : 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 13, 1999