Application Design Considerations

Connecting to Your Database

Connecting to your database in a Web application is similar to connecting to your database in Windows applications. You still use a Connection object to manage the connection to your database. However, there are some slight differences to keep in mind when connecting in a Web application.

Working with Trusted Connections

Throughout this text, we've used trusted connections to connect to the local .NET Framework SDK MSDE installation on your development machine:

Provider=SQLOLEDB;Data Source=(local)\NetSDK; Initial Catalog=Northwind;Trusted_Connection=Yes;

You can use this same connection string at design time in a Web application, but you might encounter problems using this connection string at run time. Say you create a new Web application and add the following code to your Web Form's Load event:

Visual Basic .NET

Dim strConn As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open()

Visual C# .NET

string strConn; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open();

Depending on the version of Windows that you're using to build your Web application, you might generate an exception when you try to run this application with the following message:

Login failed for user 'MyMachineName\ASPNET'

This same connection string worked just fine in Windows and Console applications. Why would it fail in a Web application? Remember that when you connect to SQL Server using a trusted connection, SQL Server uses your network credentials to determine whether you can connect to the database. The exception's message indicates why the connection is failing. The connection attempt is made using the ASP.NET account's credentials, not yours, because the code is running in the ASP.NET process. The result of the connection attempt depends on whether the ASP.NET account has privileges to the SQL Server database.

SQL Server Enterprise Manager makes granting database access to an account simple. Expand the Security folder for the server, right-click Logins, and select New Login. If you're working with an MSDE database and don't have access to Enterprise Manager, you can also execute the following query to grant account access to SQL Server:

exec sp_grantlogin 'DomainOrMachineName\AccountName'

Impersonating Users

What if you want to use the actual user's credentials to log in to your database?

ASP.NET makes impersonating users relatively straightforward. When you create a new Web application using Visual Studio .NET, you'll see a file called Web.config in Solution Explorer. This file is an XML document that contains settings for your application. In this document, you'll find an authorization section that looks like this:

<authorization>     <allow users="*" /> </authorization>

These settings allow all users to connect to the application. You can have the application impersonate the current user and deny anonymous logins by changing these settings to the following:

<authorization>     <deny users="?" /> </authorization> <identity impersonate="true" />

Once you've made these changes, attempts to connect to your database using trusted connections rely on the actual user's network credentials. However, I'm not a big fan of using trusted connections in this fashion for Web applications for a number of reasons.

This architecture does not benefit from connection pooling. If three different users connect to a Web application that relies on impersonation and trusted connections, each of these connections has a different security context. As a result, ADO.NET can't pool User A's connection and then reuse it for User B. In fact, connection pooling can adversely affect the performance of this type of application. Each user's connection will remain open until it's removed from the pool, but it can be reused only by that particular user.

I don't like the idea of relying on the database to enforce security in an application. If you're familiar with trusted connections to your database, it's tempting to rely on this feature rather than learn about different ASP.NET authentication options. There's no need to incur a network round-trip to your database in order to authenticate the current user. ASP.NET offers plenty of options for enforcing security in your application—Windows authentication, Passport authentication, and Forms authentication.

Working with Access Databases

Many developers are used to communicating with Access databases and plan on relying on them in their ASP.NET applications. I don't want to encourage that. Access databases are not designed for large numbers of simultaneous users. They're not scalable like SQL Server and Oracle databases. However, I can understand their appeal. They're extremely simple to create and manage.

So, I'd be performing a disservice if I didn't talk briefly about working with Access databases in ASP.NET applications. Late in the development of .NET, Microsoft made some changes to the network permissions associated with the ASP.NET account. These changes can affect developers working with Access databases in their ASP.NET applications.

When you open an Access database, the Jet database engine uses a locking file associated with the database to keep track of the various locks (both read and write) that users have placed on various rows and pages. As a result, the user who is accessing the database needs read/write permissions on the directory in which the Access database resides. If you receive either of the following exceptions,

The Microsoft Jet database engine cannot open the file  'C:\Path\To\MyDatabase.mdb'. It is already opened exclusively by another  user, or you need permission to view its data.

or

Operation must use an updateable query.

the likely cause of the problem is the fact that the ASP.NET account cannot write to the lock file. Make sure that the ASP.NET account has read/write access to the directory in which your Access database resides.