Microsoft Office 2000/Visual Basic Programmer's Guide

Preventing Unauthorized Access

The method you use to prevent unauthorized access may differ depending on whether you are using an Access database or a SQL Server database as the back end for your data access page. In either case, the back-end database itself must be secured. For an .mdb file, you should secure the database by using User-Level security as described earlier in this chapter. For a SQL Server database, you should secure the database by using native SQL Server security accounts or by integrating Windows NT security accounts, which is referred to as integrated security. When you are using integrated security, user and group accounts defined for a Windows NT network are used to authenticate users for SQL Server database security. This means that users don't have to log on for security purposes when opening a database; they are identified by the Windows NT user account they used to log on to the network (or by the group account of which that user account is a member). The user or group account's access level still must be defined in the database itself, but you are using the Windows NT user or group accounts instead of using native SQL Server accounts. For more information about how to use security in a SQL Server database, see the documentation provided with SQL Server.

You can also author a data access page against an Access database that is secured with a database password. However, a database password provides control only over who can open the database, and requires that all users know a single password to open the database. If you use database password security to control database access for a data access page, you have two options on how to use it. By default, a database password isn’t saved with a data access page, so users are prompted to enter the database password when opening the page. (Similarly, if a database password is set for the database after you author the page, users of your page will be prompted to enter the password before they can use the page.)

If you don’t want to prompt users for the database password, you can embed the password in the page; however, when you do so, the password is saved in an unencrypted format in the HTML of the page itself, which makes it fairly easy to discover.

To embed the database password in the page

Open the HTML page in Notepad or a text editor.
Search for Jet OLEDB:Database Password=
Delete &quot;&quot; following the = sign.
Type the password following the = sign.
Save your changes.

Even though you can edit the value of the Jet OLEDB:Database Password property on the All tab of the Data Link Properties dialog box (which is displayed by right-clicking the database name in the field list and then clicking Connection) while authoring a data access page in Access, you aren’t allowed to save changes because the database is already in use.

Preventing unauthorized database access from a data access page consists of performing the following steps:

Preventing the user ID and password (that you use to access the database while authoring a data access page) from being saved with the page.
Determining how users of the page will be authenticated; that is, determining how their logon identities will be entered and validated.
Determining how — or if — you can control the level of access for individual users.
Preventing unauthorized access from malicious scripts hidden in the page.

Preventing the User ID and Password from Being Saved with the Page

For an Access database back end secured with User-Level security, the only way to prevent unauthorized access is to prevent the security account user ID and password from being saved with the information used to connect to the database. This will cause a logon dialog box to be displayed that requires a user to enter a correct logon account name and password before the data access page can connect to the database. This is also appropriate for SQL Server back-end databases regardless of whether you are using native SQL Server security accounts or Windows NT integrated security for authentication. To prevent a logon password from being saved with a data access page's connection information, you must use the Data Link Properties dialog box as described in the following procedure.

To prevent the password used to connect to a database from being saved with a data access page

Start Access and open the data access page you want to work with in Design view.
On the View menu, click Field List.
On the Database tab, right-click the database name, and then click Connection.
Under Enter information to log on to the server, make sure the Allow saving of password check box is cleared.
Click OK, and save your changes to the data access page.

Now whenever the data access page is opened, the user must provide the appropriate user name and password to log on to the database, or if you are using integrated security with SQL Server, the user's identity must be passed in from the Windows NT Server network's authentication system.

Determining How Users Are Authenticated

Two major factors determine how users of a data access page will be authenticated to access a secured back-end database:

How user accounts are defined and authenticated in the back-end database. This differs depending on whether you are using an Access database or a SQL Server database.
How authentication is configured on the Internet Information Server (IIS) that is used to publish the page.

For Access databases, there is the additional factor of allowing access to the database file itself (.mdb) through the file system. Because the Access database itself is a file, users of a data access page connected to an Access database must have sufficient file-system permissions (defined through the network operating system) to open and possibly write to the database file. These factors must be coordinated in order for authentication to work correctly. The following sections describe the available options for each database type.

