Problem deploying forms authorization

[HIK]

I have a forms authentication application in asp.net 2.0 which is
working fine on my development machine. I authenticate the user with a
simple custom routine. I have copied the database to the production
server and have given necessary permissions for the logged on user to
access the database. Using SQL profiler I can see that the queries are
being carried out on the sql server (2005).

My production server is giving me an error page "An error has occurred
while establishing a connection to the server. When connecting to SQL
Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) "

this error is in spite of the fact that my queries called in the code
have ran properly on sql server.

If I remove the forms authorization mode portion of the web config file,
the rest of my application works fine on the production server, and I
have no problem accessing the database. Here is my web config:

<system.web>

<authorization>
<allow roles="admin"/>
<allow roles="manager"/>
<allow roles="user"/>
<deny users="?" />

</authorization>

<identity impersonate="true" userName="myuser" password="password"/>

<roleManager enabled="true" />
<compilation debug="true" strict="false" explicit="true"/>

<authentication mode="Forms">
<forms loginUrl="Logon.aspx" name=".ASPXFORMSAUTH">
</forms>


</authentication>

It appears my deployment problem has something to do with configuration
of the logon page. As the code works on my production machine, and the
connection is made with sql server, I don't think its a problem with the
code, but with some configuation settings.

Any help is appreciated.

haim katz

 

[Steven Cheng[MSFT]]

Hi Haim,

From your description, I understand you're using ASP.NET forms based
authentication, and will use a custom SQL Server database to authenticate
the username/password credentials. However, you found that it start report
connection error after you move the database to a production database
server ,correct?

Regarding on this issue, as you mentioned that "this error is in spite of
the fact that my queries called in the code have ran properly on sql
server.", so you can make sure that the ASP.NET application has correctly
connected to the SQL Server database and executed query on it, correct?

Since there are many things that could be related to the problem here, I
think we'd better perform some tests to isolate the issue:

** Currently your ASP.NET application is still on the development machine
and connect to the remote SQL Server on production machine, correct?

** If you've made sure that the query has been executed on the remote SQL
Server 2005 instance, is there any other code in your ASP.NET application
that will visit a remote SQL Server machine which may return such exception?

** In the SQL profiler trace message, have you seen the expected security
identity from the ASP.NET application(as you've used <impersonate userName=
... password=...> to force impersonate)?

** You can create a console application which use the same security account
to execute some SQL scripts against that remote SQL database to see whether
it works or will report the same error.

Anyway, if will be much helpful if we can recreate the symptom under some
simplified condition.

If there is anything else I missed or you have any other finding, please
feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HIK]

Dear Steven,

Thank you for your prompt response. Your assumptions are generally
correct.
Situation that works:
Development environement : Visual Studio hosting the application and
SQL Server Express on the local machine with the database information:

Situation that has problems:
Production environment: IIS hosts the application and SQL Server 2005
is on the same machine as the production environment (domain controller).

Here is the code that does my authorization and authentication:
Public Function AuthenticateUser(ByVal uname As String, ByVal pword As
String) As Boolean
Try
Dim myds As New Authenicateds
Dim myta As New
AuthenicatedsTableAdapters.AuthenticatTableTableAdapter
myta.Fill(myds.AuthenticatTable, uname, pword)
If myds.AuthenticatTable.Rows.Count > 0 Then
Return True
Else
Return False
End If

Catch ex As Exception
Return False
'Return ex.Message
End Try


End Function
Public Function GetUserRole(ByVal uname() As String) As Boolean

Try

Dim myds As New Authenicateds
Dim myta As New AuthenicatedsTableAdapters.RolesTableAdapter
myta.Fill(myds.Roles, uname(0))
If myds.Roles.Rows.Count > 0 Then
Dim myrow As Authenicateds.RolesRow
For Each myrow In myds.Roles.Rows
Roles.AddUsersToRole(uname, myrow.Role)

Next

Else
Roles.AddUsersToRole(uname, "User")

End If

Catch ex As Exception
Return False

End Try


End Function

''This is the login page code calling from a page with a login control


Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim uname As String = Me.Login1.UserName
Dim mystr As String
Dim pword As String = Me.Login1.Password
Dim mycomp As New Handle()
Dim unamea() As String


