Browser -> IIS -> SQL

[dave755]

I am trying to configure an in-building intranet system with multiple servers and am not getting it right. Here is the situation:

The user sits at a browser and accesses an ASP page.

The ASP is being hosted by a server running IIS on [red]Windows Server 2003[/red].

The VBScript code in the ASP uses a DNS to reference a SQL Server that is running on a second (separate) server, which is running [red]Windows 2000 Server[/red].

Here is the error reported to the browser:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

When I test the DNS on the IIS server, it checks out OK.

What do I need to do to make this run?

Dave Gee

 

[mrdenny]

Your DNS is working fine. The error message you are getting back si from the SQL Server. You are attempting to log into the database using a trusted connection, and the web server is not passing the users info across.

What are you security settings in IIS set to? (The security tab of the web site in the IIS manager.)

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dave755]

For reference:
The IIS server is on the computer named "TEST2003"
SQL Server is on the computer named "DATASTORE"

Here are the settings under the "Authentication and Access Control" section of the "Directory Security" tab:

-- Anonymous Access is enabled.
-- The username is set to IUSR_TEST2003
-- The password is blank. This matches the user account set up on DATASTORE.
-- Integrated Windows Authentication is checked.
-- In SQL Server itself, a login is defined as a "Windows User" with the name DATASTORE\IUSR_TEST2003


Dave Gee

 

[mrdenny]

OK, I should have asked this before. Are you trying to have everyone log in with a single windows account, or do you want everyone to log in with there own windows account?

From what you've described you want to use a single common windows account.

Instead of using local accounts, you'll want to create a domain account for IUSR_TEST2003 or something like that, and setup IIS to use that account, and setup that account with acess to the SQL Server. Turn off Intergrated Windows Authentication (You don't need this since you have Anonymouse Access turned on).

The reason for using the domain account, instead of the local accounts, is that you want IIS to be able to manage the account. With seperate accounts on each server it can't do this.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dave755]

Thanks for the quick responses.

Short answer: Yes.

I will have 100 or so different people accessing this web for various reasons. I do not want to have to individually credential each of them.

Potential complication - you spoke of domain accounts. At present, there is no domain in sight. Is there a way to do this without using domains or do I need to bite the bullet and convert over to domain-ness?

I have been threatening to set up a domain for a while now, but never really had a solid reason to do so. Is this it?

Dave Gee

 

[mrdenny]

There are lots of reasons to setup a domain. This is one of them.

Ease of Managment of user accounts
Ease of installation when setting up new machines
Ease of deployment of security patches
Domain based auditing
Increased security on the machines

These are all just a few reasons to setup a domain.
With 100 or so people, I'm very suprised that you don't have a domain already.

You could also simply setup the web page to log into the SQL Server using a SQL Login instead of a windows login. That will get you up and running until the domain is setup.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dave755]

Inertia is a horrible thing...

Thank you for the help. I will set about the task (tomorrow).

Dave Gee

 

[mrdenny]

no problem

Denny

--Anything is possible. All it takes is a little research. (Me)