Authenticating Users of Data Access Pages Connected to an Access Database Back End

Users of secured Access databases must be authenticated against account information stored in a workgroup information file. For authentication to work correctly for a secured Access database back end, you must also make sure that the connection information for a data access page specifies the correct workgroup information file. To do this, you must also make sure that the workgroup information file is in a location, such as a public network share, that can be accessed by all users who need to open the data access page. The following procedure describes how to specify this information for a data access page.

To specify the workgroup information file to use when opening a data access page that is connected to a secured Access database (.mdb)

Start Access and open the data access page you want to work with in Design view.
On the View menu, click Field List.
On the Database tab, right-click the database name, and then click Connection.
On the All tab, double-click Jet OLEDB:System database.
Specify the path to the correct workgroup information file in the Property Value box. In most cases, this should be a UNC path (\\ServerName\ShareName) to the file on a network share.
Click OK twice to close the Data Link Properties dialog box, and then save your changes to the data access page.

For authentication information to be passed correctly from the IIS server that is used to publish the page to the Jet database engine, you must configure the authentication method used on the IIS server. If you want to use individual user accounts to determine the level of access a user has after opening an Access database through a data access page, you must use IIS Basic Authentication, which prompts the user to enter a user account name and password when opening the page. For information about configuring Basic Authentication, see "Using Basic Authentication" later in this chapter.

If it is acceptable to use a single level of access for all users who open the database through a data access page, you can define an Access user account that matches the name and password of the IIS Anonymous User account and use Anonymous Access authentication on the IIS server. In this case, all users are automatically logged on under the single Anonymous Access user account. For information about configuring Anonymous Access authentication, see "Using Anonymous Access Authentication" later in this chapter.

It is also possible to use Windows NT Challenge/Response authentication, but because the Microsoft Jet database engine can't use Windows NT logon accounts, the database must be secured by granting the desired permissions to the Admin user as described earlier in this chapter in "Securing Your Database Without Asking Users to Log On." For more information about configuring Windows NT Challenge/Response authentication, see "Using Windows NT Challenge/Response Authentication" later in this chapter.

Authenticating Users of Data Access Pages Connected to a SQL Server Back-end Database

How users of secured SQL Server databases are authenticated depends on how security accounts are defined for the back-end database. If you are using native SQL Server security accounts, users are authenticated against information stored on the database server itself. As long as you haven't stored the user ID and password you used while authoring the page, and you are using Basic Authentication on the IIS server, users will be prompted to enter a user ID and password when they open the page, and that logon information will be passed to the server for authentication.

Just as with an Access database, if it's acceptable to specify a single level of access for all users who open the database through a data access page, you can define a native SQL Server user account and password that matches the IIS Anonymous User account and use Anonymous Access authentication on the IIS server. For information about configuring Anonymous Access authentication, see "Using Anonymous Access Authentication" later in this chapter.

If you are using Windows NT integrated security to connect to a SQL Server database from a data access page, only authorized Windows NT accounts will be allowed to open or work with the database — their Windows NT logon identity will be passed to the database. To use Windows NT authentication to connect to a SQL Server database from a data access page, you must use the Data Link Properties dialog box as described in the following procedure.

To use Windows NT authentication to connect to a SQL Server database from a data access page

Start Access and open the data access page you want to work with in Design view.
On the View menu, click Field List.
On the Database tab, right-click the database name, and then click Connection.
Under Enter information to log on to the server, click Use Windows NT Integrated security.
Click OK, and save your changes to the data access page.

Whenever the data access page is opened, only Windows NT logon accounts that have been authorized to open and work with the SQL Server database will be allowed to log on to the database. For more information about configuring Windows NT Challenge/Response authentication on the IIS server used to publish the page, see "Using Windows NT Challenge/Response Authentication" later in this chapter.

Important For the highest level of security when you are using an IIS server to share a data access page across a network, your data access page should use three-tier data access against secured Access and SQL Server databases. Using three-tier data access against a secured database requires you to perform additional steps to configure authentication. For information about how to do this, see "Preventing Unauthorized Access from Malicious Scripts" later in this chapter.