connecting to MSDE using SSPI

Please advise: I have created a simple ASPX page to test if I can connect to MSDE on a Server 2003 system. The connection string that works ok is:

server=WS1;database=northwind;integrated security=false;user id=sa;password=xxx;

but the string that does not work is:

server=WS1;database=northwind;integrated security=SSPI;

which is the string I would like to use for normal web access. This string works fine on my development system (where I use XP Pro and IIS 5.1)

In IIS6 on the "production server" the directory security on the virtual dir is set to allow anonymous access, using user IUSR_WS1 (where WS1 is the name of the system) and to integrated security.

Please help! Thank you

John BI forgot to say the error message I am getting is:

login failed for user 'NTAUTHORITY\NETWORK SERVICE'|||There is a KB for this problem:PRB: "Login Failed" Error Message When You Create a Trusted Data Connection from ASP.NET to SQL Server. See if any of the 3 suggested Resolutions work for you.

To me, the 3rd suggestion seems to be the best course of action. And taking that further, what seems to make the most sense is to add a new database role called "webuser" or something similar, and then add the NT AUTHORITY\NETWORK SERVICE user to that role. This will give you more flexibility if the web app moves to a Windows 2000 machine -- you would just be able to add the ASPNET account as another member of that webuser role.

Terri|||Thank you for your reply. I looked at the article, but I regret to say it does not make much sense to me at this stage. I have bought the MSDE Admin tool to administer MSDE, or could use the osql tool, but am not clear how to:

a. add a new database role (is this adding a "normal" user group)? I dont see any reference to a "database role".

b. add the NTAUTHORITY\NETWORK SERVICE user to that role. I have looked at the list of users and there is no such name. I must be looking in the wrong place.

Excuse my ignorance!

TIA

John B|||You should downloadSQL Server 2000 Books Online. This is a huge download but an essential reference.

I have to admit, I am confused on this issue. I have now come across an article right on this siteRunning ASP.NET 1.1 with IIS 6.0, and part of it covers supporting integrated authentication with SQL Server. The suggestion I gave you was based on something I read elsewhere that seemed to make sense.

Further information on the path I suggested:

The system stored procedure to add a database role is sp_addrole. You'd use it like this:

EXEC sp_addrole 'webuser'

The system stored procedure to add a security account for a Windows user to the current database and enable it to be granted permissions to perform activities in the database is sp_grantdbaccess.

EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE', 'Network Service'

The system stored procedure to add a security account as a member of an existing database role in the current database is sp_addrolemember.

EXEC sp_addrolemember 'webuser', 'Network Service'

Terri|||Thank you. The article you suggested in the last post is very helpful. I have now solved the problem, thanks to an article I found www.cgnit.com/resources/ntauthority_networkservice_error.html

which hits it bang on the nose, and may be useful to others.

It seems to me that all the books, and many of the forums suggest that when you run an ASP.NET app and use anonymous access, the system uses the identity mcxxx\aspnet (where mcxxx is the domain or machine no). But what I have found so far is that under Server 2003, the identity assumed is the mysterious user NTAUTHORITY\NETWORKSERVICE which is NOT found in the list of users, but is apparently a member of the IIS_WPG group, so configuring this group to access the database solves the problem and allows an anonymous user to access records according to the permissions set within MSDE or SQL Server.

Can anyone confirm this is true? Is the ASPNET user in fact NEVER used in Server 2003, but only in W2000 or XP?|||Yes, I can confirm that. I ran into this today, setting up ibuyspy on a Windows2003 server. Here's the quote I found in the machine.config file:
When ASP.NET is running under IIS 6 in native mode, the IIS 6 process model is
used and settings in this section are ignored. Please use the IIS administrative
UI to configure things like process identity and cycling for the IIS
worker process for the desired application

I went ahead and added the IIS_WPG to access the DB I cared about, but you could also setup your own IIS6 AppPool with seperate identity information.

/Brad|||

I cannot but say, its a great thread !!

regards