How to connect ASP to SQL Server 2008 database through ODBC

This article mainly introduces the method of ASP connecting to SQL Server 2008 database through ODBC. Friends in need can refer to it.

Method to create database connection file [dsn file]

Create ODBC DSN file

Before creating a database script, you must provide a way for ADO to locate, identify, and communicate with the database. The database driver uses a Data Source Name (DSN) to locate and identify a specific ODBC-compliant database to pass information from the Web application to the database. Typically, a DSN contains database configuration, user security, and location information, and can be obtained as a table in a Windows NT registry key or as a text file.

With ODBC, you can choose the type of DSN you want to create: user, system, or file. User and system DSNs are stored in the Windows NT registry. The system DSN allows all users logged on to a specific server to access the database, while the user DSN restricts database connections to specific users using appropriate security credentials. File DSN is used to obtain tables from text files, provides access to multiple users, and can be easily transferred from one server to another by copying the DSN file. For these reasons, the examples in this topic use file DSNs.

You can create DSN-based files by opening Control Panel from the Windows Start menu. Double-click the ODBC icon, then select the File DSN property page, click Add, select Database Driver, and click Next. Follow the instructions below to configure the DSN for your database software.

Configuring File DSN for Microsoft Access Database

1. In the Create New Data Source dialog box, select Microsoft Access Driver from the list box and click Next.
2. Type your DSN file name and click Next.
3. Click Finish to create the data source.
4. In the ODBC Microsoft Access 97 Setup dialog box, click Select. Select the Microsoft Access database file (*.mdb) and click OK.

NOTE For performance and reliability reasons, we strongly recommend that you use a client-server database engine to configure data driven by Web applications that must be accessible to more than 10 users simultaneously. Although ASP can use any ODBC-compliant database, it is designed and rigorously tested for use with client-server databases, including Microsoft®SQL Server, Oracle, and others.

ASP supports shared file databases (such as Microsoft® Access or Microsoft® FoxPro) as valid data sources. Although some examples in the ASP documentation use shared file databases, we recommend using such database engines only for development or limited deployment scenarios. Shared file databases may not be a good fit for client-server databases that cater to high-demand, high-quality Web applications.

Configure SQL Server database file DSN

Note If the database resides on a remote server, contact the server administrator for additional configuration information; the following procedure uses the SQL Server's ODBC default settings, which may not apply to your hardware configuration.

1. In the Create New Data Source dialog box, select SQL Server from the list box and click Next.
2. Type a name for the DSN file and click Next.
3. Click Finish to create the data source.
4. Type the name, login ID, and password of the server running the SQL service program.

1. In the Create New Data Source for SQL Server dialog box, type the name of the server that contains the SQL Server database in the Server list box, and then click Next.

1. Select how to verify your login ID.

1. If you choose SQL server authentication, enter a login ID and password, and click Next.
2. In the Create New Data Source for SQL Server dialog box, set the default database, driver for stored procedure settings, and ANSI identification, and then click Next. (For more information, click Help.)
3. In the dialog box (also named Create New Data Source for SQL Server), select a character conversion method and click Next. (For more information, click Help.)
4. In the next dialog box (also named Create New Data Source for SQL Server), select Login Settings.

   Note Typically, you can only use logs to debug database access issues.

5. In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. If the DSN was created correctly, the test results dialog box will indicate that the test completed successfully.

   SQL server connection and security information

   If you are developing an ASP database application that connects to a remote SQL Server database, you should consider the following issues:

   • Connection options - You can choose between TCP/IP sockets and named pipes to access remote SQL Server databases. When using named pipes, because the database user must be authenticated to Windows NT before a connection can be established, users with appropriate SQL Server access identities but without a Windows NT user account on the computer may be denied access to the named pipe. As an alternative, a connection using TCP/IP sockets can be connected directly to the database server without going through an intermediary computer using named pipes. Because you can connect directly to the database server using a TCP/IP socket connection, users can gain access through SQL Server authentication without having to authenticate through Windows NT.

     Note that using TCP/IP sockets can improve performance when connecting to remote databases.

   • Security - If you are using SQL Server's integrated or hybrid security features, and the SQL Server database is on a remote server, you cannot use Windows NT request/response acknowledgment. That is, the Windows NT request/response ID cannot be forwarded to the remote computer, but only basic authentication can be used, which is based on the user's username and password information.

     For more information on this topic, see http://www.microsoft.com/sqlsupport/

     Microsoft SQL Server Technical Support home page.

Configure Oracle database file DSN

First make sure that the Oracle user software is properly installed on the computer on which the DSN is to be created. For more information, contact your server administrator or consult your database software documentation.

1. In the Create New Data Source dialog box, select Microsoft ODBC for Oracle from the list box and click Next.

1. Type a name for the DSN file and click Next.

1. Click Finish to create the data source.

1. Enter the username, password, and server name and click OK.

Note that DSN files have a .dsn extension and are located in the /Programs/Common Files/ODBC/Data Sources directory.

For more information about creating DSN files, visit the Microsoft ODBC Web site: http://microsoft.com/odbc/.

================================================== ==

The first step in accessing database information is to establish a connection to the database source. ADO provides a Connection object that can be used to establish and manage connections between applications and ODBC databases. The Connection object has various properties and methods that you can use to open and close database connections and issue query requests to update information.

To establish a database connection, you should first create an instance of the Connection object. For example, the following script creates a Connection object and then opens a database connection:

1. <%
2. 'Createaconnectionobject
3. Setcn=Server.CreateObject(ADODB.Connection)
4. 'Openaconnection;thestringreferstotheDSN
5. cn.OpenFILEDSN=MyDatabase.dsn
6. %>