Connecting to SQLServer 2000 from ASP.NET

[Patrick]

I have an ASP.NET application that connects to a SQL Server database.

The SQL Server resides on a seperate development server from the IIS5.1 on
Windows XP SP2 on development PCs which host the ASP.NET application.

I would like to use Integrated Windows Authentication like
Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=DBName;Data Source=DevServer1

My problems!
1) ASP.NET would be running on development PCs as
MachineName\IUSR_MachineName and under SQL Server Enterprise Manager on the
Dev PC, I can't think of a way to add this user (which is on a different
machine) as a Windows user
2.1) I managed to add MyDomain\AUser as a SQL user in Enterprise Manager.
2.2) I tried to get my ASP.NET to run as MyDomain\AUser by editing
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG\Machine.config as
follows
<processModel
enable="true"
timeout="Infinite"
idleTimeout="Infinite"
shutdownTimeout="0:00:05"
requestLimit="Infinite"
requestQueueLimit="5000"
restartQueueLimit="10"
memoryLimit="60"
webGarden="false"
cpuMask="0xffffffff"
userName="MyDomain\AUser"
password="password"
logLevel="Errors"
clientConnectedCheck="0:00:05"
comAuthenticationLevel="Connect"
comImpersonationLevel="Impersonate"
responseDeadlockInterval="00:03:00"
maxWorkerThreads="20"
maxIoThreads="20"
/>

However, when I launch the ASP.NET application from IE (before it even get
to the stage to connect to SQLServer, I get the error on IE saying
Application Unavailable, and in the Event Viewer, I get

Event Type: Error
Event Source: ASP.NET 1.1.4322.0
Event Category: None
Event ID: 1084
User: N/A
Computer: MachineName
Description:
aspnet_wp.exe could not be started. The error code for the failure is
80004005. This error can be caused when the worker process account has
insufficient rights to read the .NET Framework files. Please ensure that the
..NET Framework is correctly installed and that the ACLs on the installation
directory allow access to the configured account.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.


Event Type: Error
Event Source: ASP.NET 1.1.4322.0
Event Category: None
Event ID: 1007
User: N/A
Computer: MachineName
Description:
aspnet_wp.exe could not be launched because the username and/or password
supplied in the processModel section of the config file are invalid.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

How could I fix this? I am 100% sure I have entered the correct username
and password under machine.config and I did do an iisreset as well!

 

[Scott Allen]

The problem may not be the username and password, but in the rights
MyDomain\MyUser has. For instance, I know the user has to have "Log in
as batch job" user rights. One way to track down these problems is to
turn on security auditing in the local security policy and look at
what turns up in the event log.

Another solution I have used is to let ASP.NET execute as the local
ASPNET account but set the password to a known password. In other
words, instead of:

userName="machine" password="AutoGenerate"

in machine.config, use:

userName="machine" password="longpassword"

Then on the database server, create a local ASPNET account with the
same password. For rights, you'll need "Access this computer from the
network", and "log on as batch job". You can deny local login. You'll
need to add ASPNET as a login to SQL Server and grant access to the
correct databases of course.

HTH,

 

[Steven Cheng[MSFT]]

Hi Patrick,

From your description, you're trying to access a sqlserver database on a
remote serverfrom your asp.net webserver. and since you 're using
integrated windows Authentication mode to connect the sqlserver db, you'd
like the ASP.NET web app to act as a certain domain account( which has the
right permission for conecting the sqlserver db). Also, you've tried change
the account in the <processModel> element for the asp.net but found that
even cause the web app unable to startup running, yes?

As for this problem, here are my suggestions:
1. The <processModel> 's account setting is used for the asp.net's
workerprocess's execute account. It need many asp.net specified permissions
which make sure that the asp.net web applciation can run properly. So if
you replace it with a domain account, it 'll cause some unexpected
permission issues which lead to the application uable to startup.

2. As for your situaion, I think the impersonate feature of asp.net is what
you want. The impersonation of asp.net
can help use a customized account for asp.net when accessing some
serverside resources. It can use
1) the user account from the client(passed by iis)
2) specified a fix accoun in the <identity ..> element for asp.net to use
when accessing serverside resources.

