Considerations for developing an ASP program to connect to remote SQL Server

Microsoft SQL Server7.0 is a large, distributed database system and can be considered a large, distributed data Warehouse. Because of its
Windows NT and BackOffice, as well as Visual Studio, are closely matched and have been used by a growing number of network applications. and ASP program also with its and
The close connection of NT, can use the characteristic such as ActiveX object, CGI interface object to launch the powerful challenge to CGI program. Let's talk about developing connections to remote SQL
The Server's ASP program should pay attention to several problems.
SQL Server is a security-focused database, especially after release 7.0, which gives Windows NT direct permission to
, database roles can also include NT users or groups and users and roles for older versions of SQL Server. In addition, combined with Microsoft Proxy Server, you must
You need to use a proxy server to access SQL Server on the Internet, which makes the data more secure.
On the connection scheme, you can select method access for TCP/IP sockets and Named pipes. A user with an NT user account is suitable for using a named pipe method, without
Users with NT user accounts are not suitable for this method. This is because before the connection is established, the database user must be confirmed by NT, and the TCP/IP socket is selected
Then there is no such limit, the connection can be directly connected to the database server, without having to use the intermediate computer of the named pipe, only through SQL Server to confirm the user
Access is available.

Because it is the ASP application that writes the database connection, the data access programming pattern that uses is of course ADO! Previous data access programming patterns in ADO have data
The Access object DAO and Remote Access Object Rdo,rdodao are all developed under ODBC, which is a SQL-based desktop discipline written in C language. In contrast, ADO is
A new procedure for OLE
DB, OLE DB can be viewed as OLE technology applied in a database. It is developed based on C + + and provides a standardized COM connection to the capabilities of ODBC
Mouth. C + + is object-oriented, so ADO also has object-oriented features. And can expand itself through a series of COM interfaces. This is the case for the ASP program,
It's too important. We know that the functionality of the VBScript scripting language used by ASP programs is very weak to augment the ability of VBScript to access several
According to, you must create a server-side component that accesses data through a COM interface, which is what ADO is good at.

You must also create a data source name (DSN) on the Web server when you are programming to connect to the servers and databases specified on the network. To set a DSN, you must give the service
The server provides the name of the database, the ODBC driver used for the communication, and the address on the network. Note that only the DSN that is set up is a system DSN to
Let ADO work properly in ASP. How can I determine if a DSN is a System DSN instead of a user or a file DSN? Very simply, only for all users and services on the NT Server
DSN is the System DSN that can be accessed.
Set SQL
Server DSN is simple, you only need to start the "ODBC" Control Panel on a machine with IIS or PWS, and in the ODBC Data Source Administrator dialog box, select
Select SQL SERVER after the system data source Name option is taken. If there is no SQL SERVER, add it first and then select it. This way, even if I
To change the database, you do not have to modify the ODBC parameters in the program, just reset the System DSN.
Another important issue in designing an ASP program to connect to SQL Server is to implement access control for SQL Server. Because in a relational database, the DBMS itself
The DCL language only supports access control for tables and fields, and does not directly support security protection for records in a table. In order to achieve its security, but also without losing B/s structure
Can be considered in the way of using the middle-tier access program logic combined with triggers. Using a middle-tier access program that is client-side programs not directly visited
Ask the DBMS, but through a middle-tier access program. Using online BBS For example, when the user inquires, the Access program returns all the records in the table to the user.
When a user inserts a record, the accessor fills in the current username in the User_name field of the new record, indicating that the owner of the record, when the user deletes and modifies the record, accesses
The program returns only his own records to the user based on the User_name field, and allows the user to choose from their own records. The way you use triggers
Is that all users access the table directly, but the following triggers are established on the table:

CREATE TRIGGER trigger_1 on Table_ 1 for INSERT as
BEGIN
IF EXITS (SELECT * from inserted WHERE user_name<>user_name ())
BEGIN
ROLLBACK TRANSACTION
RAISERROR (' Please enter the correct username ', 16,-1)
End
End
CREATE TRIGGER trigger_2 on table_1 for Update,delete as
BEGIN
IF EXITS (SELECT * from deleted WHERE user_name<>user_name ())
BEGIN
RAISERROR (' You can't modify other people's data ', 16,-1)
End
End


In the above program, Trigger_1 rejects the insert request of a user with an incorrect username. Trigger_2 for attempts to modify the records of others
Shown to be rejected. Because triggers are attached to a basic table in SQL Server, no matter how the user updates a record, the trigger's check cannot be skipped because
The security that is implemented using triggers is quite high. However, you cannot define a trigger for a query because you can only define triggers that are deleted and modified for manuscripts in a table
, therefore, to achieve access control of the query, through the middle-tier access program, only to the user query to return a specific condition of the record to achieve.