Try

e.Authenticated = mycomp.AuthenticateUser(uname, pword)
If e.Authenticated Then
Dim myname(0) As String
myname(0) = uname
mycomp.GetUserRole(myname)


End If
If Not e.Authenticated Then

Response.Write("Try again")
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub

I'm referring to the production environment scenerio:

The queries in the authentication subroutines
myta.Fill(myds.AuthenticatTable, uname, pword)
myta.Fill(myds.Roles, uname(0))

are executed on the server by the user defined in the impersonate
attribute of the web.config. The application, however, appears to hang
after calling these queries and won't open the page its directed to.

If I eliminate the authenication and go directly to the redirected page,
which is the heart of the appication. it opens fine and queries the
database.

I cannot find any calls to another server by mistake.

Could the authentication class be looking for something I didn't define?
or need some other permissions?

Haim

 

[Steven Cheng[MSFT]]

Thanks for your reply Haim,

So no matter on development machine or production machine, the ASP.NET web
application and the SQL Server database are always on the same machine,
correct? If so, I'm feeling a bit confused since the error message contains
a "remote connection not allowed ...." info which should only occur when
there is db connecction and request from remote client.

Sure, I think the problem is related to the forms authentication part,
however, I don't think the problem is the code logic since the code logic
is quite straighfoward and most of them are calling some data access
component(TableAdapter ....). There should be something incorrect with the
security context.
When you run the application on development box, since it run in VS IDE
test server, the security context is the logon user, while host in IIS, the
security context is IIS worker process account, as you've also used
<impersonate ....> setting, is the username & password credential matches
the logon user on development machine?

Currently, I would still suggest you try simplify the problem application.
You can try pick some data access code (within the forms authentication
code logic) out and put them into a single page and run that page
individually(you can remove forms authentication when perform this test) so
as to repro the same error. If we can repro the problem like this, it would
be much easier to continue troubleshoot.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steven Cheng[MSFT]]

Hi Haim,

Any progress on this issue? Since we're continouslly monitoring this issue,
please feel free to post here if there is anything we can help.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HIK]

Dear Steven,
I was away for a couple days so I'm sorry for the delay. I did find
some old posts of yours which indicated to me that the problem is with
the Membership provider. My login screen uses the .net login control
and I think the application is looking for the default sql membership
provider. Its apparently looking for another instance of SQL server and
cannot find it. The error seems to be comming from the Framework and
not my code.

Even though I don't use the provider membership tables, I installed the
sql membership provider to my database using the tool from the command
prompt, and then recopied the database to my production server. Now I
do not get the same error. I get a new one: Login failed for user 'NT
AUTHORITY\NETWORK SERVICE'

I made the NT Authority\Network Service a member of all the asp_net
roles which were added as a result of the membership provider, but I
still get the same message. Again when I look at the SQL profiler, my
sql statements are being run without problems. I believe I'm having
problems with permissions for the membership provider.

Below are the error message and web.config file:

*****************
Error Message:
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.

Stack Trace:


[SqlException (0x80131904): Login failed for user 'NT AUTHORITY\NETWORK
SERVICE'.]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject) +437

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) +82

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context,
Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String
connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlRoleProvider.GetRolesForUser(String username)
+771
System.Web.Security.RolePrincipal.IsInRole(String role) +272

System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection
roles, IPrincipal principal) +120
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal
user, String verb) +300

System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal
user, String verb) +191
System.Web.Security.UrlAuthorizationModule.OnEnter(Object source,
EventArgs eventArgs) +3394990

System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously) +64

*****************

Web Config
*******************

<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="FishConnectionString1" connectionString="Data
Source=myserver;Initial Catalog=Fish;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="FishConnectionString" connectionString="Data
Source=myserver;Initial Catalog==Fish;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.

Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.
-->
<authorization>
<allow roles="admin"/>
<allow roles="manager"/>
<allow roles="user"/>
<deny users="?" />

</authorization>
<identity impersonate="true"/>

<!--<roleManager enabled="true"/>-->
<roleManager enabled="true">

<providers>

