Managing Secure Database Connections with SQL Server

Safely manage access to your Web site

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,, 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,, 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

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,, 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 adoprg04_9gfj.asp. Make sure you click the Dynamic Properties link to expand the list of properties.

Using Connection Strings
Connection strings are versatile, and because they're dependent on a specific setting within your system, you can place them almost anywhere in an application. This capability can become a liability, however, because developers often make the mistake of embedding DSN-less connection information in every Web page, then discover that keeping that information up-to-date is a maintenance nightmare. When you work with DSN-less database connection information, each application should have only one repository for connection information. You can locate this repository in one include file, in the system registry, or—as Microsoft suggests—in a Microsoft .NET web.config file.

Putting the information in an include file that's part of the Web site is a good idea because the file moves with the content that's associated with the site. Therefore, if you move the content from one server to another server, the database settings automatically move, too. You might be concerned that embedding the username and password in an include file would expose the information to intruders. This approach does represent a risk, but several factors mitigate that risk. For example, you can encrypt the username and password, then boost performance by decrypting them and loading the clear versions into application memory when you use them. You can further mitigate the risk and secure the environment by limiting the privileges associated with the database accounts that the application uses.

The registry is an excellent central storage location for application settings because Active Server Pages (ASP) and components can easily access it. However, registry entries don't automatically move with Web files. Connection-string data stored in the registry probably won't be the only such settings on an ASP site. In ASP, as opposed to ASP.NET, several application settings related to component registration probably live in the registry. Although tools such as Microsoft Application Center 2000 can manage many Web server settings across all the servers in a Web farm, keeping settings in the registry doesn't give you all the benefits that connection strings can provide.

ASP.NET lets you package a Web application as a set of files by using the web.config file to include settings associated with a connection string. When the Web application starts, it loads these settings into memory and makes them available to the Web site's pages and components. For example, within the web.config file's XML code in an ASP.NET application, you'll find the SessionState node. Within the SessionState settings, Visual Studio .NET automatically generates a placeholder connection string. Using that connection string, you can quickly customize a Web-farm­hosted application to automatically use a SQL Server Cluster so that session data is available to all the servers in the Web farm.

When I use ASP.NET as an example, I don't mean to imply that only ASP.NET-based applications let you place connection information in one location. You can achieve a similar result in other applications by placing the information in an include file or other file that IIS doesn't directly reference. You want to be able to update the connection information, and having that information in one location simplifies that task. During the application's lifetime, you'll need to update the connection string when you move or rename servers.

Furthermore, as with other account information, you'll want to maintain security by regularly changing the credentials that the application connection string uses. If you're working with application developers, be sure that they include in the application a way for an administrator to change the connection-string credentials. To manage this requirement, you need to include a change to the application connection credentials in your application test plan. The final development step should be to thoroughly test the application to ensure that all the application components (e.g., pages, DLLs, config files) are packaged correctly.

The developers will need to obtain the SQL Server account information and set the application's connection strings and environment settings before testing the application. After the application is set up in the testing environment and is connected to the SQL Server database, change the connection information to test that administrative function. Performing this test early in the testing phase will give the development team time to correct problems. If you make any exemptions to let testing continue until the software that uses embedded connection strings is corrected, you should again change the connection information as the final step in the testing cycle to ensure that the feature still works. When preparing to move an application to a test or staging environment, developers ask for the SQL Server account information so that they can set the application's connection strings and the application environment settings. After the initial application setup and after you demonstrate connectivity, you can make changes to the connection information. These changes can be the first step in your test plan. If the application is written to use connection information that's stored in one location, this test should be simple to perform. Conducting this step early in the test plan will give the development team time to correct problems, but remember that if you make any exemptions, this test should also be the final test for the application. As an administrator, you don't want to be in a situation in which you can't maintain the accounts associated with database access because the connection string is scattered throughout various files and registry keys on your server.

Preparing to Manage an Enterprise Application
Unlike a desktop environment, which almost guarantees single-user access and control over the local file system, the Web is a multithreaded, multiuser environment—in some cases, it's even a multiserver environment. A robust database solution such as SQL Server is an important component of a scalable, multithreaded environment that supports multiple users simultaneously. Applications that use easily modifiable DSN-less connection strings to manage access to the database keep the connection details separate from the OS details. As I'll discuss in a future article, by properly managing the acounts associated with application data access, you can create a secure environment for your public-facing Web applications.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.