Recipe 1.5 Connecting to an Access Databasefrom ASP.NET

Recipe 1.5 Connecting to an Access Databasefrom ASP.NET

Problem

You know your connection string is correct, but still can't connect to your Microsoft Access database from your ASP.NET application. What are the differences between connecting from a Windows Forms .NET application and an ASP.NET application?

Solution

You must grant the necessary file permissions for accessing a Jet database (Microsoft's transparent data access engine) to the default user account used by ASP.NET.

Discussion

When a user retrieves a page from an ASP.NET web site, code runs on the server to generate and deliver the page. By default, IIS (Internet Information Server) uses the system account to provide the security context for all processes. This account can access the IIS computer, but is not allowed to access network shares on other computers.

To allow an ASP.NET application to connect to a Microsoft Access database, IIS must be configured to use an account other than the system account. The new account must be configured to have permission to access all files and folders needed to use the Access database. If the Access database is on a remote computer, the account also requires access to that computer.

The following sections describe how to configure the IIS Server and the Access computer to allow ASP.NET to connect to an Access database.

Configure IIS

The system account cannot authenticate across a network. Enable impersonation in the web.config file for a given ASP.NET application so that ASP.NET impersonates an account on the Microsoft Access computer with the required access permissions to the Access database. For example:

 <identity impersonate="true" userName="domain\username"
    password="myPassword" /> 

This method stores the username and password in clear text on the server. Ensure that IIS is configured to prevent users of the web site from viewing the contents of the web.config filethis is the default configuration. Other ways to impersonate a user from an ASP page are described in the Microsoft Knowledge Base article Q248187.

The Microsoft Jet engine uses the TEMP folder on the IIS computer that is accessing the Access database. The user identity requires NTFS (Windows NT File System) full-control permissions on the TEMP folder. Ensure that the TEMP and TMP environment variables are properly configured.

Configure the Access server

On the Access computer, the user account that is used to access the database requires Read, Write, Execute, and Change permissions on the database file. The user identity needs Read, Write, Execute, Delete, and Change permissions on the folder containing the database files. The user account requires permissions to access the share that contains the database file and folders.

The user account must be recognized by the Access computer. For a domain user account, add it to the permissions list on both computers. For a user account local to the IIS computer, create a duplicate account on the Access computer with the same name and password.

Grant the user account Log on Locally and Access this Computer from the Network permission to access the computer in the local security policy. These permissions are assigned within the Security Settings \ Local Policies \ User Rights Assignment node in the Local Security Policy tool.

Recipe 1.6 Using an IP Address to Connect to SQL Server

Problem

You want to connect to a SQL Server using its IP address instead of its server name .

Solution

Use the Network Address and Network Library attributes of the connection string.

The sample code contains a single event handler:

Connect Button.Click

Creates and opens a connection to a SQL Server using its IP address. Information about the SQL Server is displayed from the properties of the SqlConnection object.

The C# code is shown in Example 1-5.

Example 1-5. File: ConnectSqlServerIpAddressForm.cs
 // Namespaces, variables, and constants
using System;
using System.Data.SqlClient;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    String connString =
        "Network Library=dbmssocn;Network Address=127.0.0.1
;" +
        "Integrated security=SSPI;Initial Catalog=Northwind";
    
    SqlConnection conn = new SqlConnection(connString);
    conn.Open( );

    // Return some information about the server.
    resultTextBox.Text =
        "ConnectionState = " + conn.State + Environment.NewLine +
        "DataSource = " + conn.DataSource + Environment.NewLine +
        "ServerVersion = " + conn.ServerVersion + Environment.NewLine;

    conn.Close( );

    resultTextBox.Text += "ConnectionState = " + conn.State;
} 

Discussion

SQL Server network libraries are dynamic-link libraries (DLLs) that perform network operations required for client computers and SQL Server computers to communicate. A server can monitor multiple libraries simultaneously ; the only requirement is that each network library to be monitored is installed and configured.

Available network libraries for SQL Server 2000 include:

AppleTalk ADSP

Allows Apple Macintosh to communicate with SQL Server using native AppleTalk protocol.

Banyan VINES

Supports Banyan VINES Sequenced Packet Protocol (SPP) across Banyan VINES IP network protocol.

Multiprotocol

Automatically chooses the first available network protocol to establish a connection generally with performance comparable to using a native network library. TCP/IP Sockets, NWLink IPX/SPX, and Named Pipes are supported.

Named Pipes

Interprocess communication (IPC) mechanism provided by SQL Server for communication between clients and servers.

NWLink IPX/SPX

The native protocol of Novell Netware networks.

TCP/IP Sockets

Uses standard Windows sockets to communicate across the TCP/IP protocol.

Clustered installations of SQL Server support only Named Pipes and TCP/IP protocols. AppleTalk, Banyan Vines, and Multiprotocol protocols are unavailable if named instances are installed on the server.

For more information about network libraries and configuring network libraries, see Microsoft SQL Server Books Online.

The use of the SQL Server TCP/IP Sockets improves performance and scalability with high volumes of data. It avoids some security issues associated with named pipes. As with any protocol, the client and the server must be configured to use TCP/IP.

To connect to SQL Server using an IP address, the TCP/IP network library must be used to connect to the SQL Server. This is done by specifying the library in the connection string as either the attribute Net or Network Library with a value of dbmssocn . Specify the IP address using the Data Source , Server , Address , Addr , or Network Address parameter. The following connection string demonstrates using an IP address to specify the data source:

 Network Library=dbmssocn;Network Address=127.0.0.1;
    Integrated security=SSPI;Initial Catalog=Northwind 

In the example, the IP address is the local machine. This could also be specified as ( local ). To specify a SQL Server other than a local instance, specify the IP address of the computer on which SQL Server is installed.

Default instances of SQL Server listen on port 1433. Named instances of SQL Server dynamically assign a port number when they are first started. The example above does not specify the port number and therefore uses the default port 1433 of the SQL Server. If the SQL Server is configured to listen on another port, specify the port number following the IP address specified by the Network Address attribute separated by a comma as shown in the following snippet, which connects to a local SQL Server listening on port 1450:

 Network Address=(local),1450