Below are some references on ASP.NET impersonation;

#ASP.NET Impersonation
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconaspnetimpersonatio
n.asp?frame=true

#Using IIS Authentication With ASP.NET Impersonation
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconusingiisauthentica
tionwithaspnetimpersonation.asp?frame=true

#INFO: Implementing Impersonation in an ASP.NET Application
http://support.microsoft.com/?id=306158

Hope helps. Thanks.

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.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Patrick]

Unfortunately,
1) My ASP.NET seems to have stopped working completely! Now, even having
reverted my machine.config to
<identity impersonate="false" userName="" password=""/>

<processModel
enable="true"
timeout="Infinite"
idleTimeout="Infinite"
shutdownTimeout="0:00:05"
requestLimit="Infinite"
requestQueueLimit="5000"
restartQueueLimit="10"
memoryLimit="60"
webGarden="false"
cpuMask="0xffffffff"
userName="machine"
password="AutoGenerate"
logLevel="Errors"
clientConnectedCheck="0:00:05"
comAuthenticationLevel="Connect"
comImpersonationLevel="Impersonate"
responseDeadlockInterval="00:03:00"
maxWorkerThreads="20"
maxIoThreads="20"
/>

My IIS won't even render a test.aspx which contains:
<%@ Page language="C#" AutoEventWireup="false" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
<META http-equiv="Expires" content="0">
<META http-equiv="Pragma" content="no-cache">
<META http-equiv="Cache-Control" content="no-cache">
</HEAD>

<body>
OK?
</body>
</HTML>

I get the same error "Server Application Unavailable" error, and that is
after I have done IISRESET.

Before this, I have tried running processModel as a DomainUser that is a
member of the local "Administrators" and "Debugger Users" group with rights
to logon as a batch job, etc.

SOS!

 

[Patrick]

Note seeting processModel userName="SYSTEM" does not help either even after
an IISRESET !

 

[Patrick]

Problem resolved- I had my WinXP ACL locking down file access to
Machine.config, relaxed that and problem resolved!

 

[Patrick]

My ASP.NET site is working, but it still won't let me into SQL Server!

Here is the code:
SqlConnection myConnection = new SqlConnection("Integrated
Security=SSPI;Persist Security Info=False;Initial
Catalog=SubscriptionsDB;Data Source=DevServer1")
myCommand = new SqlCommand(sSQL, myConnection);
myConnection.Open();
myCommand.ExecuteNonQuery();

I get an SQL Exception on the call to myConnection.Open():

System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason:
Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at Website.Templates.Subscription.Submit_Click(Object sender, EventArgs
e) in c:\inetpub\wwwroot\website\templates\subscription.aspx.cs:line 173

My machine.config is as follows:
<identity impersonate="false" userName="MyDomain\aspUser1"
password="password"/>
<processModel
enable="true"
timeout="Infinite"
idleTimeout="Infinite"
shutdownTimeout="0:00:05"
requestLimit="Infinite"
requestQueueLimit="5000"
restartQueueLimit="10"
memoryLimit="60"
webGarden="false"
cpuMask="0xffffffff"
userName="machine"
password="AutoGenerate"
logLevel="Errors"
clientConnectedCheck="0:00:05"
comAuthenticationLevel="Connect"
comImpersonationLevel="Impersonate"
responseDeadlockInterval="00:03:00"
maxWorkerThreads="20"
maxIoThreads="20"
/>

On server DevServer1 where SQL Server 2000 Standard edition with SP3 is
installed, under the Security->Login tab, MyDomain\aspUser1 is added as a
user,

Under the SubscriptionsDB Database, the user MyDomain\aspUser1 is picked as
a user with data_reader and data_writer role.

What is wrong?! Why do ASP.NET not seem to be picking up the username under
impersonate user?

 

[Steven Cheng[MSFT]]

Hi Patrick,

First, glad that your critical problem has been resolved. As for the
connecting to sqlserver problem,
have you ever tried the suggestions in my last reply? As I've mentioned,
when you want to provide some powerful permisssions(a powerful account )
when accessing some serverside resource, you can just use impersonate
rather than changing anything in the machine.config.

You can both
1) the user account from the client(passed by iis)
2) specified a fix accoun in the <identity ..> element for asp.net to use
when accessing serverside resources.
<identity impersonate="true" userName="MyDomain\aspUser1"
password="password"/>

