Databases are integral components of Web site architecture. Although personal sites such as those that host family vacation photos might not need databases, sites that support businesses do, and managing Web users' access to the company database is a vital concern for Web site administrators.
In this article, I cover some basic principles of Microsoft SQL Server. Although I concentrate on using SQL Server with public Web sites, the principles apply equally to intranet sites. I also discuss some database basics and explain how to create and manage secure database connections.
Database Basics
Using a database is the best way to manage transactions and dynamic data. But why use SQL Server? Why not use Microsoft Access? Although Access is a database of sorts, a close look reveals several potential problems. Microsoft designed Access as a single-user desktop database; it doesn't have a security model. Access requires single threading and, as a result, isn't well suited to multithreaded Web environments. As a file-based data store, Access isn't compatible with a Web farm's multiserver environment. And because Microsoft designed Access to support single-user desktops, it requires a shared drive that all the servers in the Web farm must access. Although SQL Server provides built-in support for transactions, security roles, and accounts, Access relies on a limited security model that isn't designed to support a multithreaded Web application. So even if you're setting up a small site, Access isn't a viable solution if that site is in a load-balanced environment. (For information about threads and threading, see "Inside the Windows NT Scheduler, Part 1," July 1997, http://www.winnetmag.com, InstantDoc ID 302.)
For Microsoft-oriented solutions, SQL Server is the database of choice. Other multiuser databases (such as the freely available Microsoft SQL Server Desktop Engine—MSDE) can fill the role I describe in this article. But for business data accessible through the Web, SQL Server is superior because it combines a robust data store and a solid security model.
Hosting SQL Server on a server other than your Microsoft IIS server is the first element of this security model. Although separate hosting improves performance, security is the primary reason to install your database on a separate server. By keeping your database on a separate server, you can begin to insulate it from the risks that your Web server faces.
As the public-facing component of your Internet presence, your Web server is the most exposed server and the most likely server to be compromised in your network architecture. You don't want to put your data on your most vulnerable server. Intruders who manage to compromise your Web server can easily gain administrative privileges and access your Web applications; installing SQL Server on a separate server ensures that those intruders won't gain access to the real prize: corporate data.
Most corporate system engineers block access from ports 80 (HTTP) and 443 (HTTP Secure—HTTPS) to the server that hosts the SQL Server database. Many engineers use a firewall for this task, but even engineers in small companies that have few resources use tools such as IP Security (IPSec) to block access to the SQL Server system through common Web ports. (For information about IPSec, see "Protect Private Ports with IPSec," April 2002, http://www.windowswebsolutions.com, InstantDoc ID 24273.)
Managing Database Connectivity
When you consider firewall security, remember that SQL Server's default configuration uses port 1433. However, when you work with named instances, each named instance uses the next available higher-numbered port. SQL Server named instances let you create separate data environments that provide, among other things, separate processes associated with different applications. (For more information about instancing, see the sidebar "SQL Server Named Instances," page 6.) Whether you have one instance or multiple instances of SQL Server, the Microsoft Data Access Components (MDAC) library is key to gaining access to the database server from your IIS application. MDAC 2.7 is the most recent version of the software. However, for clustered installations that use SQL Server 2000 or earlier, you should configure your Web server to use MDAC 2.5 Service Pack 2 (SP2). You can download the appropriate MDAC version at http://www.microsoft.com/data/download.htm.
After you install the appropriate version of MDAC on your Web server, your Web applications will use MDAC to connect to SQL Server. You can configure SQL Server connections to use an ODBC Data Source Name (DSN). "SQL Server Database Access with IIS," April 2001, http://www.windowswebsolutions.com, InstantDoc ID 20061, explains how to use a DSN to connect to SQL Server. In the past, connections to SQL Server were typically based on a DSN that was installed on the Web server separately from the actual application files. However, new applications use DSN-less connections. These connections are common to both ADO and ADO.NET databases and can be faster than DSN-based connections (because you avoid repeated registry hits) and easier to install and maintain.
ADO and ADO.NET let you create a DSN-less connection and provide the name or IP address of the server, the name of the database on that server, and a username and password for the connection. This approach is much more portable than the DSN approach because you don't need to hard-code the DSN name within the application. ADO and ADO.NET let applications pull connection-string information from any location on the server or even determine it on the fly. The connection string provides the who, what, where, and how for connecting to the database.
Figure 1 shows a typical connection string. This string specifies a connection to the local machine for a database called myDB and attempts to connect by using the systems administrator (sa) account with a blank password. If the sa account is secured with a password, the connection will fail and the system won't prompt for authorization.
This example is fairly simple and illustrates the basic property name = value; format. I formatted this string in the order that I prefer—machine, server, user, password—but you can reorder the various elements. For example, you might place the user id=sa; property at the beginning of the string. Figure 2 shows a more complex string that connects to a different database and specifies that the system use the OLE DB driver.
This string specifies a database connection to a SQL Server database named IKTC1 within the Extranet named instance on the IKSQL01 server. Connection strings can use many possible combinations of elements to provide application-specific control over essentially all the details of each database connection. For more information about the various connection-string parameters and some of their synonyms, review the information about the ADO Connection Object at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/
adoprg04_9gfj.asp. Make sure you click the Dynamic Properties link to expand the list of properties.
Prev. page
[1]
2
next page 
