Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005

Configuring an ASP.NET 2 application to work with SQL Server is not an easy undertaking. You need to be aware of a number of security related concepts in the area of SQL Server, IIS and ASP.NET 2.0. The number of different options you have make the problem even more complex. In this article, I describe some of the common scenarios that you are likely to run into when working with SQL Server and ASP.NET pages. I'll be using SQL Server 2005 in all my examples, but most of the concepts also apply to SQL Server 2000. Note that most of the principles described in this article can also be used in ASP.NET 1.x applications.

Many books about ASP.NET 2.0 (including my own), use Microsoft SQL Server 2005 Express Edition. The reason behind this choice is that the Express Edition is very easy to use and configure. In many cases, using a SQL Server Express database is as easy as adding a database to your project (or one of the new Login controls), and then hitting F5 to run the application. This makes the Express Edition great for local development and quick and dirty applications. However, in a production environment, the Express Edition won't cut it. Instead, you'll need one of its bigger brothers like SQL Server 2005 Standard Edition. But as soon as you try to make the move to one of these SQL Server versions, you may run into a number of security related configuration issues. This article takes a look at a number of common issues that you need to be aware of when you try to use SQL Server 2005 in your applications.

The article starts off with familiarizing you with the terminology that you'll run into when configuring the system. The second part then looks at a number of different scenarios for configuring an ASP.NET 2.0 application with SQL Server 2005. You'll see how to use a custom SQL Server database in your own application and how to configure both SQL Server and your application.

This article doesn't touch on configuring your database for the new ASP.NET 2.0 provider features, like Membership and the Role Manager. However, there's a great article by Scott Guthrie called "Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005" that shows you how to use tools like aspnet_regsql.exe to configure an existing SQL Server 2000 or 2005 to work nicely with the new provider features. That article, together with this one, should provide you with enough details to set up your system so it can work with a SQL Server 2000 or 2005 database.

Read more... (By Imar Spaanjaars)