<remove name="AspNetSqlRoleProvider" />

<add connectionStringName="FishConnectionString1"
applicationName="/"

name="AspNetSqlRoleProvider"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<remove name="AspNetWindowsTokenRoleProvider" />

</providers>

</roleManager>
<membership>

<providers>

<remove name="AspNetSqlMembershipProvider" />

<add connectionStringName="FishConnectionString1"
enablePasswordRetrieval="false"

enablePasswordReset="true" requiresQuestionAndAnswer="true"
applicationName="/"

requiresUniqueEmail="false" passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"

minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1"

passwordAttemptWindow="10" passwordStrengthRegularExpression=""

name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</membership>
<compilation debug="true" strict="false" explicit="true"/>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<webParts>

<personalization defaultProvider="AspNetSqlPersonalizationProvider">

<providers>

<remove name="AspNetSqlPersonalizationProvider" />

<add name="AspNetSqlPersonalizationProvider"


type="System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider"

connectionStringName="FishConnectionString1"

applicationName="/" />

</providers>

</personalization>

</webParts>



<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Forms">
<forms loginUrl="Logon.aspx" name=".ASPXFORMSAUTH">
</forms>


</authentication>


<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly"
defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->

<customErrors mode="Off"/>

</system.web>
</configuration>

 

[Steven Cheng[MSFT]]

Hi Haim,

Glad to hear from you.

Yes, seems you've got much progress on this, at least you've figured out
the original unexpected "remote access error....." (as it is caused by the
default sqlexpress provider setting).

For the new problem you met:

===========
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'
============

I think it is a typical ASP.NET security issue that is caused by the
accessing SQL server database from IIS hosted enviornment. For IIS6, the
default worker process identity (security account) is "NT Authority\NETWORK
SERFVICE"(which as restricted permission), so it is likely that the
application get access denied or login failed error when accessing some
protected resource under this account.

Also, to make sure it is the process identity that cause the error, I
suggest you try the following test:

** change your ASP.NET applicaiton's IIS application pool identity from
"Network Service" to an interactive account(domain account or local one)
which has sufficient permission in SQL server database.

** Restart the application pool and application to see whether it can
correctly access the database now.

If the above work, that means the problem is related to the application
pool identityt and we should continue work on the permission of it(the
default Network Service account).

BTW, for the "asp_net roles" you said, is it a custom group you used on
your machine or do you mean a built-in group or role in application?

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HIK]

Steven,
Thank you again for your help. Your suggestions worked well, but I
cannot seem ot solve a problem without creating more problems.

I created another app pool for this application and created a new user
with appropriate permissions. I no longer have the login failed
message. The code I wrote for authentication is run, but ignored. By
running profile I can see the application making many calls to the
Membership Provider database and seemingly automatically authenicates
the user, ignoring my routine. Even though my routine returns
e.authenticated = false, the application starts querying the membership
provider database and reverts e.authenticated to true.



Here is my code


Login page

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim uname As String = Me.Login1.UserName
Dim mystr As String
Dim pword As String = Me.Login1.Password
Dim mycomp As New Handle()


e.Authenticated = mycomp.AuthenticateUser(uname, pword)
If e.Authenticated Then
Dim myname(0) As String
myname(0) = uname
mycomp.GetUserRole(myname)
End If
If Not e.Authenticated Then

Response.Write("Mistake")
End If
End Sub


Public Function AuthenticateUser(ByVal uname As String, ByVal pword
As String) As Boolean
Try
Dim myds As New Authenicateds
Dim myta As New
AuthenicatedsTableAdapters.AuthenticatTableTableAdapter
myta.Fill(myds.AuthenticatTable, uname, pword)
If myds.AuthenticatTable.Rows.Count > 0 Then
Return True
Else
Return False
End If

Catch ex As Exception
Return False
'Return ex.Message
End Try


End Function
Public Function GetUserRole(ByVal uname() As String) As Boolean
Try
If Not Roles.RoleExists("User") Then
Roles.CreateRole("User")
End If
If Not Roles.RoleExists("Admin") Then
Roles.CreateRole("Admin")
End If
If Not Roles.RoleExists("Manager") Then
Roles.CreateRole("Manager")
End If

