sql server and asp.net problem

[Brian Henry]

I moved my asp.net site to a windows 2003 server with SQL Server 200 on the
same machine and it gives me the error

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'NT AUTHORITY\NETWORK SERVICE'.

Source Error:


An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.



well when i looked through the user list available to add 'NT
AUTHORITY\NETWORK SERVICE' was not on the list only ASPNET user was... which
i added like i did in 2000 and still the same error, what do I do? thanks

 

[Hermit Dave]

you will find this helpful

http://www.codeproject.com/cs/database/sql_in_csharp.asp

i prefer to execute commands through query analyser unlike osql command
prompt as in this article

HTH

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Steven Cheng[MSFT]]

Thanks for Hermit's informative resources.

Hi Brian,

The problem you meet is a general permission issue on asp.net when
accessing sqlserver. As you mentioned on 2k machine, we can grant the
permission to the machine\aspnet and make it work. But it didn't work on
2k3 machine. This is because the different process isolation model between
them.

The <processModel> setting is the one worked for asp.net on win2k or xp
machine which can use <processModel> to specify process identity .

#Configuring ASP.NET Process Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconConfiguringASPNETProcessIdentity.asp

On w2k3 with iis6, this model is also available but by default is not used.
The default process isolation model is applicaiton pool setting( can
configure in IIS management console)
The application pool setting can help speicify an asp.net web application
under a certain application pool and each application pool can configure a
process identity. And the default value is the NT AUTHORITY\NETWORK
SERVICE account which is in the IIS_WPG group. So as for the problem, we
have serveal approachs:
1. in sqlsever directly add an login identity ( type the "NT
AUTHORITY\NETWORK SERVICE" directly in the name entry field since it's a
hidden account
and grant it the sufficient permission

2. Grant the IIS_WPG group sufficient rights since the NETWORK SERVICE
account is in this group.

3. Find the applicaton pool setting in IIS6 and change its identity account
so as to let the application run under a different account

4. Swtich to use the older model in 2k iis5 , use the <processModel> and
machine\aspnet account (but is not recommeded)


Here is some further resource on the new application pool isolation model
on IIS6:

#IIS 6.0 Application Isolation Modes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconiis60applicationisolationmodes.asp

#Mapping ASP.NET Process Model Settings to IIS 6.0 Application Pool Settings
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconaspnetprocessmodelsettingequivalencetoapplicationpoolsettings.asp

Hope helps. If you have any other questions, please feel free to post here.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Hermit Dave]

Thank you steven. Hey, it does mention IIS 6 on Windows 2003 Server and how
to get SQL Server access denied problem sorted
you just have to scroll a bit further down.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Brian Henry]

thanks

 

[Steven Cheng[MSFT]]

You're welcome. Thanks you for posting here and have a good day!

Regards,

Steven Cheng
Microsoft Online Support

 

[John Holmes]

I'm having some similar problems and don't have the full text of all the
threads, but have read through the last threads and now understand the
differences between IIS5 and IIS6 ProcessModel settings. I was going to try
and use the ASPNET user to connect to SQL Server as recommended in the
secnet.pdf file. I have tried using both the IIS5 isolation mode and then
modifying the machine.config file with a username="machine" and the password
set to the ASPNET users password. I reset the ASPNET user password manually.
In my asp.net application I used the following code to verify that the user
is the local aspnet user:

string strUser =
System.Security.Principal.WindowsIdentity.GetCurrent().Name;
Response.Write(strUser);

The username that's displayed is: WEBTESTMV2\ASPNET
in both instances so I know the Windows Identity is what I expect it to be.

I've configured my test sql server with a local ASPNET user and set this
user up in sql server and as a database user for my Phones database with
read access to the database. I am getting the following error:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.

My connection string includes: integrated security=SSPI

I was led to believe from the secnet.pdf that I could use non-domain
accounts on separate servers if they had the same name and passwords.

Any help would be appreciated. Also, if this isn't the recommended method
for connecting to sql server from a web server let me know as well.

Thanks,

John Holmes

 

[Kevin Spencer]

SQL Server can use 2 types of Authentication: Windows Authentication and
Mixed SQL Server and Windows Authenitcation. To use Window Authentication,
the user account must be in Active Directory (or Local Machine Users), as
well as having specific permissions assigned to that Windows User account in
SQL Server. In Mixed mode, you can create SQL Server User accounts that are
not in Active Directory or Local Machine users, but only in SQL Server, and
the user can log in using SQL Server Authentication.

In your Connection String, you included "Integrated Security=SSPI". This
indicates that only Windows Authentication should be used, rather than SQL
Server Authentication.

I was led to believe from the secnet.pdf that I could use non-domain
accounts on separate servers if they had the same name and passwords.

Apparently not. ;-)

You have several options here. If the 2 machines are in the same domain, you
can create a domain-level account, and use impersonation to access the SQL
Server. Or, you can not use a Trusted Connection, set up a SQL Server user
account, and include the name and password of that SQL Server user account
in your Connection String.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[hosseinhakimi]

I moved my asp.net site to a windows 2003 server with SQL Server 200 on the
same machine and it gives me the error

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'NT AUTHORITY\NETWORK SERVICE'.

Source Error:


An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.



well when i looked through the user list available to add 'NT
AUTHORITY\NETWORK SERVICE' was not on the list only ASPNET user was... which
i added like i did in 2000 and still the same error, what do I do? thanks

User submitted from AEWNET (http://www.aewnet.com/)