View Single Post
  #2 (permalink)  
Old June 6th, 2003, 02:22 PM
Imar's Avatar
Imar Imar is offline
Wrox Author
Points: 71,804, Level: 100
Points: 71,804, Level: 100 Points: 71,804, Level: 100 Points: 71,804, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,048
Thanks: 80
Thanked 1,581 Times in 1,558 Posts

Hmmmm, there are lots of books dealing with these issues so it's hard to name one.

Anyway, what you may need is a basic understanding of the authentication mechanism of SQL Server and MSDE. The MSDE is basically the same as SQL Server, except that it's limited to just a few users (after 5, if I recall correctly, things start to slow down).

Anyway, SQL Server 2000 (and MSDE 2000) have two different security mechanisms: SQL Security and Integrated Security.

The first is the easiest to understand. You define a user in SQL server, supply an account and a password which you can use to connect to the server. An example of a connection string looks like this:

"Provider=sqloledb;Data Source=ServerName;Initial Catalog=DatabaseName;User Id=YourUser;Password=YourPassword;"

Since you need to store the password in the connection string (and thus in include files or configuration files) this is considered an insecure solution. However, it's easy to set up and use. Once you can log in to the Query Analyzer with this account, you can be pretty sure you connect to it from a database.

The other possibility is Integrated Security where the connection is made under the context of the "current user". An example of a connection string could look like this:

"Provider=sqloledb;Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;" "

You can also replace Integrated Security=SSPI; with Trusted_Connection=True if I am not mistaken (it's Friday afternoon, with beautiful weather and too much beer here ;) )

Now, the concept of the "current user" may be hard to graps at first as it depends on a lot of factors.

First of all, the IIS user is used in ASP solutions when IIS is set to Anonymous access. If that's the case, the IUSR_MachineName is the current user. For ASP.NET solutions, the ASPNET account is that user.

If anonymous access is off (you use Basic or Integrated security in IIS) the current user is the user visiting your site.

For more details on the ASPNET account, check out:

As for recommendations, it really depends on your application. In classic ASP I'd like to have a VB DataAccess layer that takes care of all the data access. This DLL can be configured using COM+ to use a specific user context.
In ASP.NET with Anonymous Access, Integrated Security has always worked for me (add the ASPNET account as a database account). Alternatively, you can change that account as the URL I posted suggests.

I know it's not a book, but it may give you a head start in the right direction.