Catch ex As Exception

End Try

Try

Dim myds As New Authenicateds
Dim myta As New AuthenicatedsTableAdapters.RolesTableAdapter
myta.Fill(myds.Roles, uname(0))
If myds.Roles.Rows.Count > 0 Then
Dim myrow As Authenicateds.RolesRow
For Each myrow In myds.Roles.Rows
Roles.AddUsersToRole(uname, myrow.Role)

Next

Else
Roles.AddUsersToRole(uname, "User")

End If
''*
'Return "Good"
''*
Catch ex As Exception
Return False
'Return ex.Message
End Try


End Function

 

[Steven Cheng[MSFT]]

Thanks for your reply HIK,

Glad that you've got some progress and the login failed error no loger
exists.

For the new problem you met, I'm not quite sure on the following behavior:
The code I wrote for authentication is run, but ignored.
<<<<<<<<<<<

I can imagine that the SQL calls are passed and executed in SQL server as
you now can correctly login SQL Server and execute query. However, what's
the behavior that means your code has been ignored?

Also, from the code you provided, it seems you haven't used the built-in
membership & role provider feature and use your own custom database and
component class for user account validation and role retrieving , correct?
Also, in the Login Control's authentication code, I found that you only use
component to do the data access and validate the account, have you also
called the Forms Authentication API to establish the forms authentication
cookie ticiet? If you haven't used the forms authentication API and are
not sure on it, you can have a look at the following articles:


#BASICS: Forms Authentication in ASP.NET 2.0
http://www.eggheadcafe.com/tutorials/aspnet/009e2e5e-5a44-4050-8233-59a0d698
44e8/basics-forms-authenticat.aspx

#Explained: Forms Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/aa480476.aspx

#How To Implement Role-Based Security with Forms-Based Authentication in
Your ASP.NET Application by Using Visual C# .NET
http://support.microsoft.com/kb/311495/en-us

Please feel free to let me know if you have anything unclear on this.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HIK]

Dear Steven,
You understand me very well.

My program flow is as follows:
1.User logs in with username and password (using logon control)

2. I query the database to see that it is accurate and returne if the
user is authenticated or not. (using my own table).
3. If he is authenticated, then it queries my table to see what his/her
role is and redirects the user to the correct page.

This worked fine on my production machine.

What appears to be happening now is
Steps 1 & 2 above
3. asp.net queries the asp.net sql membership provider and authenticates
the user ignoring/overriding the results of my custom query.
4. The user is still defined a role based on my custom query.

After reading the links you provided it seems to me I have the following
options>
1. Use the sql membership provider and configure the users with the
provider and not to use my tables.
2. not to use the logon control and create my own authentication cookie
in the code.

What I want to do is use the logon control and my own tables to
authenticate the code, without having to create my own membership class.
Is this is not possible?

Haim

 

[Steven Cheng[MSFT]]

Thanks for your reply Haim,

After some further speculation, I think I somewhat figure out the problem
you're encountering. Yes, it does be somewhat caused by the Login Control,
however, you can still use the login control with your own authentication
logic(and suppress the built-in membership/role providers'). To do so, you
should perform as below:

** For the Login Control, you should use the "LogginIn" event instead of
"Authenticate" event, because in LogginIn event, you can cancel the event
so that it won't execute further to call built-in membership providers.

** Here is the pseudocode code logic of the "logginIn" event:

=================
protected void Login1_LoggingIn(object sender, LoginCancelEventArgs e)
{
//do your database query and username/password validation here

//use FormsAuthentications.XXX method to establish
authentication ticket

//cancel the event so that it won't execute down furthe to call
built-in membership provider
e.Cancel = true;

}
=========================

** For the code about "use FormsAuthentications.XXX method to establish
authentication ticket", you can refer to the articles(that mentioned
manually use Forms Authentication api to generate authentication cookies) I
mentioned in previous reply.

If you still have anything unclear, please feel free to let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steven Cheng[MSFT]]

Hi Haim,

How are you doing?

Have you got progress or resolved on this issue or does the further
information in my last reply help you some?

If you need any further help, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.