#note in your web.config, not machine.config, machine.config is the setting
for all the web applicatoin on the machine, and can be override in each web
app' web.config file.

when we set <identity impersonate="true" ...> if we specify a user account
after it, then the asp.net will use this account to access serverside
resources . If not specified, it will use the account passed from IIS(the
client account)

3) Also, we can use code to programmatically impersnate the current
thread's security context. I strongly recommend that you have a look at the
following tech article which may provide some clues.

#INFO: Implementing Impersonation in an ASP.NET Application
http://support.microsoft.com/?id=306158

Also, the following refrences will help you understand the impersonating
and the ASP.NET's authentication mechnism.

#ASP.NET Impersonation
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconaspnetimpersonatio
n.asp?frame=true

#Using IIS Authentication With ASP.NET Impersonation
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconusingiisauthentica
tionwithaspnetimpersonation.asp?frame=true

Good Luck. Thanks.

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.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Patrick]

Hi Steven,

Thanks for your reply.

This is a web application which does not take in a user ID (from IIS
Integrated windows authentication or Forms authentication) and it should
ideally use the IWAM_MachineName account to connect to SQL Server (in a
test/production environment), but for a development environment, it would do
with a developer's domain account.

I understand that if I have the <impersonate /> tag within the
machine.config, then this settings would apply to all web applications on
the machine, and if I set it within the web.config of an individual web
asp.net application, then only that application would be impersonated.

My point is
1) It should be OK to have the impersonate settings in machine.config
2) My ASP.NET application should pickup the <impersonate/> settings from the
machine.config (it was not set in web.config)
3) But it clearly isn't working, as you see from my SQL Exception, it seems
like the username is NULL !!

Note I do *Not* want to impersonate programatically, because this is only
meant to be a remedy in the development enviroment, whereby the SQL Server
on a seperate SQL Server cannot add DevelopmenPC\IWAM_DevelopmentPC as a
user (where by in production, SQL and ASP.NET would be running from the same
box and would not be an issue).

But my query is why isn't ASP.NET seem to be passing the impersonated user
credentials to SQL Server when it is trying to connect to execute the SQL
query?

 

[Patrick]

This is weird...

I have deployed the code on the Development Server, which is running ASP.NET
and SQL Server against which the ASP.NET is trying to connect.

It's getting the following exception:
System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\NETWORK SERVICE'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at Website.Templates.Subscription.Submit_Click(Object sender, EventArgs
e) in c:\inetpub\wwwroot\website\templates\subscription.aspx.cs:line 173.

The connection string used is "Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=SubscriptionsDB;Data Source=DevServer"

This is running on a Windows 2003 Server, though. Would this makes a
difference?

 

[Patrick]

Note, same problem even after
1) I set <identiy/> tag to do impersonate as a Domain account which has
access to the said database
2) Performed IISRESET

 

[Felix Wang]

Hi Patrick,

Have you tried setting the "impersonate=true" in the machine.config? In
your original post, it was the following:

<identity impersonate="false" userName="MyDomain\aspUser1"
password="password"/>

If "impersonate" is set to false, ASP.Net will not impersonate the fixed
identity you specify.

Regards,

Felix Wang
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.