Database integration with Web Pages - Overview

Introduction

Increasingly, web pages are being used to access information held in databases. The main reasons for doing this are a) to provide up-to-date information without editing and re-publishing individual web pages whenever some information changes and b) to enable selection of information through searches with parameters selected on a web page. Although information can be extracted from databases to provide 'static' pages created at intervals when the data changes, increasingly web pages are served that provide data that is obtained from a database at the time the web page is served.

IT Services at The University of Reading provide the facility to connect to both MS Access and MS SQL Server databases. We support, through training and advice, the methods used to make this possible. We support MS Active Server Pages (ASP) and their connectivity to MS Access (for low volume and low security applications and when using Frontpage to create the site) and MS SQL Server. MS Access can be used to connect to the data on the MS SQL server - more details below.

We now also offer MySQL and PHP, although we are unable to provide the support and training available for ASP, MS Access and MS SQL Server.

Register a Web Information Provider Username

If you do not already have a Web Information Provider (WIP) account registerd for use on the MS IIS web server (a WIP account is recognised from the fact that it starts with a 'w') you should either

Register for a new Web Information Provider username for use in the MS IIS web server
- do this using the online form Request for a Web Site name or Web Information Provider

or

Register your existing Web Information Provider username for use on the MS IIS web server.

- do this using the online formApplication for a MS SQL Server Database.

MS Access or MS SQL Server?

Please use the following diagram to help you choose whether you should use MS Access or MS SQL Server:

A: Use MS SQL Server:

  1. Apply for a MS SQL Server database (Application for aMS SQL Server Database). This form should be filled in separately for every database that you wish to have. You should give the username of your Web Information Provider account in response to the questions "If Web pages already exist for your group, what is the Information Provider username?"
  2. Create the database usingSQL Server database administration using Web Data Administrator and/or SQL Server database administration using Access Project. We would normally expect database owners to use Web Data Administrator for setting up users and controlling access to their databases. However users will probably find it easier to manipulate tables etc. using MS Access Project.
  3. Create your web pages which will connect to the database

B:Use MS Access and a database within the web directory structure:

First the database needs to be created (see Application for a MS SQL Server Database,. The web pages then need to be created to interact with the database

Administering your database

SQL Server database

Staff and Postgraduates can administer their database by using the Web to connect to the MS SQL Server Web Database Administrator..

Undergraduate students should use Access Project to administer their MS SQL database. If students need to change the access settings for their database this must be done via a request to ITS Help.

At the connection page for Web Database Administrator you need to select the Login to SQL Server Web Data Administrator link and then click on the link so you can login. There is a comprehensive Help system for the Web Data Administrator which can be accessed by selecting the ? in the top right-hand corner of the Web Data Administrator screen.

MS Access

Both MS Access and MS Access Project databases are administered from the Access interface. Note that access permissions cannot be set using this interface.

Converting a MS Access database to use on SQL Server

the method of converting an MS Access database to a SQL server database is fully described in Moving a database from MS Access to MS SQL server.

Writing Web Pages to extract data from your database

The documentDisplaying Database Information on the WWW using ASP describes how to use ASP to display information within a database on a web page.

Controlling access to your database and web pages.

Database

You can control access to your database from Web Data Administrator.

However you can only limit access to those on the drop-down list which consists of all users who have been given login permissions to the SQL server. If you require anyone who is not listed to have access then you will have to complete an Application to register additional users for MS SQL Server Databases form. Once you have created a user for your database you will be given a list of permissions that you can allocate to your newly created user.

Note: you must add the local IUSR_VI2KWS1 user if you want your database to be read with anonymous access: other users can be added as needed. Groups can also be added, for example RDGStaff and RDGPGrads.

Web Pages

If you are publishing your web pages using MS FrontPage and FrontPage extensions (i.e. connecting to or publishing the web using the http protocol) you can set who can see the pages from Tools, Server, Permissions. You can set Unique permissions for the web and determine whether anonymous access is on or off. If you set it off, you can then set which users or groups can access the web. This is true no matter where your database is held.

Creating a new SQL Server database

It is not possible for users to create their own databases. Users must complete an Application for a MS SQL Server Database form for each database required.

Extending your MS SQL Server database

If you need a MS SQL Server database larger than the default size (10MB) or current size, you can use the Application for Extending a MS SQL Server Database form.

Registering additional users for MS SQL Server databases

If you would like to limit who can access your SQL Server database, you can do this via Web Data Administrator. Access permissions can also be set on the web pages using FrontPage. On web pages set up to use MS Access, permissions can be set on the web pages to limit the access to the database. However Access databases on the web server, particularly if they are within the web directory structure, are less secure than SQL server databases held on a separate machine.

MySQL Databases

We provide MySQL, interfaced with PHP, as an alternative to MS Access and SQL Server. You can complete an Application for a MySQL Database, but you must first use the Request for Web Site name or Web Information Provider form to apply for a Web Information Provider username. PHP is available with the web space allocated to your Information Provider account; you will not need to make a special application to use PHP.

Administering your MySQL database

The document MySQL Database administration using phpMyAdmin explains how to administer your MySQL database.

Additional users of your MySQL database

To have additional users of your MySQL database you should register subsidiary users of your Information Provider account using the Request for Web Site name or Web Information Provider form.

TM Trademark owned by Microsoft Corporation.
© Screen shot(s) reprinted by permission from Microsoft Corporation.