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

• 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.

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

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

  2. 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.

  3. In the Workgroup Administrator dialog box, click Create.

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

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

  5. 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.

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

  7. 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.

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

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

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

 11. Quit Microsoft Access.

 12. 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.

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

 14. Click the Join button.

 15. 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.

 16. 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

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

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 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.

  1. Start Microsoft Access.

  2. Open Northwind2.mdb.

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

        Query: SecureIDC
        ----------------
        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 SecureIDC query and close it.

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

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

  9. 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.

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

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

 12. 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 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 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;
   

2. Save the SecureIDC.IDC file and close it.

3. 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">
   

4. 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>
   

5. Save the SecureIDC.HTML file and close it.

6. Copy SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC to a folder on your Web Server computer where you have both Read and Execute permission.

   ARTICLE-ID: Q160754
   TITLE     : ACC97: 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 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.

3. 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:

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